Wednesday, December 2, 2009

Truncate your SQL Server Transaction logs

If your SQL Server Transaction logs are exploding, you can shrink them using following commands:

Check first if their are any pending transactions:

   1:  DBCC OPENTRAN(<TransactionLogName>)

If there are no pending transactions, you can safely execute following command:

   1:  USE DatabaseName
   2:   
   3:  GO
   4:   
   5:  DBCC SHRINKFILE(<TransactionLogName>, 1)
   6:   
   7:  BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
   8:   
   9:  DBCC SHRINKFILE(<TransactionLogName>, 1)

No comments: