go to ISQsolutions.com | Contact Us

 
Q10134 - FAQ: What is a transaction log?

The Transaction log is a log of all transactions that have taken place in a database since the last backup. There are two basic types of transactions: Active and Inactive.

Active transactions are current changes in the database from a batch that has not been committed.

This provides a means of rolling back the changes made if a problem occurs and allows SQL Server to maintain its referential integrity. Once a batch has been completed successfully, it is committed and becomes an Inactive Transaction.

Inactive Transactions are kept for recovery purposes and can allow a restore to a point in time.

Although we offer restores on our shared SQL Servers, we only restore full backups and do not use transaction log backups.

Since the log keeps all transactions from the last backup until it is backed up again, it is possible to fill up the log and receive a message stating that the transaction log is full.

To clear space, you will need to truncate the log.

This can be done by running the command below:

BACKUP LOG yourDBname WITH TRUNCATE_ONLY;

Truncating the logs removes all Inactive Transactions from the log. This is the only means of clearing the logs.

The Active transactions cannot be cleared until they are marked inactive.

We can also set the database to "Truncate log on checkpoint" which will automatically truncate the log when a checkpoint is raised.

Even with this option it is possible to fill up the log with Active Transactions.

If you have us set the database to "Truncate log on checkpoint" and you still receive log full errors, you will need to choose to add more log space or perform fewer transactions at one time.

In MS SQL 2008 to truncate/shrink the log file you can use the script bellow:

Use yourDBname

Alter Database yourDBname Set Recovery Simple

Alter Database yourDBname Set Recovery Full

DBCC SHRINKFILE ('yourDBname_Log', 1)

You can also set the Recovery model to Simple instead of Full.

A comparison between the models can be find at:

http://msdn.microsoft.com/en-us/library/ms189275%28v=sql.100%29.aspx


Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 2/2/2005 4:06:00 PM.
Last Modified on 4/18/2012 11:32:00 AM.
Last Modified by ISQ Solutions Inc.
Article has been viewed 3608 times.
Rated 7 out of 10 based on 3 votes.
Print Article