Monday, July 24, 2006

Keeping MSSQL Transaction Logs in Check

An essential part of maintaining any Microsoft SQL Server 2000 (MSSQL) Database driven application is a robust backup strategy, not only for the case of disaster recovery but also for keeping the size of your database transaction logs in check. By default, these transaction logs will grow over time to an infinitely large size, or if an upper limit is placed, the log file will fill up without scheduled maintenance, locking users out of the application.

The following Microsoft KB article is very helpful in this regard: http://support.microsoft.com/kb/272318/EN-US/

My recommendations for transaction log maintenance for UniPhi and related databases is as follows:

Since the ES2 (Innerprise Search) database is repopulated on a daily basis, the transaction log is not of a great importance, the following commands could be scheduled for backup purposes, keeping the log file size down:
BACKUP LOG ES2 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(ES2_log, 100)

However for the UniPhi database, we do want to have the transaction logs available in case of an emergency, so:
BACKUP LOG uniphi TO uniphilogbackup
DBCC SHRINKFILE(uniphi_log,100)
Post a Comment