How to move your SQL Server transaction log?

How to move your SQL Server transaction log?

Why would you move your transaction log file?  Maybe your tran log has outgrown the disk that it is on, and you need to move to another location to prevent failure.  Or, you may be moving the log to another physical drive, separate from the data, to improve your I/O performance.  Regardless of the reason, this can be done via SSMS (SQL Server Management Studio) or tSQL, and I will show you the tSQL approach in this tip.  Be sure to complete these steps beforehand:

  - Use sp_helpfile to note the current location, size and name of the existing files.
  - Determine the location and name of the new file location. 
  - Backup your database.

Ok.  This is how you move your tran log, adjusting your drive letter and directory names accordingly:
 
     /* Take exclusive access to your database. */
     ALTER DATABASE YourDatabaseName 
      SET OFFLINE WITH ROLLBACK IMMEDIATE;
 
     /* In Windows Explorer, copy/paste your files to new location, delete the old files. */

     /* ALTER your database, use MODIFY FILE for new file location. */
     ALTER DATABASE YourDatabaseName
     MODIFY FILE (
     NAME='YourDatabaseName_Log',
     FILENAME='X:\NewDirectory\NewSubdirectory\YourDatabaseName_Log.ldf');

     /* Bring your database back online. */
     ALTER DATABASE YourDatabaseName
     SET ONLINE;
  
That's it!

But what about sp_detach_db and sp_attach_db?  The relocation of the transaction log can be done with sp_detach_db/sp_attach_db in SQL Server versions pre 2012.  These commands were deprecated, however, in v2005.  If you are in v2005 or v2008, the file relocation can be done using this method, again adjusting the drive letter and directory names accordingly:
 
    USE MASTER
    GO
    /* Take exclusive access to your database. */
    ALTER DATABASE YourDatabaseName
     SET SINGLE_USER
     GO 
 
    /* Detach your database. */
    EXEC sp_detach_db 'YourDatabaseName'
    GO

     /* In Windows Explorer, copy/paste your files to new location, delete the old files. */

    /* Re-attach your database. */
    EXEC sp_attach_db 'YourDatabaseName', 
    'X:\NewDirectory\NewSubdirectory\YourDatabaseName_Data.mdf',
    'X:\NewDirectory\NewSubdirectory\YourDatabaseName_Log.ldf'
    GO

SQL Server 2005 Error Log Management

SQL Server Error Log
To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log.  Depending on the growth rate of the SQL Server error log dictates when sp_cycle_errorlog should be issued.  Reference the code below as an example.

EXEC master.sys.sp_cycle_errorlog;-- Expected successful output -- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Next, the easiest means to address this need would be to schedule a SQL Server Job to support the need.  Reference the SQLServer2005_CycletheErrorLog_Job.txt as a point of reference.

SQLServer2005_CycletheErrorLog_Job.txt

