Set Up Scheduled Tasks
Now, you need to set up the scheduled tasks required for the collection of the error log data into your centraldb. First, define a list of target servers. In my case I chose all live servers, as it is only on these that I consider backups and DBCC checks to be of vital importance. Clearly, if you have differing requirements, you could extend the system to any servers you wish to monitor, including test and development ones.
On each target server you need to set up a local scheduled task in SQL Server Agent to run the stored procedure sp_cycle_error_log. You should set these up on each server to run at the same time; for example, 0730, assuming all your overnight maintenance will be complete by then. The error logs will then be collected centrally before this time so that it captures the error log information before the log is recycled. So in this example we would schedule, on our central server in centraldb, at 0700 each day, a job with a single step, which would contain one entry for each target server with the execution of prGetErrorLog for that server, so your job step will look like this:
EXEC prGetErrorLog ‘SERVER1’
EXEC prGetErrorLog ‘SERVER2’
And so on, one for each of the target servers in your list.
Set Up Security
The central job step is going to be executing distributed transactions from the central server to all the others so you need to add, on your central server, each target server as a linked server. You can use whichever security context for this that you prefer, but my recommendations for simplicity are these:
1. Run SQL Server and SQL Server Agent on ALL servers using the same single domain account
2. Set up the linked server from the central server to the target servers as:
-
General tab, Server Type – chose “SQL Server”
-
On the security tab, chose “use existing security context“
-
Leave all other options as default
If you have differing security requirements you will need to adjust these as required. Essentially, it is simplest if the account running SQL Server and SQL Server Agent on the central server is an administrator of each target server.
Reporting on the Central Data for Checking Backups and DBCC Checks
For this I use three stored procedures: one for checking all maintenance, both backup and DBCC, and the one for just checking backups, and another for just checking DBCCs. These are fairly straightforward procedures, and you could easily adapt them for your individual requirements.
CREATE procedure prssr
as
SET NOCOUNT ON
SELECT EventDate, Servername, ErrorLogText AS ‘Events for all servers’
FROM ServerHistory
WHERE (DATEDIFF(HOUR, EventDate, GETDATE()) < 24)
ORDER BY Servername, EventDate
SET NOCOUNT OFF
GO
CREATE procedure prssr_backup
as
SET NOCOUNT ON
SELECT EventDate, Servername, ErrorLogText AS ‘Backup Events for all servers’
FROM ServerHistory
WHERE (DATEDIFF(HOUR, EventDate, GETDATE()) < 24)
AND ErrorLogText LIKE ‘%back%’
ORDER BY Servername, EventDate
SET NOCOUNT OFF
GO
CREATE procedure prssr_DBCC
as
SET NOCOUNT ON
SELECT EventDate, Servername, ErrorLogText AS ‘DBCC Events for all servers’
FROM ServerHistory
WHERE (DATEDIFF(HOUR, EventDate, GETDATE()) < 24)
AND ErrorLogText LIKE ‘%DBCC%’
ORDER BY Servername, EventDate
SET NOCOUNT OFF
GO
So these stored procedures will give you everything that’s happened on your server in terms of backups and DBCC checks in the past 24 hours.
The first, prssr (short for Procedure Show Server Results) shows all events, both backups and dbss checks. The second, prssr_backup, shows only backups, and the third, shows only DBCC checks. I set up 3 web pages, one for executing each of these 3 procedures, so that I can choose whether I want to see everything or just backups, or just DBCC checks.
The value 24 (hours ago) can be set to whatever you require, or you could rewrite the stored procedure to take it as a parameter which you can set each time you run it. In fact, in my work, I use 28 hours, not 24, because my data warehouse backs up during the morning, and I need to include it – if I’m running the stored procedure in the afternoon I don’t want my warehouse’s bits to drop off the end of the results because they are longer ago than 24 hours by then (and remember, the job that collects the data only runs once a day, so the procedure won’t pick up that morning’s output yet, only the output from the day before).
I’d recommend you running this with a parameter of 24. But, say on a Monday morning, you might want to see what’s happened over the entire weekend – just adapt it and change the time criteria to be 72 hours, for example.
How you execute this procedure is totally up to you. You can run it in Query Analyzer if you like. I have embedded mine in an Active Server Page, so I can just pull up this page in my browser and see the results there. At this point, non-programmer DBA people mustn’t panic! It isn’t that hard at all. In fact I used the wizard in Microsoft FrontPage to do mine. Here is a screenshot with sample output.
To use an ASP to run this, which is quite a good way of impressing the boss (“Hey, look at my automated maintenance checker embedded in a web page” etc.), you’ll need a server running IIS, and you need to set up a folder for your pages, and a Data Source Name connection to point your pages at (if you’re using the FrontPage wizard). More experienced ASP people can easily do this in code. You also have to create an “application” in IIS to enable it to run the active content in the pages.
I am not including detailed or comprehensive information in this article about how to set up IIS and how to construct ASP pages to query the data you’ve collected, because it’s beyond the scope of what I am trying to show you how to achieve. But it’s worth investing some time in. It’s particularly handy if you have a different person than yourself (e.g. a shift operator) checking your maintenance. You can simply send them a link to the page that runs the stored procedure and get them to check that for errors. So you can save someone else (or yourself if applicable) the time-consuming task of connecting to each server in turn and looking at job outputs or text files.