This article is also available for viewing online at https://knowledgebase.winspc.com/questions/210/


How do I manage the Microsoft SQL Server transaction log?

If you are using Microsoft's SQL Server as the backend database for WinSPC, you may need to manage the database transaction log in order to keep it to a manageable size.  The transaction log is used by the database server to record changes to a particular database in the event that those modifications have to be reverted (or "rolled back").  Under some database settings, the transaction log can grow unbounded, filling up the hard drive of the server as well as decreasing the overall performance of the database server and WinSPC.  In extreme cases, if the transaction log cannot increase in size anymore (due to it completely filling the hard drive), errors regarding the transaction log will appear in WinSPC.

Microsoft SQL Server (2000, 2005, and 2008) supports three different ways for managing the transaction log (called the "Recovery Model"):
  • "Simple" - This model only keeps a minimal amount of information in the transaction log, causing it to remain fairly consistent in size (it will not continuously grow over time).  In a disaster recovery scenario, a database with this recovery model can only be restored to the state of its last backup.
  • "Full" - This model will record every change to the database in the transaction log.  The database administrator is responsible for truncating the transaction log (this is often done following a successful backup) to prevent it from growing unbounded in size.  In a disaster recover scenario, a database with this recovery model can be restored to any point in its transaction log.
  • "Bulk-logged" - This model is a special purpose model that functions similar to the "Full" model.  This model logs minimal information about bulk data modification transactions, which results in better performance, but fewer recovery options (specifically, you cannot restore the database to a specific point in time).  Microsoft recommends only using this model for short periods of time; specifically when performing bulk operations.

The recovery model can be configured in the database properties (using SQL Server Enterprise Manager).

In Microsoft SQL Server 2005 or 2008, it can be found on the 'Options' page:
sqlrecoverymodel2005.png


In Microsoft SQL Server 2000, it can be found on the 'Options' tab:
sqlrecoverymodel2000.png



In general, for WinSPC, we recommend either using the "Simple" or "Full" recovery model.  If you are using the "Full" recovery model, you will need to ensure that the transaction log is being truncated on a periodic basis.  As mentioned previously, this is normally done following a database backup.

If your transaction log is very large, one way to permanently remove some of the older entries (and to reduce the size of the log file) is to do the following:
  1. Execute the following SQL command: DUMP TRAN database WITH NO_LOG
    where database is the name of your WinSPC database.
  2. The above command removes entries from the log, but may not necessary shrink the size.  To release the unused space (SQL Server 2005/2008 only):
    1. In SQL Server Management Studio, right click on the database and select "Tasks" > "Shrink" > "Files".
    2. From the "File Type" drop down, select "Log".
    3. Verify that "Release unused space" is selected as the "Shrink Action".
    4. Click "OK".
Alternatively, you can set the "Recovery Model" to "Simple" and then execute step #2 above to shrink the size of the transaction log.

More information regarding managing the size of the transaction log can be found on Microsoft's website here:
http://msdn.microsoft.com/en-us/library/ms345583.aspx
http://support.microsoft.com/kb/873235