Managing Transaction Logs in SQL Server
12 November 2009 by Robert Sheldon
-------------------------------------------------
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