Know The Transaction LOG -Part 1

After writing about the LOG shipping in my previous post, many questions raised that urged me to share the knowledge about the database logs in SQL Server 2005. As it is a huge concept to discuss, I planned to write a series of posts in Database Transaction Logs.

What is Database LOG?

A Database log always called as Transaction Log, is a critical component of a database. It is in a format of one or more disk files, created with 'Create Database' or 'Alter Database' command. The Transaction Log is required to bring back the last working state of your database when a failure occurs to your database.

Based on the 'RECOVERY' option of the database, Transaction Log records every database modification including the information about the pages which are being modified, the data values added or modified, start and end time of modification occurs to a series of LOG records. So that, whenever a 'undoing' or 'redoing' is required, SQL Server 2005 can do that to your database with the help of Transaction Log.

What is Undo or  Redo ?

Before  knowing these, it is necessary to know how a transaction  work with the modification or how SQL Server handles a transaction.

Whenever a data modification request is received by the SQL Server, regardless of explicit or implicit transaction, all the underlying pages are loaded into the buffer cache. A series of log records are created for this transaction including page numbers for which the modifications are to be carried out, before stage and after stage of the modification. All these logs records are internally linked together. Then the modifications take effect in the pages loaded in cache.

After modifications are done in cache, if a Rollback request for this particular transaction is received then all the undo operations for this transaction are carried out from the Transaction Log records. This Roll Backward operation is called 'Undoing'.

Suppose a Commit request for this transaction is received, then first log records are written to log disk files, prior to the data pages that are modified in cache are written to the data disk files. The buffer manager ensures this. Write LOG Records first and then DATA Records into Disk Files. This mechanism is called as 'Write-Ahead-Log'(WAL).

After writing log records into the log file or files and before writing the modified data records into the data files, say suppose, SQL Server stopped due to some resource problems, then, SQL Server uses these log records to recover all the transactions that are marked as committed  and not reflected in data,  during the restart of SQL Server. This recovery is called Restart Recovery. This restart recovery is always done for all the databases of an instance when that instance of SQL Server is restarted. This restart recovery, which is doing Roll Forward of all transactions to the data files is called 'Re-doing'.

LOG Sequence Number (LSN)

Every Log Record in Transaction log is associated with a LSN that is Log Sequence Number. As every transaction is associated with a series of log records, and every log records having LSN within it and all related log records are linked backward for Rollback operations. LSN of a log record is unique and it is a sequence number greater than the previous LSN associated with old transactions.

Every Data Page has a LSN number of the Log record which modified this page earlier, recorded in its header. Every LOG Record associated with a page for which the modification is being carried out, is also having the previous LSN stored in the Page’s header, and the new LSN number for this current modification. When a Redo operation is carried out by the SQL Server, it checks these two LSN numbers. If the LSN of the page is high then REDO skip for this page.

Active LOG Records and Inactive Log Records

The Records in LOG files are marked as two types, Active and Inactive. All the Log Records that are the part of live transactions which are not yet either committed or rolled back are called as Active Log Records. The Log Records related with earlier committed or rolled backed transactions are called as Inactive Records. The redoing or undoing operation carried out by the SQL Server, only deals with Inactive Log Records.

Virtual Log Files

SQL Server Database Engine, divide the physical Log disk file into Virtual log files internally. The number and size of the virtual log files cannot be configured explicitly by any DBA. It is based on the auto growth specification in Database for its log file. Database engine tries to have a minimum number of virtual log files for a physical log file. When a log file is created first, the number of Virtual log files may be 4 to 16. And it will go higher when the physical LOG file is enlarged. The performance will be slower, if number of virtual log files are high. SQL Server will automatically create VLFs during the expansion of Physical LOG File, so creating the LOG file with considerable size and file growth percentage should be adequately specified will reduce the number of Virtual Log Files in LOG. To view the Virtual log file use undocumented DBCC Command DBCC LOGINFO. The following is DBCC LOG Info of one of my active SQL Server Database in my development server. This Log file is having 16 Virtual Log files.

 

