Click here to monitor SSC
  • Av rating:
  • Total votes: 212
  • Total comments: 12
Robert Sheldon

Managing Transaction Logs in SQL Server

12 November 2009

The Transaction Log provides the means by which either the complete set of tasks of a database transaction are performed or none of them are. It ensures that , via rollback, only valid data is written out to the database, and it allows transactions to  be played back to recreate the system state right before a failure.  Robert Sheldon explains the various basic tasks involved in managing the transaction log.

Each database in a SQL Server instance has a log that records all database modifications. Because this log is written independently, before the modifications take place, the transaction log enables the database to roll back or restore transactions in the event of hardware failure or application error.  Because of the importance of its role, the transaction log is stored in one or more log files that are separate from the data files; the log records are written to the transaction log before the modified contents in the buffer cache are written to the data files.

For each database, the transaction log can support any of the following operations:

  • Rolling back individual transactions if a ROLLBACK statement is issued or the database engine detects an error.
  • Rolling back incomplete transactions that result from server failure. The transactions are rolled back when SQL Server is restarted.
  • Recovering incomplete transactions written to the logs but not to the data files as a result of server failure. The transactions are written to the data files when SQL Server is restarted.
  • Rolling forward a restored database, filegroup, file, or page to the point of failure in the event of hardware failure. The transactions are rolled forward after the latest full and differential backups are applied.
  • Supporting transactional replication, database mirroring, and log shipping.

The file (or files, if more than one file is used) that makes up the transaction log are divided into virtual log files whose size, along with their quantity in the physical log, is determined by the database engine. The database engine also decides when, and which, virtual files get truncated. You can, however, specify the minimum and maximum sizes of the physical log, as well as configure the growth increments used when expanding that file. In addition, you can add physical files to the log, delete files, increase the size of the log, and shrink the log.

In this article, I explain how to perform these tasks so you can begin to manage your transaction logs, and I provide examples that demonstrate each how each task works. For these examples, I used the following code to create the EmployeeDB database on a local instance of SQL Server 2008:

USE master;

 

IF EXISTS

(

  SELECT name FROM sys.databases

  WHERE name = 'EmployeeDB'

)

DROP DATABASE EmployeeDB;

 

CREATE DATABASE EmployeeDB

ON

(

  NAME = EmployeeDB_dat,

  FILENAME = 'C:\SqlData\EmployeeDb.mdf'

)

LOG ON

(

  NAME = EmployeeDB_log,

  FILENAME = 'C:\SqlData\EmployeeDb.ldf'

);

Notice that I created the database files in a location other than the default used by SQL Server. If you run this code, you can locate the database files wherever you think is appropriate. After I created the database, I used the following SELECT…INTO statement to retrieve data from the AdventureWorks2008 database and create the Employees table:

USE EmployeeDB;

 

IF OBJECT_ID ('Employees', 'U') IS NOT NULL

DROP TABLE dbo.Employees;

 

SELECT BusinessEntityID,

  FirstName,

  LastName,

  JobTitle,

  PhoneNumber,

  EmailAddress,

  AddressLine1,

  AddressLine2,

  City,

  StateProvinceName,

  PostalCode,

  CountryRegionName 

INTO dbo.Employees

FROM AdventureWorks2008.HumanResources.vEmployee;

You do not have to use this code to perform the examples in this database, but it does help to have a small test database that you can experiment with as you learn about transaction logs. If you plan to use a database other than the one shown here, simply insert the database name, where appropriate, in the code samples I provide.

Configuring the Recovery Model

Each SQL Server database includes the Recovery Model property, which determines how transactions are logged, whether the transaction log can be backed up, and the type of restore operations permitted. By default, a new database inherits the recovery model from the model database. However, you can override the default setting by assigning a different recovery model.

You can configure a SQL Server database with any one of the following three recovery models.

  • Simple: In this model, transaction log backups are not permitted, which means you do not have the administrative overhead associated with maintaining transaction log backups. The model also automatically reclaims log space, so there is almost no need to manage the transaction log space. However, this is also the riskiest of the models—a database can be restored only to the point of its last backup. Transactions that have been performed since the last backup are lost. This model is generally used for the system databases, and for both testing and development, although it is sometimes appropriate for a read-only database such as a data warehouse. In this model, some operations are only minimally logged.
  • Full: The log files can and should be backed up, as they provide full recoverability to a specific point in time. However, this model is less risky than the Simple model. In the Full recovery model, all operations are fully logged, including bulk import operations. The Full recovery model is generally the model used for production environments.
  • Bulk Logged: This model is intended as an adjunct to the Full recovery model because operations such as bulk import are only minimally logged. For example, you might want to bulk load data and you’re not concerned about logging these transactions because you can reload the data if necessary. In such cases, you can set the recovery model to Bulk Logged while importing the data, and then change the setting back to Full when you are finished. (Note that you should perform a full backup after you change the setting back to Full.)

You can switch the recovery model on a database by running an ALTER DATABASE statement and specifying the SET RECOVERY clause, as shown in the following example:

USE master;

 

ALTER DATABASE EmployeeDB

SET RECOVERY FULL;

As you can see, I am altering the EmployeeDB database and setting the recovery model to FULL. Note, however, that by default the model database is configured with the Full recovery model, which means that the EmployeeDB was automatically configured with the Full model because it inherited the setting from the model database. As a result, if the default recovery model wasn’t changed in the model database of your instance of SQL Server, the ALTER DATABASE example above did not change the setting. However, you should also note that if you switch a database from the Simple model to the Full model, there are other steps you must sometimes take, such as doing a full database backup. The topic "Considerations for Switching from the Simple Recovery Model" in SQL Server Books Online describes what steps you might need to take when switching from the Simple model to Full or Bulk Logged.

You can also set the recover model in SQL Server Management Studio. In Object Explorer, right-click the database name and then click Properties. In the Database Properties dialog box, click the Options page and then set the Recovery model property.

Monitoring the Log File

When maintaining a database’s transaction log, you’ll often want to retrieve information about the log so you can verify its settings or track how much log space is being used. One way to find information about the log is by using the sys.database_files catalog view. The view returns details about database files, including the type of file, the current size of the file, and how large the file is permitted to grow.

In the following example, I use the sys.database_files catalog view to retrieve data about the log file associated with the EmployeeDB database:

USE EmployeeDB;

 

SELECT name,

  size, -- in 8-KB pages

  max_size, -- in 8-KB pages

  growth,

  is_percent_growth

FROM sys.database_files

WHERE type_desc = 'LOG'

The statement returns the current size of the file (in 8-KB pages), the maximum size that the file is permitted to grow (also in 8_KB pages), the growth rate, and the is_percent_growth flag, which determines how the growth rate should be interpreted. If the flag is set to 0, the growth rate is the number of 8-KB pages. If the flag is set to 1, the growth rate is a percentage.

The above SELECT statement return results similar to the following:

Name            size  max_size  growth  is_percent_growth

EmployeeDB_log  128   268435456 10      1

As the results show, the statement returns only one row. That’s because the EmployeeDB database has only one log file associated with it. The results also indicate that the current size of the EmployeeDB_log file is 128 8-KB pages. However, the file can grow to 268,435,456 8-KB pages at a growth increment of 10%.

You can also use the DBCC SQLPERF statement to return information about the transaction logs for each database in a SQL Server instance. To retrieve log data, you must specify the LOGSPACE keyword in parentheses, as shown in the following example:

DBCC SQLPERF(LOGSPACE);

The statement returns the log size in MB, the percentage of log space used, and the status of the log for every database on your SQL Server instance. The following results show the information that the DBCC SQLPERF statement returns for the EmployeeDB database:

Database Name  Log Size (MB)  Log Space Used (%)  Status

EmployeeDB     0.9921875      40.05906            0

