April 29, 2009 at 6:21 am
Hi All,
Can some one help me in building a Simple SQL scripts which can be used to moniter the Error Log (Sql server)
As my monitering Tool does not read Stored Procredure.(its the drawback).
I we have the Script, i can be used to ping the Server and any issues found then alert ..
Many Thanks,
Gagan,
April 29, 2009 at 6:53 am
If you want to filter the result set SET @ Filter = 'Whatever you want to filter by'
DECLARE @filter VARCHAR(100), @LogDate DATETIME
SET @LogDate = getdate()
SET NOCOUNT ON
set transaction isolation level read uncommitted
set lock_timeout 10000
BEGIN
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#TempLog1]'))
DROP TABLE #TempLog1
CREATE TABLE #TempLog1
(LogDate datetime, ProcessInfo varchar(50),ErrorLog VARCHAR (1000))
INSERT INTO #TempLog1
EXEC('xp_readerrorlog')
IF @filter IS NULL AND @LogDate IS NULL
BEGIN
SELECT LogDate, ErrorLog
FROM #TempLog1
ORDER BY LogDate DESC
END
ELSE IF @filter IS NOT NULL AND @LogDate IS NULL
BEGIN
SELECT LogDate, ErrorLog
FROM #TempLog1
WHERE ErrorLog Like '%'+@filter+'%'
ORDER BY LogDate DESC
END
ELSE IF @filter IS NULL AND @LogDate IS NOT NULL
BEGIN
SELECT LogDate, ErrorLog
FROM #TempLog1
WHERE LogDate BETWEEN CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME), 101)
AND CONVERT(VARCHAR(50), CAST(@LogDate + 1 AS DATETIME), 101)
ORDER BY LogDate DESC
END
ELSE --Both parameters have values
BEGIN
SELECT LogDate, ErrorLog
FROM #TempLog1
WHERE LogDate BETWEEN CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME), 101)
AND CONVERT(VARCHAR(50), CAST(@LogDate + 1 AS DATETIME), 101)
AND ErrorLog Like '%'+@filter+'%'
ORDER BY LogDate DESC
END
END
DROP TABLE #TempLog1
April 29, 2009 at 10:26 am
Please don't post the same question multiple times. See http://www.sqlservercentral.com/Forums/Topic706719-146-1.aspx
Francis
April 29, 2009 at 10:34 am
Closing this thread.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply