Database Maintenance : Shrinking your database files

Recently we found that our SQL server was running out of disk space!!! We noticed that no backups had been done on the server so we created an SQL maintenance plan. This failed because we did not have enough space on the server to run the plan. Does this sound like you? ..

Its easily done, forgetting to create a plan to backup your sql database, and when you find out that you have no space, you may also find that you do not have enough space to run a plan. Running a plan allows you to do something called Truncate the Transaction log. When you create a database in SQL it creates two files one for the actual data ( mdf ) and one the transaction log ( ldf ) . The ldf is the file that records all the keystrokes that have happen on the database so that if needed you could play back the log file and take the database back to any point in time. For obvious reasons this ldf file can get very big , it would not be surprising if you see these files reach 5 or 6 gig for just a simple job.

To shrink these files you , normally , need to run a backup on them first, but as explained this may not be possible. To get around this issue you can use the DBCC SHRINKFILE SQL commands.

When DBCC SHRINKFILE is run, SQL shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:

1. You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
2. You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.

The following example demonstrates this with the pubs database and attempts to shrink the MySurvey _log file to 2 MB:
1. Run this

:
DBCC SHRINKFILE(MySurvey_log, 2)

NOTE: If the target size is not reached, proceed to the next step.

2. Run this

 if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:
BACKUP LOG MySurvey WITH TRUNCATE_ONLY

-or-
Run this

 if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
BACKUP LOG MySurvey TO MySurveylogbackup

3. Run this

:
DBCC SHRINKFILE(MySurvey_log,2)

The transaction log has now been shrunk to the target size.

Leave a Comment

%d bloggers like this: