This article continues the same topic of Automatically Gathering
Server Information. If you want to read the previous articles, click on one of these links:
Getting a Count of Database Users – Part 1
Automatically Gather Database Size – Part 2
Automatically Gathering Log Space – Part 3
Now that I have a few solutions for gathering information, I
realized that information overload is rapidly becoming a problem. Checking on
the information being recorded everyday is a time consuming problem, time that I,
at least, do not have. After all, what good is the information if you do not read it or use
it. What if you are out sick and miss a day? What can you do?
Well, when I first started to gather information about
my SQL Server, I had one server and it wasnt a big deal. Then I got two more
servers pretty quickly and it started to become an issue. Then I got a new job
with over ten servers and I really needed a way to manage the information.
There was no way I could afford to spend a couple hours each day manually
checking on all the information I had automated the gathering of. The first
thing that was needed was a way to get organized.
I decided that I needed a place to hold all the information
that would be readily available without impacting other systems and processes.
In addition, some amount of history would have to be maintained, so simple
emails of data from the server were not good enough. I decided that a small
database on each SQL Server would work well, have minimal impact on other processes,
and by using a production server, backups, fault-tolerance, etc. would protect my
So I created a small database on each server called DBA that
I used to hold information for DBA-eyes only. Since this is summary information, not
much space was needed and I could create this without impacting the disk space on
This has worked well for me, until I started working in an
environment where my servers were managed and hosted by another company. In
this case, I still had my DBA database on a local SQL Server, but added a few
tables in the hosted database to gather the information and hold it until it
could be transferred to my local DBA database. I am including the code I use to
create the local database on my SQL Server here:
Creating the database is only the location for storing the
data. The other part of getting all this information organized, is to ensure
that it is updated on a regular basis and then generating a report of this
information. You can get started by including any of the information you need from
Parts 1-3 of this series in this database and scheduling them to run on whatever schedule
you think is appropriate. I generally run Part 3 on a daily basis.
Organization is extremely important to most anyone to be successful. The few
who can run their business in chaos are the exception, as IT projects have proved
time and time again over the past thirty years. I have not had any problems in any
size shop with setting up a small database for administrative purposes and I would
be curious if anyone else has an issue. My one recommendation would be to not grant
any permissions to anyone other than DBAs in this database so there is no chance that
anyone will alter this information.
As always, I welcome feedback, especially if you see some
way to improve this process that I may have missed. If you have ideas for other
information that you think is important, let me know. I may not have thought of
it and will probably tackle the project and spread the word.