SQL SERVER – Basic architecture of Transaction Logs

Readers bump into this blog because it tries to simplify some of the most complex topics into a digestible format. I am a strong believer of this and try to bring some of these topics from time-to-time. Even I find some of these topics confusing, if I can understand them in simple words – will they reach the blog immediately as part of notes of my learning. This blog is no different in this regards because I have been wanting to write about TLogs for a while and the opportunity just came from. Ever since I wrote about the WAL protocol, this followup blog has been pending.

The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it.

Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Log records for data modifications record either the logical operation performed or the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

The steps to recover an operation depend on the type of the log record:

  • Logical operation logged:
    • To roll the logical operation forward, the operation is performed again.
    • To roll the logical operation back, the reverse logical operation is performed.
  • Before and after image logged:
    • To roll the operation forward, the afterimage is applied.
    • To roll the operation back, the before image is applied.

Many types of operations are recorded in the transaction log. These operations include:

  • The start and end of each transaction.
  • Every data modification (insert, update, or delete). This includes changes by the system stored procedures or data definition language (DDL) statements to any table, including system tables.
  • Every extent and page allocation or deallocation.
  • Creating or dropping a table or index.

Rollback operations are also logged. Each transaction reserves space in the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.

The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to make a full recovery of the database. No part of the active log can ever be truncated.

I hope this was a complete dope and dump of a lot of Transaction log basics in one blog and I will try to bring up others for later blogs. Please let me know if you learnt something new today.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Understanding the Basics of Write Ahead Logging (WAL) Protocol

The journey for IT started way back in my college days more than a decade back. It was a booming time and it was exciting to see everyone eyeing the Computer Science stream. Almost every other person in those days always wanted to take this new trend. Riding the waves was one part of excitement, while the other was to be unique in whatever we do. It is easy to get stereotyped with someone and we can never be different from the herd. I always believed in doing the basics right – this blog project that I started more than 8 years was to bring these basic learnings back. I am glad I have been doing this.

background logs SQL SERVER   Understanding the Basics of Write Ahead Logging (WAL) ProtocolWhen I was at my hometown, I planned to visit my college and spend some quality time with the students and live some moments back. I always dream of giving time back to the institution that made me who I am now. I think at this moment I request every reader to spend a couple of hours in a year with students from your college. I just wish I had the same exposure in my days back then.

During my last visit, I introduced myself as a database expert (based on what people say). One of the students asked me how durability is achieved in modern databases? Do I know anything about it and how it is done?

This question got me thinking and I immediately said, the first place to search will be my blog. I quickly checked to figure out I haven’t written about this subject. To complete the conversation in the real college style, I took the whiteboard and explained the simple WAL protocol. After a couple of hours of session on various topics, I got out exhausted – after getting out, I thought of writing back here.

The concept of Write Ahead Logging is very common to database systems. This process ensures that no modifications to a database page will be flushed to disk until the associated transaction log records with that modification are written to disk first. Why do we do this? The simple answer is to maintain the ACID properties for a transaction.

What if the database modifications were flushed first and a power failure occurred before the transaction log were written? Well, if the entire transaction was committed and all changes to all pages were also written to disk, it wouldn’t be a problem. But what if the page changes were flushed to disk due to a lazy write to free up buffers and the page changes were part of an active transaction? Transactional consistency would be comprised. The database page on disk will contain changes that are part of an uncommitted transaction (because the log records don’t exist to roll back the change).

This is the reason we write to the Log file first and hence this term is called “Write ahead logging”. Once the transaction gets persisted in the log first and when a power outage happens. The data files and data pages can be appropriately rolled forward (in case of committed transactions) or rolled back (in case of failed/rollback transactions) in the event of abrupt shutdown.

More information on this topic can be found in the SQL Book Online under the topic “Write-Ahead Transaction Log”.

Other sources on this topic include: INF: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Steps to Generate Windows Cluster Log?

Every now and then I sit next to DBA to learn some of their tricks. One of this recent learning is what I plan to share here. It is tough to learn everything all by ourselves, so learning is best done by sharing. There are many incidents with SQL cluster where we need to know what is happening with cluster and the best way to know history about cluster related event is to generate a cluster.log file. Event log has events related to Failover clustering but it is not very verbose. Typically, when SQL Resource doesn’t come online, I look at cluster log.

The command to generate cluster log depends on operating system the command with change. I am not an expert on cluster, but I generally use the commands to generate log and see if there is any glaring error message. In Windows Server 2003, the cluster log was written automatically, but Windows 2008 onwards the needs to be generated. I think it was a good decision by Microsoft to not to write on this all the time. Generation of cluster log is done using the cluster.exe command. Here are the steps

  • From one of the nodes of the cluster, open a Command Prompt with Administrator rights (Right click > Run As Administrator)
  • The easiest command to generate the log is to type cluster log /g and hit enter. Parameter g stands for generate.
  • A file with name cluster.log will be generated and stored in the %windir%\Cluster\Reports directory on each node of the cluster.

Here is the output on my lab machine having 4 nodes cluster. If any of the nodes is not working, it would continue with another node.

cluster.log 01 SQL SERVER   Steps to Generate Windows Cluster Log?

Here are the files generated in C:\Windows\Cluster\Reports

cluster.log 02 SQL SERVER   Steps to Generate Windows Cluster Log?

In later version, cluster.exe is not available (unless you install legacy components) and then you have to use PowerShell method of generating cluster log. The command which is required is Get-ClusterLog

PS C:\Windows\system32> Get-ClusterLog
The term ‘get-clusterlog’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:15
+ get-clusterlog <<<<
+ CategoryInfo          : ObjectNotFound: (get-clusterlog:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

cluster.log 03 SQL SERVER   Steps to Generate Windows Cluster Log?

If this is the error, then you need to import failover cluster module using “Import-Module FailoverClusters” command. Here is the screenshot. Since we have a 4 node cluster, we are seeing cluster.log four times.

cluster.log 04 SQL SERVER   Steps to Generate Windows Cluster Log?

Here is the syntax of the command from Get-Help Get-ClusterLog command.

Get-ClusterLog [-InputObject <psobject>] [[-Node] <StringCollection>] [-Cluster <string>] [-Destination <string>] [-TimeSpan <UInt32>] [<CommonParameters>]

You can customize the file based on your need but to be very honest, I have never used the additional parameters.

Have you come across a situation where cluster log has helped you? I am sure in future more of these will get added and your experience in using the same would be of great help to others too. So do let us know if you did something similar in your servers too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Log Shipping – Sharing Tab Missing in Folder Properties

Building a solution for some sort of disaster site is critical for every organization that thinks about business continuity. One of the proven for ages is a solution around Log Shipping. Though one of the oldest, it is one of the most sought after solution inside SQL Server. With solutions like SQL Server AlwaysOn Availability Groups and others taking prominence, there is still a group of people who still love using Log Shipping techniques. I personally don’t have any negative opinion about it, but I am curious to understand why you resorted to Log Shipping when you have options like Database Mirroring, AlwaysOn techniques. I am sure it will be a learning for me too in the specific scenarios you bring to the table.

It was looking just like just another day of routine emails when one of my blog reader contacted me and informed that he is not able to configure log-shipping. While getting more details I asked him to provide exact error message and steps he was using. There is no way I can debug this without proper steps. Here is his problem as described by him via email.

I think my problem is not related to SQL Server at this point. When I right clicked on LogShippingPrimary folder and opened Properties dialog, there were only 4 tabs, General, Security, Previous Versions, Customize, NO Sharing tab. Due to this, I am not able to create Share which I can use for log-shipping purpose.

LS ShareTab 01 SQL SERVER   Log Shipping   Sharing Tab Missing in Folder Properties

Also “Share With” option is missing on the right-click context menu for folders.

Have you seen this before?

Thanks in advance.
My reply to him:

Hello Blog Visitor,
Here are the thing which I wish to verify. Thanks again for sending such queries to me. I am glad I could be of help and hope the following steps would lead you in the right direction.

1. Open Network Connections control panel, open the properties page for your Connection. Are “Client For Microsoft Networks” and “File and Printer Sharing for Microsoft Networks” checked?

LS ShareTab 02 SQL SERVER   Log Shipping   Sharing Tab Missing in Folder Properties

2. In the Services console (start, run, services.msc), make sure that “Server” service is running?

 LS ShareTab 03 SQL SERVER   Log Shipping   Sharing Tab Missing in Folder Properties

With the above two recommendation, I got a reply from him and he informed that “Server” service was in disabled state due to hardening of the server done by Wintel team. Here is the screenshot which he shared after the issue was resolved.

LS ShareTab 04 SQL SERVER   Log Shipping   Sharing Tab Missing in Folder Properties

Have you ever seen such scenario? Any other things which have caused the same issue in your environment? I am sure we can learn from your experiences too. Let me know so that I can add in the blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Whenever someone reports some weird error on my blog comments or sends email to know about it, I always ask to share SQL Server ERRORLOG file. There have been many occasions where I need to guide them to find location of ERRORLOG file generated by SQL Server. Most DBA’s are intelligent and know some of these, but this is my try to share my learnings.

I decided to write this blog so that I can reuse it rather than sending steps every time. At this point I must point out that even if the name says ERRORLOG, it contains not only the errors but information message also. Here are various ways to find the location of SQL Server Errorlogs:

A) If SQL Server is running and we are able to connect to SQL Server then we can do various things. So we can connect to SQL Server and run xp_readerrorlog.

EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'

errorlog 01 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

If you can’t remember above command just run xp_readerrorlog and find the line which says “Logging SQL Server messages”.

B) If we are not able to connect to SQL Server then we should SQL Server Configuration Manageuse. We need to find startup parameter starting with -e. Below is the place in SQL Server Configuration Manager (SQL 2012 onwards) where we can see them.

errorlog 02 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

C) If you don’t want to use both ways then here is the little unknown secret. The ERRORLOG is one of startup parameter and its values are stored in registry key and here is the key in my server. SQLArg1 shows parameter starting with -e parameters which point to Errorlog file.

errorlog 03 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

Here is the key which I highlighted in image: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters\

Note that “MSSQL12.SQL2014” would vary based on SQL Server Version and instance name which is installed. Here is the quick table with version reference

SQL Server Version Key Name
SQL Server 2008 MSSQL10
SQL Server 2008 R2 MSSQL10_50
SQL Server 2012 MSSQL11
SQL Server 2014 MSSQL12

In SQL Server 2005, we would see a key name in the format of MSSQL.n (like MSSQL.1) the number n would vary based on instance ID.

Here is a key where we can get mapping of Instance ID and directory.

errorlog 04 SQL SERVER   Where is ERRORLOG? Various Ways to Find its Location

In the above image, you can see that this computer has a default instance (Instance Name MSSQLSERVER) of SQL Server 2012 and named instance (Instance Name SQL2014) of SQL Server 2014.

In case you are contacting me for any error, get the Errorlog using this blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Increase Number of Errorlog Files

A long back I had written a blog on – SQL SERVER – Recycle Error Log – Create New Log file without Server Restart. Recently one of my blog reader emailed me with this question:

Hi Pinal,
In our SQL Server, we are logging successful logins to ERRORLOG and due to this the file size keeps on increasing. I have read your blog about recycling error log. I have put a job in SQL Agent to recycle the logs every midnight. No problem so far. In near future, we will have audit in our company and as per their requirement, they want to have Errorlog worth 30 days (to check login success/failures). Since I have put midnight job and we have six archive errorlogs (ERRORLOG.1 to ERRORLOG.6) and that would cover last 6 days only.

To solve the problem, I can put a copy step to move the file somewhere else before midnight recycle. But again I also need to put logic to delete 30 days old file. Is there any way to achieve this in SQL Server without much efforts?

<Name Hidden>

I thought I had blog covering this answer but surprisingly, I couldn’t find anything on my blog. So, here are various ways to achieve the same.

Using SSMS

Once we connect to SQL instance via SSMS, we can go to “Management” Node and right click on “SQL Server Logs” and choose “Configure” as shown below.

ErrLog 01 SQL SERVER   How to Increase Number of Errorlog Files

Once we click on configure, the checkbox shown below would be unchecked by default and value would be shown as 6. That’s the reason we have files till ERRORLOG.6.

We can check the box and put the desired value in the box. Based on daily recycle of errorlog which my blog reader had and 30 days requirement, the value can be set to 30 to keep his auditors happy.

ErrLog 02 SQL SERVER   How to Increase Number of Errorlog Files

Using T-SQL

If you are not a fan of UI then below is the T-SQL which can be used to achieve the same change.

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

Above is essentially changing the registry key called NumErrorLogs for this instance. Notice that SQL is invoking xp_instance_regwrite which doesn’t have instance related details as it detects the exact key internally. For my named instance “SQL2014” for SQL Server the exact key would be

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer as shown below

ErrLog 03 SQL SERVER   How to Increase Number of Errorlog Files

Hope this would help you in real world to keep more errorlogs than default if that is a requirement from your DBA/Network or auditor teams.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSMS: Transaction Log Shipping Status Report

History has its own way to define now civilizations thrived. Most of the cities flourished in the river side and transporting lumber was one of the key activity. Most of the cities like Seattle and many others have this boom and bust life. The idea here was to cut the timber upstream and use the natural flow of rivers to transport to factories downstream using the river. These are classic and wonderful examples of how we typically work with Log-Shipping in SQL Server too. This blog is about Log Shipping Status report.

Ensuring the availability of databases, meeting SLA and performance tuning are some of the top priorities for today’s database administrators (DBAs). One of the important work of DBA is to monitor the database servers and make sure the application is working fine. The monitoring might involve automatic alerts, running scripts or looking at some dashboard. Even for high availability solutions, we need some kind of monitoring mechanism. One of the traditional high availability solution is Log Shipping.

As the name suggests, Log-shipping is based on transaction log backups getting shipped from one server to one or more servers on the other side. For understanding this you need to know basics of transaction log backups. First, log backups can be taken from the database which is in full or bulk logged recovery model. In the simple recovery model, transaction log backups are not allowed because every checkpoint flushes the transaction log file. In other two recovery models log backup would do flush. Another basics of log shipping is that all log backups form a chain. T1, T2 and T3 must be restored in sequence. Missing any one the file would cause an error message during restore. In log shipping, backup, copy and restore is done automatically. The SQL Agent service does that for us. Since we can ship to multiple servers, backup location is shared so that other servers can get a copy of that file to perform the restore. Source server in technical terms is called as the primary server. Rest all servers which are at receiving end are called as a secondary server. You would also hear monitor server, which is responsible to check the health of copy, backup and restore job. If the jobs are not running properly, then secondary would be behind primary server and would defeat the purpose of high availability. Based in the threshold defined, monitor server can raise alerts so that corrective action can be taken.

This is the last report in the list under server node. Based on the name of the report, you might have already guessed that it can be used to “see” the status of log shipping status.

reportlaunch1 SQL SERVER   SSMS: Transaction Log Shipping Status Report

The important note about this report is that the data shown in the column would be dependent on the server where we launch the report. Here is the report, when launched from Primary Server.

reportlaunch2 SQL SERVER   SSMS: Transaction Log Shipping Status Report

If we notice, information about backup section is populated. This is because the report doesn’t make a remote connection to check secondary server status. If the report is launched from a Secondary Server the output would be as below:

reportlaunch3 SQL SERVER   SSMS: Transaction Log Shipping Status Report

The information about copy and restore related information is populated automatically because those are available on secondary server.

If we configure monitor server in log-shipping (which I have not done) and launch report there, we can see information about all three steps (i.e. backup, copy and restore)

The good part about the report is that it shows the alarming pair in red color. To demonstrate, I have configured log shipping for two databases, and for one, I have disabled the backup, copy and restore jobs so that alerts are raised and we can see the impact on report.

You may wonder how this information is fetched. This has the simplest possible query behind the scene.

EXEC sp_help_log_shipping_monitor

As per Books online – “Returns a result set containing status and other information for registered primary and secondary databases on a primary, secondary, or monitor server.”

If you see anything in red color, you need to start investigation further to find the cause of delay. What is the most common cause you have observed, which causes delay in log shipping? Networking, Disk slowness or something else? Please comment and let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX : ERROR : 4214 BACKUP LOG cannot be performed because there is no current database backup

I recently got following email from one of the reader.

Hi Pinal,

Even thought my database is in full recovery mode when I try to take log backup I am getting following error.

BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)

How to fix it?

[name and email removed as requested]

backuperror SQL SERVER   FIX : ERROR : 4214 BACKUP LOG cannot be performed because there is no current database backup

Solution / Fix:

This error can happen when you have never taken full backup of your database and you try to attempt to take backup of the log only. Take full backup once and attempt to take log back up. If the name of your database is MyTestDB follow procedure as following.

TO DISK = N'C:\MyTestDB.bak'
TO DISK = N'C:\MyTestDB.bak'

Reference: Pinal Dave (http://blog.SQLAuthority.com)