There are three main ways to access the SQL Error Log: via the SSMS GUI, via the system stored procedure sp_readerrorlog or the extended stored procedure xp_readerrorlog. The code below I have close to hand in a Snippet and I probably use it every day. It copies the contents of the SQL error log and optionally the SQL Agent error log to a temporary table. Once there you query it for specific information. This can be also be done by using parameters 3 and 4 of sp_readerrorlog but for me this is a little more flexible.
/*
/*
sp_readerrorlog can take 4 optional
parameters, they are:
1. The number of the error log file you want to read: 0 = current, 1 =
Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one
you want to search for
4. Search string 2: String two you want to search for to further refine
the results
*/
----------------
-- Current SQL Error log
---------------
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#ErrorLog'))
DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog (
logdate DATETIME
,processinfo VARCHAR(200)
,info VARCHAR(8000)
)
GO
INSERT INTO #ErrorLog
EXEC sp_readerrorlog 0
,1
GO
CREATE CLUSTERED INDEX ix_date ON #ErrorLog (logdate)
GO
-- Show the last 10,000 errors within
the last 24 hours
SELECT TOP 10000
logdate
,processinfo
,info
FROM #ErrorLog
WHERE logdate > DATEADD(HOUR, - 24, GETDATE())
ORDER BY 1 DESC
-----------------
-- Current SQL Agent log
-----------------
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#ErrorLogAgent'))
DROP TABLE #ErrorLogAgent
CREATE TABLE #ErrorLogAgent (
logdate DATETIME
,processinfo VARCHAR(200)
,info VARCHAR(8000)
)
GO
INSERT INTO #ErrorLogAgent
EXEC sp_readerrorlog 0
,2
GO
CREATE CLUSTERED INDEX ix_date ON #ErrorLogAgent (logdate)
GO
-- Show the last 10,000 errors within
the last 24 hours
SELECT TOP 10000
logdate
,processinfo
,info
FROM #ErrorLogAgent
WHERE logdate > DATEADD(HOUR, - 24, GETDATE())
ORDER BY 1 DESC
Comments
Post a Comment