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.

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

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.

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.

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.

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?

Regards,
<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.

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.

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]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
GO

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

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.

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.

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:

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?

Thanks,
[name and email removed as requested]


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.

BACKUP DATABASE [MyTestDB]
TO DISK = N'C:\MyTestDB.bak'
GO
BACKUP LOG [MyTestDB]
TO DISK = N'C:\MyTestDB.bak'
GO

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