FieldID	FileSize	StartOffset	FSEQNO	STATUS	PARITY	CREATELSN
2	253952	8192	24	0	64	0
2	253952	262144	27	0	64	0
2	253952	516096	22	0	128	0
2	278528	770048	23	0	128	0
2	262144	1048576	25	0	64	24000000034800494
2	262144	1310720	26	0	64	25000000049500003
2	262144	1572864	28	0	64	27000000020000052
2	262144	1835008	29	0	64	28000000019100003
2	262144	2097152	30	0	64	29000000007300459
2	262144	2359296	31	0	64	29000000050700023
2	262144	2621440	32	0	64	31000000019800006
2	327680	2883584	33	0	64	32000000004600469
2	327680	3211264	34	0	64	33000000024100176
2	393216	3538944	35	0	64	34000000035400136
2	393216	3932160	36	0	64	35000000034400087
2	458752	4325376	37	0	64	36000000044200003

Checkpoints in Transaction Logs

Within a start and end of a complete transaction, we may use checkpoints or save points with the help of CHECKPOINT and SAVE TRANSACTION T-SQL statements, to store partially done transactions to write in disk files. These checkpoints may also internally triggered by SQL Server itself too. What a checkpoint does within the transaction?

Checkpoint is a SQL Server process that writing all modified data pages I buffer cache into disk files.It is also forces any pending transaction log records into log file. Performing Checkpoints reduces the recovery time of restart recovery, as it forced the transactions to written to log files and also writes the dirty pages into disk files. This process of Checkpoints minimize the Roll forward operations of Restore Recovery.

The Checkpoint Operation involves following steps.

• Writing out all dirty pages into Data disk files.
• Writing a list of active transactions to Transaction log.
• Storing check point log records to Transaction Log.

Scope of the Checkpoints is the Database level. So the Checkpoint operation run only for the current database only.

Checkpoint occurs in the following cases.

1. Whenever we issue CHECKPOINT T-SQL Command for the current database.
2. Whenever SQL Server shuts down without option WITH NOWAIT. This checkpoint works for all the databases in that instance. WITH NOWAIT option skips the checkpoint.
3. Whenever a Data Files are added to or removed from a Database using ALTER DATABASE Command.
4. When Bulk copy operation or Select Into operation performed in a database for which ‘Bulk-logged’ Recovery model is set.
5. When a database’s recovery model is changed from Bulk-logged or FULL to SIMPLE.
6. For a Simple Recovery Model Database, if the size of the Transaction Log exceeds 70%.
7. When number of log entries exceeds the estimated amount of work required by the SQL Server's 'Recovery Interval' configuration.

I think, I covered utmost every aspect of Restart Recovery often called as Crash Recovery.In my next part of this post, I will write about the another type of Recovery - Restore Recovery.

Hey Standby Server ! Your (Transaction Log) Ship(ment) has arrived…!-(LOGSHIPPING)

Having SQL Server as a Database Solution to your enterprise applications, you may have a little chance of losing your valuable data, due to some failures, as SQL Server 2005 is having a lot of features for High-Availability and Disaster Recoverability.

One of such feature is LOG Shipping. Even though LOG Shipping is available from predecessors of SQL Server 2005, it is more robust with SQL Server 2005. In SQL Server 2000, logshipping is only available in Enterprise edition. In SQL 2005, it is available from Standard edition.

All you require a stand by server with a same SQL Server Configuration, and a tamper proof network connection between the production server and the standby server.

How LOGSHIPPING works….?

Before delving into LOGShipping, let us think over, how we were maintaining a stand by server for our failures?

Normally, we backup our Database in to a Device (either tape or a disk file) and move the backup to another server, and restore the backup there. In these situations, the down time of the Database will be more, depending upon the standby or warm server’s setup and how frequently we are doing this manualy. If the standby server is located near by the primary production server then the job will be done frequently. Suppose if the Standby Server is located in another part of the world, then what will happen!?

LOGShipping does all the steps described above in an automatic way. SQL Agent Service plays a vital role here.

A SQL Server Agent Job first takes the Backup of the Transaction log of the Database from the Primary Server (for which Logshipping is enabled) in a file and store it in a (specified) network shared path in a specified time interval. Then secondary server’s SQL Server Agent’s Job gets the files from the network shared path in the order that they were taken backup from primary Server Database and copy it in its file system and restore the transaction log to the standby server’s database. For this purpose, the network service account which is maintaining the SQL Server Agent Service in the Secondary server must have the Read Permission for the Network Shared Path.

Setting up a Logshipping in a Domain scenario , where the Primary Server and Standby Server are the members of the same Domain, it is advisable to use the same domain user account for the both Servers’ SQL Server Agent Service, so that no conflict will happen and give read /write permission for the network shared folder to that account. But in a no-domain scenario, to set up a log shipping, you have to adopt a tricky way. Create an user account with a same name and same password in both the Prinmary and the Secondary Server and make it member of SQL Server Agent Group and give full control over the Network Shared Path where Primary Server’s Database's Transaction Log Backup were stored.

Steps Involved in Logshipping.
1. Backup the primary database in to a device.
2. Restore the backup in the secondary Server with NORECOVERY option.
(Note: First time transport log manually. If your LOG file is too big in size, then, change the RECOVERY model of the DATABASE to Simple and take the Backup. It will reduce the size of your log. After taking the backup change the recovery model to FULL, because, you can set up logshipping for the Database with FULL recovery model. Following steps are handled by the scheduled jobs.)
3. Backup the Transaction Log of the Primary Database.
4. Copy the Transaction Log to Secondary Database.
5. Apply the Transaction Log To Secondary Database.
Steps to be followed in Failover
1. Backup the last transactions after the last schedule of LOGShipping from the Primary (fault) sever, if possible.
2. Apply to the secondary database.
3. Synchronize the user related to the database in the secondary server.
4. Reconfigure the server as Production Server.
PROS & CONS of LOGShipping
Pros are :
o Easy to implement.
o Easy to maintain.
o It is more reliable.
o Multiple standby servers can be configured.
o Stand by Server Database can be used for reporting purposed to minimizing the workloads of the primary server.

Here are some cons too…
o No automatic failover (The Database Mirroring, an another feature of SQL Server 2005, supports automatic failovers.)
o Manual failover requires a technocrat during failover.
o At least a minimum data loss depending upon how frequently your logs are being shipped.

Some last considerations….

All the steps in LOGShipping involves SQL Server Jobs, all that jobs are executed in a frequency of time, and the failover is manual (main drawback of LOGShipping) so keep the frequency of logshipping in a low time interval to minimize the amount of data loss during failover.

Give some special consideration for synchronizing the user logins in both servers (primary and secondary) for failover situations, so that you can switch the standby server as a production server to all of your client applications by simply changing the name or IP or both, and keep trouble-shooting the faulty server. If you succeeded in troubleshooting, you can make this server as standby server and started log shipping from the production server. Synchronizing users in secondary server may cause SID conflicts. So do it carefully.

If you have enough infrastructure, then it is recommended to have another server as Monitoring Server which involves the LOGShipping operation, and track status and statistics of LOGShipping.

After setting up LOGShipping, you can monitor the operations, through three tools available in SQL Server 2005.
1. TRANSACTION LOG SHIPPING STATUS REPORT - an built-in Report available in SSMS Standard reports.
2. SQL Server LOG files
3. SQL Server Agent’s Job History.

For further studies:

1. Read Chapter 27:LOGShipping and Database Mirroring , from the book ‘MICROSOFT SQL SERVER 2005 – Administrator’s Companion’ by Edward Whalen, Marcilina Gracia and others - MS PRESS.
2. Refer Books Online.