Getting Organized
- Getting a Count of Database Users - Part 1
- Part 2 - Automatically Gathering Database Size
- Part 3 - Automatically Getting Log Space
- Part 4 - Getting Organized
- Part 5 - A Daily Report
- Part 6 - Capturing Ad hoc Information
- Part 7 - Checking Job History
- Part 8 - Tracking Your Objects
Introduction
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?
The Problem
Well, when I first started to gather information about my SQL Server, I had one server and it wasn't 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.
My Solution
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 data.
So I created a small database on each server called DBAthat 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 the server.
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.
Conclusion
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.
Steve Jones
© November 2000