How to Centralize the Monitoring of SQL Server Error Log

SQL Server error log is the first place we search to identify and troubleshoot SQL Server issues. Whenever you call Microsoft SQL Server for any SQL Server issues you would be asked to share your SQL Server’s Error log. In this article, we would learn how to centralize the monitoring of all SQL Server’s Error log

The location

Centralize the Monitoring Of SQL Server Error LogBefore learning how to automate the Error Log monitoring, it is necessary to know the location of the log file.  The first and easy approach is to identify the file location using the function ‘Serverproperty’. Run this script in a new query window and it would give the file location. In the same location, you could find all archives of Errorlog.

SELECT SERVERPROPERTY('ErrorLogFileName')

The second approach is to use the system stored procedure ‘SP_READERRORLOG’. This will read the active error log file and would display the content. The file location would be present in one of the first few rows. SQL Server Configuration manager and the extended stored procedure ‘XP_READERRRORLOG’ are other available options.

Centralize

You must execute this script from a Central Server. First step is to store all server names in a table. Create linked server for all servers. We will be triggering a distributed transaction and read the error log from each SQL Server in our list.  The collected data is stored in a table which can later be read and analyzed for errors.

DECLARE @Tserver TABLE 
  ( 
     cserver VARCHAR(200) 
  ) 

INSERT INTO @Tserver 
VALUES      ('SERVER1') 

DECLARE @Tcmd TABLE 
  ( 
     ccmd VARCHAR(2000) 
  ) 
DECLARE @Tlog TABLE 
  ( 
     cdate    DATETIME, 
     cinfo    VARCHAR(200), 
     cmessage VARCHAR(2000) 
  ) 

INSERT INTO @Tcmd 
SELECT 'exec (''EXEC sys.xp_readerrorlog'') at [' 
       + cserver + ']' 
FROM   @Tserver 

DECLARE c1 CURSOR FOR 
  SELECT * 
  FROM   @Tcmd 
DECLARE @cmd VARCHAR(2000) 

OPEN c1 

FETCH next FROM c1 INTO @cmd 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      PRINT @cmd 

      INSERT INTO @Tlog 
                  (cdate, 
                   cinfo, 
                   cmessage) 
      EXEC(@cmd) 

      FETCH next FROM c1 INTO @cmd 
  END 

CLOSE c1 

DEALLOCATE c1 

SELECT * 
FROM   @Tlog

SSMS vs TSQL

SQL Server Error LogIf your SQL Server is ONLINE for a long time or if you have not recycled SQL Server Error logs manually, the size of Error log will be huge. To open and read such huge files from SSMS would be troublesome. SSMS would take more time to open huge error log files. Mostly during an emergency, we would have less time to react and opening SSMS is not possible. Reading error logs through TSQL is the smartest way and it’s always faster than GUI. The best part is we can easily apply filters. Using SQL agent, you can schedule this script to run at regular intervals and collect data from all servers into our centralized log table.

Format it

Using the data from log table, you can easily create HTML alert emails and send it to DBA group for their quick action. On a corrupt SQL Server database, you would not be able to retrieve records using the xp_readerrorlog. Fix the integrity of the database and then try again.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.