In this case, the EmployeeDB log is about 1 MB in size, and about 40% of the log space is being used.

You can also generate a report in SQL Server Management Studio that graphically displays data similar to the results of the DBCC SQLPERF statement. To generate the report, right-click the name of the database in Object Explorer, then point to reports, next point to Standard Reports, and finally click Disk Usage.

Backing Up the Log File

If a database is configured with the Full or Bulk Logged recovery model, you should back up the transaction log regularly so it can be truncated to free up inactive log space. The backup can also be used (along with the database backups) to restore the database in the event of failure.

Before a log file can be backed up, a full database backup must be performed. For instance, before I back up the log file I am using for the examples in this article, I will run the following BACKUP DATABASE statement on the EmployeeDB database:

BACKUP DATABASE EmployeeDB

TO DISK = 'E:\DbBackup\EmployeeDB_dat.bak';

Note that, if you run this code, make sure the TO DISK location exists, or specify a different location.

After I backed up the database, I ran the following data modification statements so the log would contain transactions not included in the backup:

USE EmployeeDB;

 

UPDATE Employees

SET JobTitle = 'To be determined';

 

UPDATE Employees

SET CountryRegionName = 'US'

WHERE CountryRegionName = 'United States';

 

DELETE Employees

WHERE BusinessEntityID > 5;

I then reran the DBCC SQLPERF statement to view the amount of log space being used. The statement returned the following results:

Database Name  Log Size (MB)  Log Space Used (%)  Status

EmployeeDB     0.9921875      64.41929            0

As you can see, the percentage of log space being used increased from about 40% to nearly 65%.

After the database has been backed up, you can back up the transaction log. To perform a transaction log backup, use the BACKUP LOG statement and specify the target destination for the backup files, as shown in the following example:

-- back up transaction log

BACKUP LOG EmployeeDB

TO DISK = 'E:\LogBackup\EmployeeDB_log.bak';

Note that, if you run this code, make sure the TO DISK location exists, or specify a different location.

Notice that I include the TO DISK clause to specify the file destination. However, the BACKUP statement supports other options for backing up data. See the topic “BACKUP (Transact-SQL)” in SQL Server Books Online for more information.

After you back up the transaction log, the SQL Server database engine automatically truncates inactive log space. (Truncating a log file removes inactive virtual log files, but does not reduce the file size. In addition, you cannot specifically truncate a log. You can, however, shrink the file, which does reduce the size. I explain how to shrink a log file later in the article). To verify whether the log has been truncated, run the DBCC SQLPERF statement again. This time, the results should be similar to the following:

Database Name  Log Size (MB)  Log Space Used (%)  Status

   EmployeeDB     0.9921875      44.88189            0

Now the percentage of log space being used is back down around 45%.

Modifying a Log File

You can use the ALTER DATABASE statement to modify a log file. You must specify the MODIFY FILE clause, along with the appropriate options. In addition to specifying the logical name of the log file, you can define the following three arguments:

  • SIZE: The new size of the log file. You can specify the size as KB, MB, GB, or TB, such as 10 MB or 1 GB. If you do not specify a size when you add the file, the database engine uses the default size of 1 MB. The new size must be greater than the current size, otherwise you’ll receive an error when you run the statement.
  • MAXSIZE: The maximum size that the file can grow to. You can specify the size as KB, MB, GB, or TB. If you do not specify a maximum size, the file will grow until it fills the disk (assuming the space is needed).
  • FILEGROWTH: The growth increment used when expanding the file. You can specify the size as KB, MB, GB, or TB, or you can specify the size as a percentage, such as 10%. If a number is specified without a suffix, MB is used. If no number is specified, 10% is used. A value of 0 indicates that no automatic growth is allowed.

The following ALTER DATABASE statement modifies the EmployeeDB_log file in the EmployeeDB database:

-- modify log file

ALTER DATABASE EmployeeDB

MODIFY FILE

(

    NAME = EmployeeDB_log,

    SIZE = 2MB,

    MAXSIZE = 200MB,

    FILEGROWTH = 10MB

);

As the statement shows, after I specify the logical name of the log file, I set the new size for the file (2 MB), the maximum size (200 MB), and the growth increment (10 MB).

After you run the ALTER DATABASE statement, you can they query the sys.database_files catalog view, to verify the changes. Your results should be similar to the following:

Name             size  max_size  growth  is_percent_growth

EmployeeDB_log   256   25600     1280    0

The file size is now 256 8-KB pages, the maximum size is 25,600 8-KB pages, and the growth increment is 1,280 8-KB pages.

Shrinking a Log File

As you’ll recall, in order to truncate the transaction log, you must first back up the log. The database engine then automatically truncates the inactive records. However, truncating the log doesn’t reduce its size. Instead, you must shrink the log file, which removes one or more inactive virtual log files.

To shrink a log file, you can run a DBCC SHRINKFILE statement that specifies the name of the log file and the target size, in MB. For example, the following DBCC SHRINKFILE statement shrinks the EmployeeDB_log file:

-- shrink log file

DBCC SHRINKFILE (EmployeeDB_log, 1);

The target size in this statement is 1 MB (128 8-KB pages). When you run the statement, the database engine will shrink the file down to that size, but only if there are enough inactive virtual log files.

After you run the statement, you can verify the extent to which a file was reduced by querying the sys.database_files catalog view, which should return results similar to the following:

Name             size  max_size  growth  is_percent_growth

EmployeeDB_log     128    25600       1280       0

As you can see, the size has been reduced from 256 8-KB pages to 128. If the database engine cannot free up the space, it issues a message that suggests steps you can take to free up log space. Follow the suggested steps and then rerun the DBCC SHRINKFILE statement.

Adding or Deleting a Log File

If you need to enlarge your transaction log, one method you can use is to add a file to the log.

You can do this by using the ADD LOG FILE clause of the ALTER DATABASE statement. In addition to specifying the logical and physical names of the new log file, you can define the following three arguments:

  • SIZE: The initial size of the log file. You can specify the size as KB, MB, GB, or TB, such as 10 MB or 1 GB. If you do not specify a size when you add the file, the database engine uses the default size of 1 MB.
  • MAXSIZE: The maximum size that the file can grow to. You can specify the size as KB, MB, GB, or TB. If you do not specify a maximum size, the file will grow until it fills the disk (assuming the space is needed).
  • FILEGROWTH: The growth increment used when expanding the file. You can specify the size as KB, MB, GB, or TB, or you can specify the size as a percentage, such as 10%. If a number is specified without a suffix, MB is used. If no number is specified, 10% is used. A value of 0 indicates that no automatic growth is allowed.

The following example adds the EmployeeDB_log2 file to the EmployeeDB transaction log:

ALTER DATABASE EmployeeDB

ADD LOG FILE

(

    NAME = EmployeeDB_log2,

    FILENAME = 'C:\SqlData\EmployeeDB2.ldf',

    SIZE = 2MB,

    MAXSIZE = 50MB,

    FILEGROWTH = 10%

);

Notice that I first specify the logical and physical file names, and then define the initial size, maximum size, and growth increment. After I run this statement, I can confirm that the file has been added to the log by querying the sys.database_files catalog view (using the same query as I used previously), which returns the following results:

Name             size  max_size  growth  is_percent_growth

EmployeeDB_log   128   268435456 10      1

EmployeeDB_log2  256   6400      10      1

As the results indicate, the EmployeeDB_log2 file has been added to the database with an initial size of 256 8-KB pages, a maximum size of 6,400 8-KB pages, and a growth increment of 10%.

You can also use the ALTER DATABASE statement to remove a log file by specifying the REMOVE FILE clause, as shown in the following example:

ALTER DATABASE EmployeeDB

REMOVE FILE EmployeeDB_log2;

