Monday, December 14, 2009

SQL Server Transaction Logs taking up too much space

Transactions LOGs are used by SQL server to give transactional integerity. If you have set your DB Recovery Model to "Full" every data change query will be logged in  the Transaction log. If your transaction logs are getting too large (e.g you are retrieving large data-sets / day and deleting data-sets for old dates )  it is suggested that either you get more harddisk space OR:
  • Change the DB Recovery model (right click db in ssms -> select properties -> select options page) to "Simple"
  • Set Autoshrink to True. 
  • Organize your backup model accordingly.


In case you do not want to do that there is a quick solution (DBNAME is your database name):

USE DBNAME
GO
DBCC SHRINKFILE(DBNAME_log, 1)
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBNAME_log, 1)



But note that it can come back to bite you because:
  • you need to do a backup from the beginning after this. Previous backups are invalidated. Which means the backup model you have in place for Full Recovery model is invalidated.
  • It does not solve the underlying problem why the transaction log became this big in the first place.It will happen again.

Best of Luck!