Procedure to Shrink the Database Transaction Log


What is Shrink Database. In reality to increase SQL Server disk space, shrink database task is used. This article will demonstrate the procedure to shrink the database transaction log.


Occasionally, you might need to conduct database maintenance whether you have the pre-packaged MSDE or the full version of SQL Server. This maintenance should be conducted after you have deleted large amounts of data from the database (Example.mdf and Example_log.ldf ). Usually this occurs if you delete a large number of events from the event log. Even after data is removed, the .mdf and .ldf files need to be compacted (similar to an Outlook *.pst file after deleting emails) in order to recover disk space and increase efficiency, and also to reduce seek time.

More details will be available from this link.

Using the Code

Before we start using this code / how the code works as well, I would like to share some basic information regarding the database shrink process.

The process has several control parameters, most of the time you only need to worry about

the first four as these are the big controls whereas the fifth is simply a fine tuning control which rarely needs to come into play.

Parameter Description at a Glance

More details will be available from this link.

Using this code is very simple. Just follow the given procedure:

  1. Set the name of your database.

Example. This is the name of the database for which the log will be shrunk.

USE [databasename] put the logical name of the database to replace the database name.

  • Set the logical file name.

    Example. Use sp_helpfile to identify the logical file name that you want to shrink.

    *Do the same here replace the database_log for the database log name.

    *Run the script, if it doesn't run then it's down to the logical name of the database that isn't right.

    Note: This has been tested successfully on SQL 2000, 7 and 2005 databases. 40 GB went down to under 1 GB, 176GB went down to 105MB.

    The code example is given below:


    Category: Forex

    Similar articles: