Archive
Reading the xp_readerrorlog Using a WHILE Loop
Last week, I needed to search through all of the SQL Server error logs on an instance. I have used xp_readerrorlog before, using the information found here. We retain 99 log files, which is the maximum number of files that can be kept. I needed to search through all of these files, so I put together the following script. This is another example of using a WHILE loop in gathering information from SQL Server.
USE master GO -- Create temp table to hold the output of the error logs CREATE TABLE #errorlog (errdate DATETIME ,errproc SYSNAME ,errtext VARCHAR(4000)) -- Declare and initialize the @loopcount variables DECLARE @loopcount INT SET @loopcount = 1 -- Loop through the error logs -- We keep the 99 logs, which the maximum -- Change the loop limit accordingly WHILE @loopcount <= 99 BEGIN -- Insert the results of xp_readerrorlog into the temp table INSERT INTO #errorlog -- You can find the parameters for xp_readerrorlog at the following link -- http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/ -- The parms I use below are as follows: -- @loopcount - file number, with 0 being the current file -- 1 - indicates the SQL error log - a 2 would query the SQL Agent log -- 'DELETE' - a search argument EXEC xp_readerrorlog @loopcount, 1, 'DELETE' -- Increment your loop counter SET @loopcount = @loopcount + 1 END -- Select the results of your loop -- Alternately, you can dump the entire log file and then use WHERE predicates to -- select what you are interested in SELECT * FROM #errorlog -- Clean up your temp table DROP TABLE #errorlog
Checking SQL Server Agent Status Using xp_cmdshell
I work in a clustered environment running more than 200 instances of SQL Server 2008. Several years ago, we began having problems with the SQL Server Agent service failing after an instance failed over. We had already developed a process to check the health of the SQL Server service, so I set out to add an Agent check to it.
Doing some web searching, I came across a number of links that suggested running the following query to determine if SQL Server Agent was running:
SELECT COUNT(*) FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher'
In the tests I ran, this worked just fine. I put it into a procedure, rolled it out to our test environment and let it run for a week, where we had no issues. It caught a number of times that the Agent was down and provided no false positives. At this point I rolled the proc out to production.
Several weeks later, we started receiving messages that the SQL Agent was down on an instance. Management Studio and Windows Failover Cluster Management showed the process was up and running. We were able to access Agent jobs and metadata, and jobs were running successfully. I queried the dbo.sysprocesses DMO on the instance and did not get a hit for ‘SQL Agent – Generic Refresher’.
This led me to open a ticket with Microsoft to find out if they could tell me why there was no row in the sys.processes DMO when the process was running. If they couldn’t do that, I was hoping they could tell me a better way to check status programmatically. I was told that querying the sys.processes table was not recommended and that they could not provide me with any other suggestions.
At that point, other things took precedence until two weeks ago, when we started receiving messages the Agent was down on an instance. Taking a look at the sys.processes table, there was no row for ‘SQLAgent – Generic Refresher’.
The instance that was failing was excluded from the job and I worked on another method of checking the Agent service. Since the last time I looked at this issue, I gained experience using xp_cmdshell in T-SQL code. This led me to use the following command:
EXEC xp_cmdshell 'tasklist' GO
This code returns a list of all of the processes running on the machine. By inserting the results of this code into a temp table, you can run a select to check for the existence of the SQL Agent process. That code looks like this:
USE master GO CREATE TABLE #processes (processlist VARCHAR(4000)) INSERT INTO #processes EXEC xp_cmdshell 'tasklist' SELECT * FROM #processes WHERE processlist LIKE '%sqlagent%' DROP TABLE #processes
Here are the query results:
| processlist |
| SQLAGENT.EXE 9896 Services 0 38,316 K |
I’ve put that block of code into a procedure that loops through all of the instances in our environment checking the status of SQL Server Agent. If the check fails, an e-mail is sent out to the DBA team. I will cover this procedure in my next post.
