Under some specific circumstances you will find yourself in the need of reducing/shrinking the physical size of transaction logs.
Let’s see a quick example of how to achieve this:
1) You can use DBCC SQLPERF (LOGSPACE) to quickly and easily list the size and percentage of use of all your databases’ transaction logs. You will get something like the image below.
Here, you can clearly see that row 7 has a database with a transaction log of 150GB (approximately) and only 0.3% of it is being used (bad).
2) Run DBCC SHRINKFILE(LOGFILENAME, TARGETSIZE) to shrink LOGFILENAME to TARGETSIZE. You can use “SELECT * FROM sys.database_files” to list the location and name of all your database files.
For example: DBCC SHRINKFILE(DATABASE_LOG, 20000) will “try” to shrink the size of the the log file “DATABASE_LOG” down to 20000MB
The final output will be something like the image shown below
From the image above you can see that the log file has been shrunk from 150GB to 20GB and now the number in “Log Space Used (%)” looks better.
Do you really want to reduce the size of your transaction logs?
WARNING In this post I am just showing how to shrink the physical size of transactional log files; however, I am NOT justifying or providing details of when this should be done. Transactional log files grow in size because this is exactly what they are meant to do, if you want to reduce the size of log files just because they “seem to be to large” then it is very likely that you are wasting your time.
Before dealing with log files I highly recommend to read about SQL Server Recovery Models so you can understand how they affect your log files.1