This article provides how-to information and code for two different monitoring systems you can set up centrally to manage reporting of SQL Server 2000 maintenance and disk utilization. The first is a scheme I have used in my role as a DBA, which has helped simplify and speed up one of the more mundane tasks we as DBAs are likely to face: checking overnight backups and DBCC checks. The second is a simple system which centrally collects disk drive utilization data from target SQL Servers. This data can be checked regularly to ensure none of your servers are about to run out of disk space, and it is also retained so you can analyze it for trends and use the results for the prediction of your future disk requirements.
Central Maintenance Monitoring The objective of this system is to provide an automated, central view of your server’s maintenance (backup and DBCC) results for you to review. The information is collected on a central server using a scheduled job and you can interrogate it using a stored procedure. I embed this in a web page which calls the SP on-demand whenever the web page is browsed, but this is a personal preference.
How Does It Work?
The system relies on two very useful system stored procedures, xp_readerrorlog, which reads the SQL Server error log from a server; and sp_cycle_errorlog, which recycles the SQL Server error log on a server. By using the latter to keep a server’s error log containing only recent, relevant information; and the former to capture the contents of a server’s error log to a central location, logged information about the backups and DBCC checks that have run on the server can be kept and stored centrally for reporting purposes.
Throughout this article I will assume you have a central server with an admin-type DBA database, in which you can put the tables needed for the purposes of the two systems described in this article. In my case I call this database centraldb; you can call yours whatever you want (or use an existing one you may already have).
First, create the tables in centraldb that will be used to hold the data.
CREATE TABLE [dbo].[ServerErrorLog] (
[ErrorLogText] [varchar] (256) NULL ,
[ContinuationRow] [bit] NULL ,
[Servername] [sysname] NULL ,
[InsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ServerHistory] (
[SHID] [int] IDENTITY (1, 1) NOT NULL ,
[ErrorLogText] [varchar] (256) NULL ,
[Servername] [sysname] NULL ,
[EventDate] [datetime] NOT NULL ,
[InsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [SFSH_01] ON [dbo].[ServerFreeSpaceHistory]([CheckDate]) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [Event_Date_cnu] ON [dbo].[ServerHistory]([EventDate]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ServerErrorLog] ADD
CONSTRAINT [DF_Serv__Inser__182C9B23] DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[ServerHistory] ADD
CONSTRAINT [DF__Serv__Inser__24927208] DEFAULT (getdate()) FOR [InsertDate],
PRIMARY KEY NONCLUSTERED
(
[SHID]
) ON [PRIMARY]
GO
These are a worktable, ServerErrorLog; and a history table, ServerHistory. The target servers’ error logs are collected by a stored procedure, prGetErrorLog.
CREATE procedure prGetErrorLog
@servername sysname
as
delete from ServerErrorLog
declare @sql varchar(255)
set @sql = @servername + ‘.master.dbo.xp_readerrorlog ‘
insert into ServerErrorLog
(ErrorLogText, ContinuationRow)
exec @sql
update ServerErrorLog
set Servername = @servername
where Servername IS NULL
insert into ServerHistory
(ErrorLogText, Servername, EventDate)
select
CASE WHEN ErrorLogText LIKE ‘%Database backed up%’ THEN SUBSTRING(ErrorLogText,34,20) + SUBSTRING(ErrorLogText,64,CHARINDEX(‘,’,ErrorLogText,64)-64)
WHEN ErrorLogText LIKE ‘%DBCC CHECKDB%’ THEN RTRIM(SUBSTRING(ErrorLogText,34,221))
WHEN ErrorLogText like ‘%BACKUP FAILED%’ THEN SUBSTRING(ErrorLogText,34,256)
END,
ServerName,
SUBSTRING(ErrorLogText,1,22)
from ServerErrorLog
where ErrorLogText like ‘%Database backed up%’
or ErrorLogText like ‘%DBCC CHECKDB%’
or ErrorLogText like ‘%BACKUP FAILED%’
GO
When called, this procedure takes a parameter, servername. Firstly it clears out the worktable ServerErrorLog. Then, using dynamic sql, it inserts into this worktable the results of running xp_readerrorlog on the target server. Then, it collects relevant rows from this worktable into the ServerHistory table for permanent storage (and later querying). It filters out only error log rows which contain data about backups and DBCC checks, as these are the only ones we are interested in.