April 25, 2012 at 1:16 am
Hi
I wants to read error logs on this month 16th and 17th dates.
Due to huge size of the error logs, How can i read my error logs as simple as possible.
it is possible to read error logs on particluar date range with EXEC sys.xp_readerrorlog 0,1
How can pass date range in above sp
April 25, 2012 at 1:18 am
load the log into a temp table then do a query against the date column
create table @errorlog (date, source, message)
insert into @errorlog exec sp_readerrorlog
select * from @errorlog where date >= '' and date <= ''
there is no way to pass in a paramater to the sp_readerrorlog that I know of, other than using the log viewer but that will read the log then filter it down which is the same as the above.
April 25, 2012 at 1:38 am
Thanks...i will try
May 10, 2012 at 1:01 pm
I believe the 5th and 6th parameters to xp_ReadErrorLog are start and stop datetimes respectively, so you should be able to do something like:
EXEC xp_ReadErrorLog 0, 1, Null, Null, '2012-05-16 00:00:00', '2012-05-17 23:59:59'
Here is my understanding of the parameters:
Parameter 1 = log version (0 = current)
Parameter 2 = log type (1 = error, 2 = agent)
Parameter 3 = Search string
Parameter 4 = Search string
Parameter 5 and 6 I believe are start/end datetime restrictions
Hope this helps.
May 11, 2012 at 7:21 am
Here is a quick, easy way that I do it:
/* =================================================================================================================== */
/* Load SQL Error Logs to a temp table for research
Whole script at:
http://www.sqlservercentral.com/scripts/Error+Logging/70611/
*/
CREATE TABLE #SQLErrorLog
(
LogDateDATETIME,
ProcessInfoVARCHAR(20),
TextVARCHAR(500)
)
GO
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 0
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 1
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 2
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 3
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 4
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 5
go
SELECT * FROM #SQLErrorLog
WHERE Text LIKE '%fail%' OR Text LIKE '%error%'
ORDER BY LogDate DESC
SELECT * FROM #SQLErrorLog
WHERE LogDate >= CONVERT(datetime,'05/04/2012') AND LogDate < CONVERT(datetime,'05/16/2012')
ORDER BY LogDate DESC
DROP TABLE #SQLErrorLog
July 30, 2012 at 1:50 pm
This worked like a charm, just make sure that you define the text column as large as needed.
Thanks for the post and thanks to the original post's author too.
-- Create a temporary table
CREATE TABLE #SQLErrorLog
(
LogDate DATETIME,
ProcessInfo VARCHAR(200),
Text VARCHAR(1900)
)
GO
vikingDBA (5/11/2012)
Here is a quick, easy way that I do it:/* =================================================================================================================== */
/* Load SQL Error Logs to a temp table for research
Whole script at:
http://www.sqlservercentral.com/scripts/Error+Logging/70611/
*/
CREATE TABLE #SQLErrorLog
(
LogDateDATETIME,
ProcessInfoVARCHAR(20),
TextVARCHAR(500)
)
GO
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 0
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 1
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 2
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 3
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 4
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 5
go
SELECT * FROM #SQLErrorLog
WHERE Text LIKE '%fail%' OR Text LIKE '%error%'
ORDER BY LogDate DESC
SELECT * FROM #SQLErrorLog
WHERE LogDate >= CONVERT(datetime,'05/04/2012') AND LogDate < CONVERT(datetime,'05/16/2012')
ORDER BY LogDate DESC
DROP TABLE #SQLErrorLog
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply