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
Before 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.
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.
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
If 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.
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.
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