SQL Server - How To Shrink A Transaction Log

How to shrink a SQL Server transaction log that does not want to shrink

Problem

Usually resulting from lack of management of your SQL transaction logs or or an unusually high amount of database transactions, you may find a transaction log that has grown to a large size.  If you do a transaction log backup to clear it, the file size may stay the same.  If you look to shrink the file you can see that there is a lot of available free space in it.  However, if you try to shrink it, it will look successful but the file will remain the same size
Fortunately, this is quite a straightforward problem to fix.

Solution - Short Version

Backup the transaction log for the database.  Change the database recovery model to be Simple and then shrink the file.  It should work this time.  When it's finished and you have verified that the file size has reduced, change the database recovery model back to Full.

Solution - Long Version

• Right-click on the database and go into tasks and Shrink File.
• Select the Log file and check the Available Free Space.  You should see that there is a large amount of free space.

• Close out of this screen and right-click on the database and click Properties.  Under Options, look for the Recovery Model.  Change it from Full to Simple.

• Go back to shrink the log file and when it has finished, you should see the new size.  You can verify the new file size in a command prompt or Windows Explorer

Don't forget to change the Recovery Model back to Full when you are finished.