March 28, 2011 at 9:09 am
SQLServer 2005 - please help with a script to copy only one month data from SQLError Logs to a user defined database table.
Thanks in advance...
March 28, 2011 at 9:22 am
The following scripts would place the entire contents of the current errorlog into a temp table.
CREATE TABLE ##Errors1 (ERRORLOG varchar(8000),continuationRow varchar(5))
INSERT into ##Errors1
(ERRORLOG,continuationRow)
Exec master..xp_readerrorlog
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 28, 2011 at 12:53 pm
As Dan mentioned master.sys.xp_readerrorlog can be used to read the contents of an error log into a table. The example Dan provided reads the most current log (log 0) but it can also be used to read older logs as well by supplying a positive integer for the first proc parameter. Log 1 is the next newest, 2 the next newest after that, and so on.
Depending on your SQL Server settings one month's worth of Error Logs may not be available. You can check to see how many are available using this call:
EXEC sys.xp_enumerrorlogs
You can then supply the value of "Archive #" from the result as the first parameter of master.sys.xp_readerrorlog to see the contents of that error log. For example, this will show you the contents of the second newest error log:
EXEC master.sys.xp_readerrorlog 1
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply