Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Transaction Log Troubles

Last updated Mar 28, 2003.

If you're reading this tutorial, chances are you've got a severe issue that you need to deal with immediately. I've arranged this article in major headings that deal with specific problems in working with the transaction log. If you're not in an emergency, you can read the Background section to learn more about the way transaction logs work. You'll notice some material in each section is repeated — this is on purpose, so that you can refer to a section all by itself to solve a problem.

In this tutorial I'm focused on the transaction logs and files. If you're looking for more general information on the file structure for SQL Server, check my article called Files and Filegroups.

Background

SQL Server doesn't normally allow users to change data directly in the database. Instead, it intercepts each update, delete and insert and stores the changes in another file (or set of files) called a log. The log is composed of two parts: a physical file or files, and a virtual file. The physical files are what you work with, and the virtual files are what SQL Server uses internally.

Whenever you perform a full database backup or transaction log backup the server commits the complete transactions in the database, and removes the committed entries from the log file. That does not, however, automatically shrink the physical file. This is because the internal virtual file doesn't necessarily follow the same structure as the physical file, and entries might be spread throughout the physical files. In other words, you might think that the file looks like this after you remove the committed entries:

|*******************         |

But in fact the file might look more like this:

|* ***  ******  **        ***|

Which means that the file won't shrink because it isn't really "empty."

With that background in mind, let's take a look at a few of the common problems many DBA's have with database transaction logs.

I can't enter any more transactions to my database

The transaction log is probably out of room. This prevents any new transactions, and effectively makes the database read-only.

  1. Stop the activity in the database.
  2. Perform a complete backup of the database. I explain how to do that here.
  3. Move the database files to a drive with more room. Here is one process to do that.

My transaction log is growing uncontrollably

Microsoft has a fantastic set of articles that you can refer to for both supported versions of SQL Server here: http://support.microsoft.com/default.aspx/kb/873235/.

Apart from explaining that you should work to keep the files smaller (it's a bit too late for that if you're reading this article), they explain that you can take two or three steps to fix the problem:

  1. Stop the activity in the database.
  2. If possible, perform a complete backup of the database. I explain how to do that here.
  3. If you can't take a complete backup, make sure you know where the last full backup is. Perform a log backup (if the database is in the full or bulk-logged recovery model).
  4. If you can't do either of the previous two steps, stop the SQL Server services and manually copy the database files to a safe location. Then, use the backup command with the TRUNCATE ONLY statement. This doesn't perform a backup; it simply forces the committed transactions to the database.
  5. Check to see if the log has come to a manageable size. If it isn't, see the section marked "I keep shrinking the log but it isn't any smaller."
  6. Once the log is back to a manageable size, look for large transaction operations such as BEGIN operations which perform multiple transactions, large data imports and so forth. Change these operations to have a smaller batch size.

I keep shrinking the log but it isn't any smaller

Perform these steps only after taking a complete backup and after you've run the DBCC SHRINKFILE command.

  1. Stop all activity in the database
  2. Detach the database using sp_detach_db command.
  3. Delete the log file. It should have an extension of LDF. DO NOT delete the database file, which normally has an extension of MDF.
  4. Attach the database using the sp_attach_db command.
  5. The transaction log will be rebuilt automatically.

I don't have a good backup and I lost my database. Can I recover it from the log?

No — you need at least one good database backup and all the log backups since the last database backup, plus the current log. If you have that, follow the restore procedures for the database and log backups, using RESTORE WITH NORECOVERY.

Can I read the data in my logs?

You can't read the transaction logs directly from SQL Server. Some products do allow you to read the logs, such as:

Informit Tutorials and Sample Chapters

You can read more about other uses for the transaction logs here.

Online Resources

You can learn more about the physical and virtual layout of the transaction log here.