WinSPC Knowledgebase Home | Glossary | Favorites | Contact | Login WinSPC Knowledgebase Home | Glossary | Favorites | Contact | Login
Search the WinSPC Knowledgebase Browse by Category
How do I manage the Microsoft SQL Server transaction log?
Article Details

Last Updated
24th of June, 2013

Applies to
WinSPC 7.2, WinSPC 8.0, WinSPC 8.1, WinSPC 8.2, WinSPC 8.3

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



Attachments
No attachments were found.
Related Articles
Visitor Comments
No visitor comments posted. Post a comment
Post Comment for "How do I manage the Microsoft SQL Server transaction log?"
To post a comment for this article, simply complete the form below. Fields marked with an asterisk are required.
   Your Name:
   Email Address:
* Your Comment:
* Enter the code below:
 

All Content 2012 DataNet Quality Systems. All Rights Reserved.