USE [msdb]
GO
/****** Object:  Job [Sample - sp_cycle_errorlog]   ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Sample - sp_cycle_errorlog', 
  @enabled=0, 
  @notify_level_eventlog=0, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'Sample job to recycle the SQL Server Error Log                                    *** NOTE *** - This job must be enabled and a schedule must be setup in order to execute this Job', 
  @category_name=N'[Uncategorized (Local)]', 
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Recycle the SQL Server Error Log]    ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recycle the SQL Server Error Log', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'EXEC master.sys.sp_cycle_errorlog;', 
  @database_name=N'master', 
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Finally, to address not loosing the historical SQL Server error log, the number of logs should be expanded beyond the default of 7.  The maximum number of logs is 99 for the SQL Server error log.  When it comes to expanding the number of SQL Server error logs, follow these steps:

• Open Management Studio
• Navigate to root | Management folder | SQL Server Logs folder
• Right click on the SQL Server Logs folder and select the 'Configure' option
• Select the 'Limit the number of error log files before they are recycled' check box
  • Reference the screen shot below as a point of reference
• Enter the desired number of error logs in the 'Maximum number of error log files'
  • Reference the screen shot below as a point of reference   
• Press the 'OK' button to save the configuration

Alternatively, the following code can be used to automate the process across multiple SQL Servers:

USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 50 GO

SQL Server Agent Error Log
The SQL Server Agent error log follows much of the same paradigm as the SQL Server error log.  The recycling process is completed by the sp_cycle_agent_errorlog in the MSDB database.

EXEC msdb.dbo.sp_cycle_agent_errorlog; -- Expected successful output -- Command(s) completed successfully.

In terms of configuring a specified number of SQL Server Agent error logs, this is not possible.  Only the current and 9 additional (a total of 10) SQL Server Agent error logs will be retained.  What can be configured are the type of messages (Errors, Warnings, Informational ) that are written to the SQL Server Agent error logs.  To access this interface, follow these steps:

• Open Management Studio
• Navigate to root | SQL Server Agent | Error Logs folder
• Right click on the Error Logs folder and select the 'Configure' option, reference the screen shot below

To automate this process across servers, reference the sp_set_sqlagent_properties system stored procedure.  Below outlines a sample execution.

USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7 GO

Alternative Error Log Access
The primary interface to access the SQL Server Error Logs is via the Log File Viewer.  This application provides insight into the SQL Server and Windows logs.  This application provides a means to review multiple logs as well as search and filter the logs.  If you do have a large error log that causes issues for this application, this should be copied and pasted and then the copied log can be reviewed via DOS's type command.  Although this is a less than ideal interface, it appears to provide immediate access and will not affect SQL Server writing new records to the original log.  Below is an example of reading a SQL Server Error log via DOS's type command.

Reading the SQL Server log files using TSQL

One of the issues I have is that the SQL Server Error Log is quite large and it is not always easy to view the contents with the Log File Viewer.  In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method of searching the error log using VBScript.  Are there any other easy ways to search and find errors in the error log files?
SolutionSQL Server 2005 offers an undocumented system stored procedure sp_readerrorlog.  This SP allows you to read the contents of the SQL Server error log files directly from a query window and also allows you to search for certain keywords when reading the error file.  This is not new to SQL Server 2005, but this tip discusses how this works for SQL Server 2005.
This is a sample of the stored procedure for SQL Server 2005.  You will see that when this gets called it calls an extended stored procedure xp_readerrorlog.

CREATE PROC [sys].[sp_readerrorlog](    @p1     INT = 0,    @p2     INT = NULL,    @p3     VARCHAR(255) = NULL,    @p4     VARCHAR(255) = NULL) AS BEGIN    IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)    BEGIN       RAISERROR(15003,-1,-1, N'securityadmin')       RETURN (1)    END       IF (@p2 IS NULL)        EXEC sys.xp_readerrorlog @p1    ELSE        EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4 END

This procedure takes four parameters:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results

If you do not pass any parameters this will return the contents of the current error log.
Here are a few examples:
Example 1

EXEC sp_readerrorlog 6

This statement returns all of the rows from the 6th archived error log.


Example 2

EXEC sp_readerrorlog 6, 1, '2005'

This returns just 8 rows wherever the value 2005 appears.

Example 3

EXEC sp_readerrorlog 6, 1, '2005', 'exec'

This returns only rows where the value '2005' and 'exec' exist.

---

xp_readerrrorlog
Even though sp_readerrolog accepts only 4 parameters, the extended stored procedure accepts at least 7 parameters.
If this extended stored procedure is called directly the parameters are as follows:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time  
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending

EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'desc' EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'asc'

Examples:

Exec sp_readerrorlog

The above command all the contents from current SQL Server errorlog.

Exec sp_readerrorlog 1,1,‘memory’

The above command reads the first SQL Server Archived Error log and searches for returns the result wherever “memory” appears

Reading the SQL Server log files - Part2
How to read the SQL Server Database Transaction Log

I will explain how to read your database transaction log file and how transactions are written for your database if you perform any database activity. This tip is helpful if you want to know what the transaction log file captures. There is an undocumented function called "fn_dblog" which enables you to read data from your transaction log which contains very informative data about things that are happening in your database. I strongly suggest testing any undocumented features in a lab environment first.
The function (fn_dblog) requires a beginning LSN and ending LSN for a transaction.  NULL is the default for this function and this will return all log records from the transaction log file.

Create a Test Database

To show how this works, we will create a database and a table to play with this function. Run the below SQL code to create a database and table.

--Create DB.
USE [master];
GO
CREATE DATABASE ReadingDBLog;
GO
-- Create tables.
USE ReadingDBLog;
GO
CREATE TABLE [Location] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Bangalore');

We have created a database named "ReadingDBLog" and a table 'Location' with three columns. Now you can check all information and processes which have been used by SQL Server to create the database and table. We will run the below code to check the log file for this newly created database to check what processes and steps SQL Server took to create the database and table.

USE ReadingDBLog;
GO
select COUNT(*) from fn_dblog(null,null)

We can see there are 339 rows that have been generated for just creating a dummy database and a blank table. You can go and check the logs by using this function to get details for all processes used to create the database and table. Look at the below code to see the data in the transaction log file.

USE ReadingDBLog;
GO
select [Current LSN],
       [Operation],
       [Transaction Name],
       [Transaction ID],
       [Transaction SID],
       [SPID],
       [Begin Time]
FROM   fn_dblog(null,null)

As the total number of rows and columns are large, I gave a snapshot of some of the columns and rows but I have tried to capture a few informative columns here. You can see in the above screenshot that the transaction name column shows the database name, similarly it will show the create table for the table creation code. Transaction ID is the same for all parts of a transaction. The value for transaction name will be filled only when the particular transaction starts with "LOP_BEGIN_XACT" in the Operation column. "LOP_BEGIN_XACT" means begin transaction. The operation column will let us know which operation is performing like an insert, update, delete, shrink, lock, page allocation etc...  It is pretty easy to understand the operation based on these key words to see what operation is being performed by SQL Server.

Run some DML commands to see what is captured

Now we will run a few DML scripts to check how data insertion, updating or deletion is logged in the database log file. During this operation you can also track how a page is allocated or de-allocated.

USE ReadingDBLog
go
INSERT INTO Location DEFAULT VALUES ;
GO 100
GO
UPDATE Location
SET City='New Delhi'
WHERE [Sr.No]<5
GO
DELETE Location 
WHERE [Sr.No]>90
Go

Let's check our database log file again. As we saw from above, there is a lot of info logged in the transaction log file, so I will filter the data.

USE ReadingDBLog
go
SELECT
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN 
   ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
    'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')  

Your output will look something like the above screenshot after running the above script.
Let's see how we can read the output. You can see the highlighted area which we will read as:

• INSERT statement with transaction ID 0000:00000448
• started at 2013/09/27 16:50:44:530
• INSERTed a row in a HEAP table 'dbo.Location' in pageID 0001:00000099
• finished at 2013/09/27 16:50:44:530

 Similarly, it will show you this same kind of information for UPDATE and DELETE statements.

Finding internal SQL Server operations in transaction log

We can also check internal functions of SQL Server through this function. Let's take for example page splitting. We can track all info about page splits like how many times page splits occur, on which page and during which operation. Let's take a look at this below to see page splits for the above INSERT operation. Run the below SQL code to get all operations related to page splits.

USE ReadingDBLog
go
--Get how many times page split occurs.
SELECT 
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction Name]='SplitPage' 
GO

--Get what all steps SQL Server performs during a single Page Split occurrence.
SELECT 
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction ID]='0000:00000451'  

As you can see above Page Split occur three times for the above DML activity. The first script will let us know how many times page splits occurred and the second script elaborates on the internal processes SQL Server ran during a single Page Split operation. The above image takes the first page split, transaction ID '0000:00000451', and shows all internal processes in the second result set.
Similarly if you drop any object or you perform other database operations they will get logged in the transaction log file which will then be visible using this function.

How a backup interacts with the transaction log

Now I will run a backup and see the transaction log file again. Run a backup on this database and then again check the transaction log file.

SELECT COUNT(*)
FROM fn_dblog(null,null)
GO

BACKUP DATABASE ReadingDBLog TO DISK = 'c:\ReadingDBLog_Full.bak'
GO

SELECT COUNT(*)
FROM fn_dblog(null,null)
GO

As we can see, the number of rows has been drastically reduced after doing a backup and it has been reduced to 9 rows from 528. This means the inactive part of the log which tracked the transactions has been dumped to a backup file and the original entries from the log file have been flushed. Now you can shrink the log file if necessary.

Other things you can do

You can also use the DBCC Log() command to see log information, but this command will not give you detail information. You can also use trace flag 2537 to look at all logs and not just the active log.

Automating daily transaction log reading

 

One of the common questions our customers have about auditing is – how to automate transaction log reading and have all the information you might need at hand. Another common request is – how to save the transactions I see in the ApexSQL Log grid, so I can easily filter, group, order and query them without running the whole ApexSQL Log reading process again? In case of large transaction logs, the reading process may take a long time, and I want to avoid running it each time I want to see the transactions

There’s no out-of-the-box solution for these requests. What you can do is read the transaction logs and store the transactions read in SQL tables in advance, so you can query the transactions just like any other SQL data

This is where the ApexSQL Log feature to export transactions into SQL or SQL bulk scripts can help

Export to SQL bulk creates three files. When executed, they insert the transactions read from the transaction logs into the APEXSQL_LOG_OPERATION_DETAIL and APEXSQL_LOG_OPERATION tables

The files created are:

• SQL that creates the APEXSQL_LOG_OPERATION_DETAIL and APEXSQL_LOG_OPERATION tables, and contains BULK INSERT commands. By default, its named export.sql

  BULK INSERT APEXSQL_LOG_OPERATION
   FROM 'E:\test\Operations_05-16-2013.sql'
   WITH(DATAFILETYPE = 'widechar')
  
  BULK INSERT APEXSQL_LOG_OPERATION_DETAIL
   FROM 'E:\test\Details_05-16-2013.sql'
   WITH(DATAFILETYPE = 'widechar')
  GO

• A bulk file for the APEXSQL_LOG_OPERATION table – by default named exportOperations, contains the information about the operation type, object affected, schema name, user name, etc. This is the information shown in the upper part of the ApexSQL Log grid
• A bulk file for the APEXSQL_LOG_OPERATION_DETAIL table – by default named exportDetails, contains the LSN, column name and type, old and new values. This is the information shown on the ApexSQL Log Operation details tab

We recommend running ApexSQL Log every night, reading the transactions that happened in the last 24 hours, creating SQL BULK and immediately executing it to store the read transactions into SQL tables. This way even if you don’t keep the old transaction log backups, you will have their content stored in 2 SQL tables

1. Create a batch file using the statement in this format:
“<ApexSQL Log installation path>\ApexSQLLog.com” /server:<server name> /database:<database name> /trusted /ec:begintime endtime lsn operation schema object transactionid user /bulk:<file1 path and name>.sql :<file2 path and name>.sql:<file3 path and name>.sql /operations:DMLALL DDLALL /time_period:24hours /backup:<transaction log backups path and names> /db_backup: :<database backups path and names> /v /f
The /backup and /db_backup switches support wildcards, and you can leverage that with the Command Line expansion to add to the auditing process only those transaction log and database backups created during the day
To add to the auditing process only those .trn files with names that start with e.g. AW2012_0516, such as

use
/backup:E:\Test\Backup\AW2012_%date:~4,2%%date:~7,2%*.trn
To add only the full database backups created during the day, with names that start with, for example, AW2012_0516, such as J, AW2012_05162013_0100.bak, use:
/db_backup:E:\Test\Backup\AW2012__%date:~4,2%%date:~7,2%*.bak
For instance, to read the transactions that happened in the last 24 hours in the AdventureWorks2012 database on the SQL Server instance Fujitsu\SQL2012, add only the backups created during the day, and create files in the E:\test folder that contain the creation date in their names:
“C:\Program Files\ApexSQL\ApexSQLLog2011test1088\ApexSQLLog.com” /server:FUJITSU\SQL2012 /database:AdventureWorks2012 /trusted /ec:begintime endtime lsn operation schema object transactionid user /bulk:E:\test\SQLBulk_%date:~4,2%-%date:~7,2%-%date:~10,4%.sql E:\test\Operations_%date:~4,2%-%date:~7,2%-%date:~10,4%.sql E:\test\Details_%date:~4,2%-%date:~7,2%-%date:~10,4%.sql /operations:DMLALL DDLALL /time_period:24hours /v /f /backup:E:\Test\Backup\AW2012_%date:~4,2%%date:~7,2%*.trn /db_backup:E:\Test\Backup\AW2012__%date:~4,2%%date:~7,2%*.bak
Keep in mind that the selected transaction log and database backups must form a full chain
Save the batch file with the bat extension, for example E:\Test\batch.bat
2. The next step is to schedule the execution of this batch using SQL Server jobs Start SQL Server Management Studio and create a new job
   
3. Specify the job name and add steps. In the first step, paste the following:

   DECLARE @Str VARCHAR(100) 
   SET @Str = 'E:\Test\Batch.bat ' 
   EXEC master.dbo.XP_CMDSHELL @Str

   
4. Add the following SQL to the second step

   DECLARE @SQLStatement VARCHAR(2000)
   SET @SQLStatement = 'sqlcmd -S Fujitsu\SQL2012 -d AdventureWorks2012 -i 
   E:\test\SQLBulk_' + CONVERT(nvarchar(30), GETDATE(), 110) +'.sql'
   EXECUTE master.dbo.xp_cmdshell @SQLStatement

   
It will use the Command Shell to execute the created SQL file and BULK insert transactions into SQL tables
5. Create a new schedule for the job, and specify its frequency and the time when it starts
   
6. Right-click the job, select Start Job at Step, and select the first step to test the job

If the job is executed successfully, you’re all set

Reading database transaction logs doesn’t have to take up any of your time. Schedule the reading to occur every night – have ApexSQL Log read all transaction log backups and full database backups created during the day, filter them for the last 24 hours, and insert the transactions into the APEXSQL_LOG_OPERATION and APEXSQL_LOG_OPERATION_DETAIL tables. Then when you arrive to your office in the morning, the transactions stored in the SQL tables will be awaiting for your queries

SQL Server Backup Types

Full

Full backups in SQL Server take a complete picture of the entire database.  To restore the entire database all you need is just the full backup.

Differential

Differential backups are a copy of all the changes to the database since the last full backup.  To restore a differential, you must first restore the full backup, then apply a single differential backup.

Transaction Log

Transaction log backups contain a backup of all of the changes since the last transaction log backup.  In order to restore a database to a point in time, transaction logs are usually your best option, but the do require more work.

The way to get a database restore of a transaction log is to do the following:

1.  Apply the last full backup prior to the point in time.

2. If you are using differential backups, apply the last differential backup prior to the point in time.

3.  Apply all the transaction logs in the right order from the last full (or last differential if using differential backups).

Here is an example of a backup restore script created with the Database Health Reports backup advisor:

use [master]; ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_010003_9840657.bak'    WITH REPLACE, NORECOVERY; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_123006_2581403.dif'    WITH NORECOVERY; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_160006_7308461.trn'    WITH NORECOVERY; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_200003_0942710.trn'    WITH NORECOVERY; RESTORE DATABASE AdventureWorks2012 WITH RECOVERY; ALTER DATABASE AdventureWorks2012 SET MULTI_USER;

Understanding Logging and Recovery in SQL Server

• How logging and recovery function in SQL Server
• How the transaction log works and what you need to know about managing it
• Recovery models and their effect on logging

What Is Logging? 
What Is Recovery? 
The Transaction Log 
Recovery Models 
Wrapping Up

Some of the most misunderstood parts of SQL Server are its logging and recovery mechanisms. The fact that the transaction log exists and can cause problems if not managed correctly seems to confound many "involuntary DBAs." Why is it possible for the transaction log to grow unbounded? Why does it sometimes take so long for the database to come online after a system crash? Why can't logging be turned off completely? Why can't I recover my database properly? Just what is the transaction log and why is it there?

These are all questions I see repeatedly on SQL Server forums and newsgroups, so in this article I want to provide an overview of the logging and recovery system and explain why it is such an integral part of the SQL Server Storage Engine. I will explain the architecture of the transaction log and how the three recovery models available for a database can change the behavior of the transaction log and the logging process itself. Along the way, I'll also provide some links to resources covering transaction log management best practices.

What Is Logging?

Logging and recovery are not concepts that are unique to SQL Server—all commercial relational database management systems (RDBMSs) must have them to support the various ACID properties of transactions. ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the fundamental properties of a transaction-processing system (such as an RDBMS). You can read more about this in the ACID Properties section of the MSDN Library.

A set of one or more such changes can be (and in fact, always are) grouped together in a transaction—which provides the basic unit of making changes (atomicity) to a database as far as users, application developers, and DBAs are concerned. A transaction either succeeds (committing) or fails/is canceled (rolling back). In the first case, the operations that form the transaction are guaranteed to be reflected in the database. In the second case, the operations are guaranteed not to be reflected in the database.Operations in an RDBMS are logged (or recorded) at the physical and logical level in terms of what happens in the storage structures of the database. Each change to the storage structures has its own log record, which describes the structure being changed and what the change was. This is done in such a way that the change can be replayed or reversed, if necessary. The log records are stored in a special file called the transaction log—I will describe this in more detail later, but for now you can think of it as a sequential-access file.

Transactions in SQL Server are either explicit or implicit. An explicit transaction is one where the user or application issues a BEGIN TRANSACTION T-SQL statement, signaling the start of a group of related changes by that session. An explicit transaction succeeds when a COMMIT TRANSACTION statement is issued, signaling the successful completion of the group of changes. If a ROLLBACK TRANSACTION statement is issued instead, all changes made by that session since the BEGIN TRANSACTION statement was issued are reverted (rolled back) and the transaction is aborted. A transaction rollback could also be forced by an external event, such as the database running out of disk space or a server crash, as I will explain later.

An implicit transaction is one where the user or application does not explicitly issue a BEGIN TRANSACTION statement before issuing a T-SQL statement. However, given that all changes to the database must be transactional, the Storage Engine will automatically start a transaction under the covers. When the T-SQL statement completes, the Storage Engine automatically commits the transaction that it started to wrap around the user's statement.

You might think that this is not necessary because a single T-SQL statement won't generate a large number of changes to the storage structures of the database, but consider something like an ALTER INDEX REBUILD statement. Although this statement can't be contained within an explicit transaction, it could generate an enormous number of changes to the database. So there must be some mechanism for ensuring that if something does go wrong (the statement is canceled, for instance), all of the changes are properly reversed.

As an example, consider what happens when a single table row is updated in an implicit transaction. Imagine a simple heap table with an integer column c1 and a char column c2. The table has 10,000 rows, and a user submits an update query as follows:

UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';

The following operations take place:

• The data pages from SimpleTable are read from disk into memory (the buffer pool) so they can be searched for matching rows. It turns out that three data pages hold five rows that match the WHERE clause predicate.
• The Storage Engine automatically starts an implicit transaction.
• The three data pages and five data rows are locked to allow the updates to occur.
• The changes are made to the five data records on the three data pages in memory.
• The changes are also recorded in log records in the transaction log on disk.
• The Storage Engine automatically commits the implicit transaction.

Note that I didn't list a step where the three updated data pages are written back out to disk. This is because they don't yet need to be; as long as the log records describe the changes are on disk in the transaction log, then the changes are protected. If the pages need to be subsequently read or changed again, then the most up-to-date copy of the page is already in memory, just not on disk (yet). The data pages will be written out to disk when the next checkpoint operation occurs or if the memory that they use in the buffer pool is required for another page image.

Checkpoints exist for two reasons—to batch up write I/Os to improve performance and to reduce the time required for crash recovery. In terms of performance, if a data page were forced out to disk each time it was updated, the number of write I/Os occurring on a busy system could easily overwhelm the I/O subsystem. It's better to periodically write out dirty pages (pages that have been changed since being read from disk) than to write out pages immediately as they are changed. I'll discuss the recovery aspect of checkpoints in a moment.

One common misconception about checkpoints is that they only write out pages with changes from committed transactions. This is not true—a checkpoint always writes out all dirty pages, regardless of whether the transaction that changed a page has committed or not.

Write-ahead logging is the mechanism wherein the log records describing a change are written to disk before the changes themselves are written. It provides the durability part of the ACID properties. As long as the log records describing changes are on disk, in the event of a crash, the log records (and hence the changes themselves) can be recovered and the effects of the transaction are not lost.

What Is Recovery?

The majority of these uses of logging involve a mechanism called recovery. Recovery is the process of having the changes described in log records replayed or reverted in the database. Replaying log records is called the REDO (or roll forward) phase of recovery. Reverting log records is called the UNDO (or roll back) phase of recovery. In other words, recovery will make sure that a transaction and all its constituent log records are either redone or undone.Logging exists to support a variety of operations in SQL Server. It ensures that if a crash occurs, a committed transaction will be correctly reflected in the database after the crash. It ensures that an uncommitted transaction will be correctly rolled back and not reflected in the database after a crash. It ensures that it is possible to cancel an in-flight transaction and have all its operations rolled back. It allows a backup copy of the transaction log to be taken so that a database can be restored and the transaction log backups replayed to bring the database to a specific point in time with transactional consistency. And it supports features that rely on reading the transaction log, such as replication, database mirroring, and change data capture.

The simple form of recovery is when a single transaction is canceled, in which case it is undone and there is no net effect on the database. The most complex form is crash recovery—when SQL Server crashes (for whatever reason) and the transaction log must be recovered to bring the database to a transactionally consistent point. This means that all transactions that were committed at the time of the crash must be rolled forward to ensure their effects are persisted in the database. And all in-flight transactions that had not committed at the time of the crash must be rolled back to ensure their effects are not persisted in the database.

This is because there is no facility for a transaction in SQL Server to continue after a crash. Thus, if the effects of a partially complete transaction were not rolled back, the database would be left in an inconsistent state (possibly even structurally corrupt, depending on what the transaction was in the middle of doing).

So how does recovery know what to do? All recovery processes depend on the fact that each log record is stamped with a log sequence number (LSN). A log sequence number is an ever-increasing, three-part number that uniquely defines the position of a log record within the transaction log. Each log record in a transaction is stored in sequential order within the transaction log and contains the transaction ID and the LSN of the previous log record for the transaction. In other words, each operation that is recorded as part of the transaction has a "link" back to the operation that immediately preceded it.

For the simple case of a single transaction being rolled back, the recovery mechanism can easily and quickly follow the chain of logged operations from the most recent operation back to the first operation and undo the effects of the operations in the opposite order from which they occurred. The database pages that were affected by the transaction are either still in the buffer pool or on disk. In either case, the image of the page that is available is guaranteed to be one where the effect of the transaction is reflected on the page and must be undone.

During crash recovery, the mechanism is more complicated. The fact that database pages are not written to disk when a transaction commits means that there is no guarantee that the set of database pages on disk accurately reflects the set of changes described in the transaction log—either for committed or uncommitted transactions. However, there is one final piece of the puzzle that I haven't mentioned yet—all database pages have a field in their page header (a 96-byte portion of the 8192-byte page that contains metadata about the page) that contains the LSN of the last log record that affected the page. This allows the recovery system to decide what to do about a particular log record that it must recover:

• For a log record from a committed transaction where the database page has an LSN equal to or greater than the LSN of the log record, nothing needs to be done. The effect of the log record has already been persisted on the page on disk.
• For a log record from a committed transaction where the database page has an LSN less than the LSN of the log record, the log record must be redone to ensure the transaction effects are persisted.
• For a log record from an uncommitted transaction where the database page has an LSN equal to or greater than the LSN of the log record, the log record must be undone to ensure the transaction effects are not persisted.
• For a log record from an uncommitted transaction where the database page has an LSN less than the LSN of the log record, nothing needs to be done. The effect of the log record was not persisted on the page on disk and as such does not need to be undone.

Crash recovery reads through the transaction log and ensures that all effects of all committed transactions are persisted in the database, and all effects of all uncommitted transactions are not persisted in the database—the REDO and UNDO phases, respectively. Once crash recovery completes, the database is transactionally consistent and available for use.

I mentioned earlier that one of the uses of a checkpoint operation is to reduce the amount of time that crash recovery takes. By periodically flushing out all dirty pages to disk, the number of pages that have changed because of committed transactions but whose images are not on disk is reduced. This, in turn, reduces the number of pages that need to have REDO recovery applied during crash recovery.

The Transaction Log

Crash recovery is only possible if the transaction log is intact. In fact, the transaction log is the most important part of the database—it's the only place where all changes to the database are guaranteed to be described in the event of a crash.

If the transaction log is missing or damaged after a crash, then crash recovery cannot complete, leading to a suspect database. In that case, the database must be restored from backups or recovered using less desirable options, such as emergency mode repair. (These procedures are beyond the scope of this article but will be covered in depth in articles later in the year.)

The transaction log is a special file that each database must have to function correctly. It holds the log records that are produced from logging and is used to read them again during recovery (or any of the other uses of logging I've already mentioned). As well as the space occupied by the log records themselves, a transaction will also reserve space in the transaction log for any potential log records required if the transaction were to be canceled and required to roll back. This accounts for the behavior you may observe where, say, a transaction that updates 50MB of data in the database may actually require 100MB of transaction log space.

When a new database is created, the transaction log is essentially empty. As transactions occur, log records are written sequentially to the transaction log, which means there is no performance gain from creating multiple transaction log files—a very common misconception. The transaction log will use each log file in turn.

Log records for concurrent transactions can be interspersed in the transaction log. Remember that log records for a single transaction are linked by their LSNs, so there is no need for all log records for a transaction to be grouped together in the log. LSNs can almost be thought of as a timestamp.

The physical architecture of the transaction log is shown in Figure 1. It is split internally into smaller chunks called virtual log files (or VLFs). These are simply an aid to easier internal management of the transaction log. When a VLF becomes full, logging automatically proceeds to use the next VLF in the transaction log. You might think that eventually the transaction log will run out of space, but this is where the transaction log is so different from data files.

Figure 1 Physical architecture of the transaction log

The transaction log is really a circular file—as long as the log records at the start of the transaction log have been truncated (or cleared). Then when logging reaches the end of the transaction log, it wraps around to the start again and begins overwriting what was there before.

So how do log records get truncated so the space they occupied can be reused? A log record is no longer needed in the transaction log if all of the following are true:

• The transaction of which it is part has committed.
• The database pages it changed have all been written to disk by a checkpoint.
• The log record is not needed for a backup (full, differential, or log).
• The log record is not needed for any feature that reads the log (such as database mirroring or replication).

A log record that is still needed is called active, and a VLF that has at least one active log record is also called active. Every so often, the transaction log is checked to see whether all the log records in a full VLF are active or not; if they are all inactive, the VLF is marked as truncated (meaning the VLF can be overwritten once the transaction log wraps). When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused.

This process is called log truncation—not to be confused with actually shrinking the size of the transaction log. Log truncation never changes the physical size of the transaction log—only which portions of the transaction log are active or not. Figure 2 shows the transaction log from Figure 1after truncation has occurred.

Figure 2 The transaction log after log truncation

Active VLFs make up the logical log—the portion of the transaction log that contains all the active log records. The database itself knows where crash recovery should start reading log records within the active portion of the log—the start of the oldest active transaction in the log, the MinLSN (this is stored in the database boot page).

Crash recovery does not know where to stop reading log records, so it continues until it reaches a zeroed section of the transaction log (if the transaction log has not yet wrapped) or a log record whose parity bits do not fit the sequence from the previous log record.

As VLFs become truncated and new ones become active, the logical log moves within the physical transaction log file and eventually should wrap around to the start again, as shown in Figure 3.

Figure 3 The circular nature of the transaction log

The check whether log truncation can take place under either of the following circumstances:

• When a checkpoint occurs in the SIMPLE recovery model or in other recovery models when a full backup has never been taken. (This implies that a database will remain in a pseudo-SIMPLE recovery model after being switched out of SIMPLE until a full database backup occurs.)
• When a log backup completes.

Remember that log truncation may not be possible because of the many reasons a log record must remain active. When log truncation cannot occur, the VLFs cannot be truncated and eventually the transaction log has to grow (or another transaction log file be added). Excessive transaction log growth can cause performance problems through a phenomenon known as VLF fragmentation. Removing VLF fragmentation can sometimes lead to a dramatic improvement in the performance of log-related activities.

For more information on this, see Kimberly Tripp's blog post "8 Steps to Better Transaction Log Throughput." Kimberly discusses best practices pertaining to transaction log capacity planning, management, and performance improvements—it's well worth reading!

There are two common issues that can prevent log truncation:

• A long-running active transaction. The entire transaction log since the first log record from the oldest active transaction can never be truncated until that transaction commits or aborts.
• Switching to the FULL recovery model, taking a full backup, and then never taking any log backups. The entire transaction log will remain active, waiting to be backed up by a log backup.

For a complete list of factors and instructions on how to determine what is preventing log truncation, see the SQL Server Books Online topic "Factors that Can Delay Log Truncation." I've also created a video demonstration that shows the effect of uncontrolled transaction log growth and how to remove VLF fragmentation—check out this video screencast (and previous screencasts on SQL topics) at technetmagazine.com/videos.

If the transaction log does grow to capacity and cannot grow any further, then error 9002 will be reported and you will need to take steps to provide more space, such as growing the log file, adding another log file, or removing any impediment to the log being truncated.

Under no circumstances should you delete the transaction log, try to rebuild it using undocumented commands, or simply truncate it using the NO_LOG or TRUNCATE_ONLY options of BACKUP LOG (which have been removed in SQL Server 2008). These options will either cause transactional inconsistency (and more than likely corruption) or remove the possibility of being able to properly recover the database.

For more information on how to troubleshoot a full transaction log, check out the Books Online topic "Troubleshooting a Full Transaction Log (Error 9002)."

Recovery Models

As you can see, the behavior of the transaction log depends in part on the recovery model the database is using. There are three recovery models available, and they all have an effect on transaction log behavior or how operations are logged or both.

The FULL recovery model means that every part of every operation is logged, which is called being fully logged. Once a full database backup has been taken in the FULL recovery model, the transaction log will not automatically truncate until a log backup is taken. If you do not want to make use of log backups and the ability to recover a database to a specific point in time, do not use the FULL recovery model. However, if you wish to use database mirroring, then you have no choice, as it only supports the FULL recovery model.

The BULK_LOGGED recovery model has the same transaction log truncation semantics as the FULL recovery model but allows some operations to be partially logged, which is called being minimally logged. Examples are an index rebuild and some bulk-load operations—in the FULL recovery model the entire operation is logged.

But in the BULK_LOGGED recovery model only the allocation changes are logged, which drastically reduces the number of log records produced and, in turn, reduces the potential for transaction log growth. For more information on minimally logged operations, see the Books Online section "Operations that Can Be Minimally Logged."

Finally, the SIMPLE recovery model actually has the same logging behavior as the BULK_LOGGED recovery, but the transaction log truncation semantics are quite different. Log backups are not possible in the SIMPLE recovery model, which means the log can be truncated (as long as nothing else is holding log records active) when a checkpoint occurs. There are pros and cons to each of these recovery models in terms of which backups are possible (or needed) and the ability to recover to various points in time (I will cover this in another article later this year).

Wrapping Up

This article has really been a more academic explanation of how a critical part of SQL Server works. I hope I've managed to clear up any misconceptions you might have had. If this is your first introduction to logging and recovery, these are the key points I'd like you to take away from this article:

• Do not create multiple log files, as it will not lead to a performance gain.
• Be aware of the recovery model your database is using and the effect it has on the transaction log—especially around whether it can automatically truncate or not when a checkpoint occurs.
• Be aware of the potential for transaction log growth, the factors that can lead to it, and how to get it back under control.
• Know where to look for help when troubleshooting a full transaction log.