3

I am new to SQL Server and don't fully understand its architecture. I've looked around, Googled, asked around but found no simple (understandable) answer to the problem. I'm not even sure if it is problem in technical terms or just the default behavior, but in terms of my business operation, it is a big problem.

I have acquired SQL Server 2008 R2 [from the developer who doesn't work here anymore], with a database of physical storage size of 2 TB. I've created a database, say TEST with

  • TEST.mdf default at 10 MB, 10% growth and max size: unlimited. There are 40 files comprising the TEST.mdf file say TEST_part01 through TEST_part40 with same configuration as TEST.mdf.

  • The log file - TEST_log.ldf file also has same configuration as TEST.mdf

  • The database handles millions of rows in daily basis, with lots and lots of insert, delete, update, drop etc. operations.

  • I am running out of space. While trying to shrink the database, I saw that the TEST.mdf could be shrunk by 41% and TEST_log.mdf by 99%.

Questions

  1. Is 41% and 99% of recoverable space actually taken by garbage, un-used data? If so can I selectively remove/purge (if not all than most of ) the 41% and 99% of data?

  2. Is there any temporary or otherwise structure/objects created by the SQL Server itself, which I can safely delete/purge?

Improve this question
1
  • If you run SQL housekeeping on the database, regularly, it will ensure unused space is sorted, log files can be truncated after full backup of the log etc. Commented Sep 28, 2012 at 9:14

3 Answers 3

Reset to default
3

If you just acquired an instance of SQL Server from someone, I would first ask have you gone over the instance configuration and all to understand how the previous person had it configured?

I would suggest taking a look at Brent Ozar's sp_BLITZ script. It is a stored procedure that will collect some good information for you on your instance configuration and give you a good overview of what it sees. I would read over Brent's blog on this script in detail before ever running this on your server, FULLY understand what it is doing.

Regarding your database, are you running out of disk storage or database storage? How much free space does the database contain? I would first understand the architecture of the files before I start making changes. A good article to read on file management is from SQLSkills.com here.

Just my $0.02...

Improve this answer
2

shrink a database id BAD, take a lok at this. It has explanations and examples

EDIT:

Regarding log files, no matter how many log files you have, SQL Server treats these multiple log files as a single contiguous-file virtual log file. So you dont have to worry about them individually.

To free some space on your log, it must be truncated. Log truncation is the process by which the Database Engine frees space in the logical log for reuse by the transaction log. Based on he size of your file, I can assuma that your are using either full or bulk-logged recovery model on your DB so, the only way to do it is by performing a log backup (as long as a checkpoint has occurred since the previous backup).

Improve this answer
1

Each file within a database can be reduced to remove unused pages. Although the Database Engine will reuse space effectively, there are times when a file no longer needs to be as large as it once was; shrinking the file may then become necessary. Both data and transaction log files can be reduced, or shrunk. The database files can be shrunk manually, either as a group or individually, or the database can be set to shrink automatically at specified intervals.

Files are always shrunk from the end. For example, if you have a 5-GB file and specify 4 GB as the target_size in a DBCC SHRINKFILE statement, the Database Engine will free as much space as it can from the last 1 GB of the file. If there are used pages in the part of the file being released, the Database Engine first relocates the pages to the part of the file being retained. You can only shrink a database to the point where it has no free space remaining. For example, if a 5-GB database has 4 GB of data and you specify 3 GB as the target_size of a DBCC SHRINKFILE statement, only 1 GB will be freed.

Check this page, it should answer all your questions concerning these issues.

I hope this helps.

Improve this answer
1
  • Also note: shrinking your database file will mess up all your indices in terms of fragmentation. If you regularly shrink your database files - please do so in a maintenance plan that rebuilds all indices afterwards. Commented Sep 28, 2012 at 9:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.