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.

--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 [PRIMARY]
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