How to Archive your SQL Errorlogs into a Table
How to Archive your SQL Errorlogs into a Table
Chris Skorlinski
Microsoft SQL Server Escalation Services
Have you noticed your SQL Server errorlogs taking up a lot of disk space? Ever wonder what was logged a few weeks ago, but the logs have already rolled over? Why not create a SQL Agent job and archive your SQL Agent and SQL errorlogs into a table then recycle the error logs using the SQL statements below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
--Create Tables statements only need to be executed once IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MS_SQLerrorlog]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[MS_SQLerrorlog]( [LogDate] [datetime] NULL, [ProcessInfo] [varchar](10) NULL, [Text] [varchar](max) NULL ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MS_SQLAgentlog]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[MS_SQLAgentlog]( [LogDate] [datetime] NULL, [ProcessInfo] [varchar](10) NULL, [Text] [varchar](max) NULL ) ON [P<span class="kwrd">RIMARY</span>] END GO --Save current SQL Server Errorlog INSERT INTO MS_SQLerrorlog EXEC ('sp_readerrorlog') Go Exec msdb.dbo.sp_cycle_errorlog Go --Save current SQL Agent log INSERT INTO MS_SQLAgentlog EXEC ('sp_readerrorlog -1, 2') Go Exec msdb.dbo.sp_cycle_agent_errorlog Go |
