Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Shrinking a Transaction Log

Discussion in 'SQL Server Scripts and Tools' started by Peter Schmitz, Jan 8, 2013.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    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:

    sp_helpdb_output.png

    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.

Share This Page