To determine whether the file has been removed, you can once again query the sys.database_files catalog view, which returns the following results:

 

Name             size  max_size  growth  is_percent_growth

EmployeeDB_log   128   268435456 10      1

EmployeeDB_log2  1     6400      10      1

Notice that the EmployeeDB_log2 file is still listed, but the size has been set to 1 8-KB page. The physical file has been deleted, but the logical file is still associated with the database. You must back up the transaction log before the logical file is removed. After you back up the log, you can again query the sys.database_files catalog view. This time your results should look similar to the following:

Name             size  max_size  growth  is_percent_growth

EmployeeDB_log   128   268435456 10      1

As you can see, the logical file has been removed.

Conclusion

Clearly, transaction logs play an important role in SQL Server databases, and the information above should provide you with an introduction on how to work with them. What I have not covered, however, are the ways that the transaction log is used to support transactional replication, database mirroring, and log shipping. I also have not covered how to use the transaction log and its backups to restore a database. These topics each deserve their own article. But you should at least now have a basic foundation in transaction logs and be able to start working with them. However, I highly recommend that you check out the various topics in SQL Server Books Online on transaction logs as well as other sources on the subject so you have a complete picture of how the logs work and how they’re best managed.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 212 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Excellent read !
Posted by: marc_scheuner (view profile)
Posted on: Sunday, November 15, 2009 at 3:04 PM
Message: Thanks for another great SQL Server related article - wonderful read as always!

Subject: getting info from the log file
Posted by: Anonymous (not signed in)
Posted on: Monday, November 16, 2009 at 4:40 PM
Message: It would be good to know how to extract information from the log file, say the last few deleted records or a deleted stored procedure.

Subject: Simple and best
Posted by: selvaraj (view profile)
Posted on: Wednesday, November 18, 2009 at 1:02 AM
Message: Hi, I am new to sql server.This article is very impressed to me.Particularly each topic with simple understanding example.Great.Thanks

Subject: Great reading
Posted by: Luis Faustino (not signed in)
Posted on: Wednesday, November 18, 2009 at 6:04 AM
Message: Thanks for posting this, it was really nice to read.

Best regards,
Luis

Subject: introduction to transaction log, simple and useful
Posted by: abhijit (view profile)
Posted on: Monday, November 23, 2009 at 2:49 AM
Message: Thanks for posting this useful article on transaction log.
Looking forward to more such article, especailly maintianing transaction log in database mirroring

regards
abhijit

Subject: Good Article
Posted by: Edwin (not signed in)
Posted on: Monday, November 23, 2009 at 5:27 AM
Message: Realy good one robert, looking foward for some more

Subject: Wonderfull Description about Translog
Posted by: Prem Chander (not signed in)
Posted on: Monday, November 23, 2009 at 7:11 AM
Message: I have never seen such an simple and brief explanation about Translogs in Sql Server . Great stuff by Robert. Awaiting for much more interesting topics in Administration of Sql Server.

Subject: simple and clear
Posted by: max (view profile)
Posted on: Monday, November 23, 2009 at 10:48 AM
Message: good job, I always like article like this

Subject: Very clearly explained ...
Posted by: Walter (not signed in)
Posted on: Tuesday, November 24, 2009 at 4:13 AM
Message: I struggled to explain to my co-workers about which environment (dev/test/prod) should probably use which recovery model. I'll now just point them to this article. Thanks.

Subject: Clearly explained
Posted by: Anonymous (not signed in)
Posted on: Tuesday, November 24, 2009 at 6:10 PM
Message: It's a great article which explains the transactional log in simple terms. Looking forward more articles

Subject: good and informative
Posted by: venky (not signed in)
Posted on: Friday, November 27, 2009 at 1:56 AM
Message: useful article

Subject: Great Article
Posted by: Patelsan (view profile)
Posted on: Friday, December 11, 2009 at 1:36 PM
Message: It is a great article and explain in simple words. Great work

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.