Shrinking a Transaction Log

I figured I would start putting some of the scripts I use regularly onto the site. Here's a small snippet of code to find out the (logical) name of the Transaction Log, and then shrinking it to a new size.

I use it whenever I receive a database backup from a client's production database. Typically, these are set to use Full Recovery model, and thus have a fairly large TransAction log file. Seeing I normally do not need that, I'll switch the database to Simple Mode, and then shrink the file to recover disk space.

Important: Do not run this on a production database, unless faced with a transaction log that somehow ended up filling the disk (in which case you probably forgot to implement transaction log backups).

First ensure that the active part transaction log has been flushed, either by creating a transaction log backup, or switching the database to Simple Recovery. Then the following commands will allow you to find the name of the log file, and to shrink it down to your desired size in megabytes.

Code (TSQL):

sp_helpdb <database_name>;
 
DBCC SHRINKFILE ('<log_file_name>', <desired size>)
 

The first line will allow you to find the logical name of the transaction log file, like so:

Code (TSQL):

sp_helpdb SchmitzIT

The output will allow us to deduct the name of the Log file:

The above screenshot shows that the name of the logfile is SchmitzIT_log. If we want to shrink the file to 250 MB (I do know the size in the screenshot is only 1 MB, but that's because I just created the DB), we would then issue the following command:

Code (TSQL):

DBCC SHRINKFILE('SchmitzIT_log', 250);

With that done, your log file should have reduced in size.

If you were recovering a production base, it is now time to switch the recovery model back to Full Recovery, and immediately making a Full Backup.

Sponsored link: