Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 13, 2000

Automated Server Information Gathering - Part 4

By Steve Jones

Getting Organized

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?

The Problem

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.

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 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 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.


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM