27 Jul

SQL Tip-5 | Lightning Fast way to find errors in SQL Server Error Logs

In my first SQL Tip Don’t forget to recycle your SQLServer ErrorLog I listed some common problems you may face if you are not recycling your errorlogs timely. However, I do understand the criticality and importance of Error Log that’s the reason I also suggested you to increase the retention of your Error Logs in another tip Do you know you can increase number of archived Errorlogs.

But even after recycling your error logs frequently you may end up with such a large Error Log that it becomes tiresome finding errors or other text. Specially if you have many DBs and Frequency of Transaction Log backup is very high. In this tip I’ll talk about a method which can certainly help you find any error or text out of your errorlog without forcing you to read the complete errorlog.

let’s find the needle in a Haystack Winking smile

SQL Server provides a Stored Procedure to read Error Logs; sp_readerrorlog.

By executing just the stored procedure without any parameter, it will give you the current SQL Server ErrorLog as an output.

Let’s say you want to read the previous error log, all you need to do is provide the first parameter i.e., Index number of Error Log. So if you want to read the Previous errorlog you can provide 1 as an input parameter on first position. <we are passing 1 for the previous errorlog because we are providing index number which is supplied by SQL Engine in reverse order starting from 0>

sp_readerrorlog 1

The Second positional input parameter is about log type:
a. 1 = SQL Server ErrorLog (Default Value)
b. 2 = SQL Agent ErrorLog

so if you ever want to read SQL Agent ErrorLog all you need to do is provide the second input parameter as 2.

Now the third and fourth parameters are search strings. If you want to search a text let’s say you want to know what all errors have been encountered since last rollover of Error Log;

sp_readerrorlog 1,1,’error’

This query will find your errorlog for the word “error” and return all rows having this word. You can further filter the rows by giving fourth parameter. If the fourth parameter is also specified this stored procedure will search the rows having both the values and return the output.

sp_readerrorlog 3,1, ‘error’, ‘18745’

You can specify any type of value in third\fourth parameter it could be alphanumeric or just numeric also.

Happy Learning 🙂

Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox

25 Jul

SQL Tip-4 | Do you know you can increase number of archived Errorlogs

By default, SQL Server keeps only seven error logs; Errorlog and Errorlog.1 to Errorlog.6. The name of the current, most recent log is Errorlog with no extension. In my SQL Tip-1 I advised why you should recycle your errorlog on frequent basis and how. But as we know when you recycle errorlogs SQL Server makes an archive of current errorlog and push it down the stack one after another and keep only 7 total error logs (1 current and 6 archived) – in this process we’ll very soon reach a limit of 6 Archive errorlogs retention and will start loosing error logs.

In some cases (for instance Tier1 DB Servers), you may want to keep more than six archived error logs so that you can read the past entries. For all those servers you can very well increase this retention policy. You can configure the retention limit between 6 to 99. Here is a step by step How to blog for the same.

Since the retention limit is being maintained in a registry key, In Order to change the Archive error log Retention limit on your SQL Server you have two options:

  1. Using command
  2. Using SSMS GUI Window

Changing Retention limit in Registry using command: There are n number of ways to change a key value in Registry but since we are SQL Server Admins\Dev’s let’s utilize the inbuilt stored procedure to change registry entries from SQL itself:

USE [master]
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 12

In the above code, we are changing the retention value to 12. That means from now onwards SQL Server will maintain 13 total errorlogs (1 current and 12 archived)

Changing Retention limit in Registry using SSMS GUI Window: You can also change this retention value using GUI Window very easily.  Let’s take a look at the below screenshot of one of the servers where the Retention was set to 6 (By Default Value)



Now Right click on SQL Server Logs & choose configure



This is the default screen you’ll get if you’ve never changed this value.



Before you can change the limit you need to select the checkbox & once it is selected you can now provide any number between the range of 6 to 99.



Screenshot of one of the servers where I changed the limit and cycled error logs to see whether it is capable of holding higher number of archived logs or not.


I hope this helped you, and now you can easily change the retention if required.
Happy Learning 🙂

Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox

17 Jul

SQL Tip-1 | Don’t forget to recycle your SQLServer ErrorLog

Many a times I’ve seen that DBAs forget to recycle the ErrorLog of their critical SQLServers. Now I also understand that SQLServer Error Log is very important & can help you troubleshoot many issues so in theory there is nothing wrong with a large Error Log but at times this could be very frustrating.

Let’s assume you are trying to resolve a severity case and already under pressure – giving updates to n number of people on a Bridge Call, handling multiple pings from Higher management\Clients and at the same time making sure you resolve the issue within your SLA.

Now between all of this, you need to get some info from your SQL Server error log, you issue the command sp_ReadErrorLog and wait for your log to get loaded and then it keeps on loading which takes 3-4 minutes to load. I am sure you won’t be able to afford this wait, obviously you can also open the file in Notepad but that will also take some time depending on the size of file & you must be on the same server and also need to locate the folder.

Since SQL Server will recycle Error Log only at the time of starting the service until and unless you’ve recycled it explicitly. The best option would be to recycle the errorlog manually on a certain interval (based on the usage & growth rate) .

You can use any one out of the two below mentioned commands in order to recycle Error Log.



DBCC ErrorLog

This command will just recycle the errorlog of your SQL Server without any restart, which means if you are recycling your errorlog every 7 days – you’ll be having a fresh errorlog every 7th day without any downtime and your last errorlog will be archived & renamed to ErrorLog.1

Note: Make sure you also increase the log retention limit before recycling your errorlogs, To know more read: Do you know you can increase number of archived Errorlogs

Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox