Commands to shrink the SQL Server transacation log

ms-sql-logoI recently encountered a database that was 4 MB in size with it’s respective transaction log at 460 MB. The transaction log is used in a recovery model to restore the database to a point in time. I wanted to reduce the size of the transaction log. More importantly, I wasn’t exactly clear as to why a transaction log of that size was possible. It turns out that the transaction log had not been backed up for some time.

To reduce the size of the transaction log, the transaction log must be backed up first and then you can use shrink. This may be accomplished utilizing the SQL Query Analyzer

  • In SQL Server Enterprise Manager, go to Tools then SQL Query Analyzer (Tested successfully. The transaction log was reduced to 2 MB)
    sqa02
  • Then select your database
    sqa01
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( databasename_Log, 1)
  • Then execute the query F5 or press the green arrow.

    • Another source offered the following solution: (untested by me)

    • Right-click the database, choose Properties
      sqa03
    • Select the Options tab
      sqa04
    • Make sure Recovery Model is set to Simple, not Full
      sqa05
    • Click Ok
    • Right-click the database again, choose All Tasks -> Shrink Database …
      sqa06
    • Go to Shrink files and press Files…
      sqa07
    • Change the type to ….log
      sqa08
    • Click Ok
      sqa09

    Alternatively, the SQL to do it:

    ALTER DATABASE mydatabase SET RECOVERY SIMPLE
DBCC SHRINKFILE (mydatabase_Log, 1)

    According to another source, “The first parameter is the so-called “logical name” of the log file (and not the physical name with the *.ldf extension). The second parameter (the zero), means to disregard the default “free space factor”, and shrink it to the maximum extent possible.”

    DBCC SHRINKFILE (DataBaseName_Log, 0)