Shrinking SQL Log files

Sometimes SQL Log files can grow out of control. This is typical when a backup of the database is not setup or has been failing for one reason or another. This quick tutorial will show you have to shrink the log file back to a smaller size. 

Launch the SQL Server Management Studio from the start menu.

Login to your Database Server

Browse down to your DB that needs to be shrunk. Right click the DB and click Properties.

Once in the propertied of the DB click Files on the left side. This will give you the name of the log file that you wish to shrink.

Under Database File the first column is Logical Name, and you will need the logical name of the LOG File.

When done close the window by selecting Cancel below.

Now right click the Database and click New Query.

Determine what version of SQL you are working with if you are not sure.

But typing Select @@version and clicking the Execute button on the toolbar above you will see the results in the pane below. In this instance it is SQL 2012. 

It will take a total of 4 commands to shrink the file. These commands depend on the version of SQL you are running. Since we are 2012 the 4 commands are below. 

These commands work with 2008, 2012, and 2014.

Alter Database DatabaseName Set Recovery Simple
Alter Database DatabaseName Set Recovery Full
DBCC SHRINKFILE<TransactionLogName, 1)
Alter Database DatabaseName Set Recovery Simple
TransactionLogName is found above in step 4


After typing the commands in, highlight each command in order one at a time, and hit the execute button.

When you done you should be able to browse to the log file location and see the new size of 1 MB. 

The commands for SQL 2000 and SQL 2005 are below. 

Alter Database DatabaseName Set Recovery Simple

Alter Database DatabaseName Set Recovery Full
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)
Alter Database DatabaseName Set Recovery Simple