SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 211 of 365 Next >

In my storied DBA career, I've come into more than my fair share of shops that were in, well, a bit of a crisis. The former DBA had left suddenly, or the previous staff was a little less than fastidious about documenting the changes of their systems.

When you're thrown into a situation like that, you need to quickly assess the system, so you know what you're dealing with. It's important to act like a physician, and "first do the patient no harm;" make no changes until you know what the system looks like. Also, check the system backups before you do anything else. You'd be surprised to learn how many DBAs don't treat backups as seriously as they should.

You can get lots of system information about things like the backups using Enterprise Manager, Query Analyzer, and SQL Profiler. To do that, you need to understand just what you're looking for, and where to find it.

Another tack to take is to use a commercial product to document your system. There are plenty out there; one I'm fond of is Quest Software's Spotlight on SQL Server, which I've reviewed before. Embarcadero also makes a good suite of SQL products, but they can be pretty expensive.

While buying a product is the quickest route to database documentation, there are times when it doesn't suit the situation. For several years, I was a database consultant, and it wasn't legal for me to install software on my clients' servers just to document them.

Not only that, it's wise to completely understand what you're looking at, and why. Buying a product that has a green light on the database files object is great, but doesn't help you understand what that really means. Is the system OK for now (only), or will it last longer than ten minutes? For that, I've found the best thing to do is to ask SQL Server how it's doing.

Yes, you can do that. I'll show you the exact scripts in part two of this article.

Before you start writing code, however, you have to decide on the granularity of your documentation. You might need only a simple set of metrics regarding backups, maintenance, and database sizes. Or you may be looking for a lot more.

Let's begin with the basic information you might want to gather. Here are some items I normally want to know about right away:

Name of the server
Operating system
Physical information (storage info, memory, CPU, etc.)
Backups and maintenance plans

And then there are all kinds of other objects I might like to know about:

Locks
Blocks
Memory use
User names
Groups
Filegroup information
y mucho más

There are a couple of ways you can get this information. One way is to write a compiled application in Visual Basic or C# using Data Management Objects (DMO) or the new SQL Management Objects (SMO) in SQL Server 2005. That's an article for another day, so we'll move on to the second method.

SQL Server comes chock-full of system tables. These are the tables in the master and msdb databases that it uses to track itself. These tables store everything from security information to database object names and descriptions.

Microsoft has made various views on these tables, since the tables themselves are subject to change without notice. While those views are very useful, they don't have everything we're after, so I'll show you the specific tables and columns on which the views are based.

NOTE

Be advised that these tables and columns can be version and service pack-specific, so the queries might break at some point in the future. If you're really concerned about that, you can look up the Information Schema Views topic in Books Online and use those instead.

SQL Server also provides system variables that we can use to request information. And finally, SQL Server has several system stored procedures and functions to call for even more info. We'll use all these SQL Server constructs to find out what we want to know.

There are several ways within this method to display the results of the queries. You could use a high-level language like Visual Basic or C#, you could create a database-enabled Web page, or you could create a report using Crystal Reports or Microsoft Access. See the reference section at the end of this article to find out how you can even use Microsoft Word! You could also store the results in a set of tables.

I normally use this last method, since it allows me to collect the "static" information (such as server name) only once, and the other information on a more frequent basis. I store the information about all my servers in a separate database and then run reports from that to the Web. I use replication to roll the remote server information up to a central location, and then I use Analysis Services to cube it.

NOTE

If you follow this method, remember that data about your server's OS and service pack level might be very useful to a hacker. Be sure you treat this information with the appropriate level of security.

One final word before I show you the queries. These aren't all the scripts that I have for this kind of thing. I've gathered these scripts over the years, and modified them to fit my needs. Some of this stuff I made up on my own, and other parts were given to me by my mentors years ago. I try to give credit where it's due, so if you see something here that you know someone independently wrote, make sure you respond to this article with that info. I've always believed in sharing, since I think it makes us all stronger. You certainly don't have to credit me with any of these scripts, but if they have a credit to someone else, please carry that along. That person deserves the mention.

In part two I'll show you the scripts, along with an explanation of what they do. See you then!

Online Resources

SQLDev has a lot of great SQL DMO programming examples.

Believe it or not, you can use Microsoft Word to document your SQL Server. This site shows you how.

Something no DBA should be without – a system table map, compliments of Microsoft.

InformIT Tutorials and Sample Chapters

You can find out more about the system functions from our own Baya Pavliashvili in his article called SQL Server System-Related Functions.

< Back Page 211 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Email Address

SQL Server Reference Guide

Online Resources

InformIT Tutorials and Sample Chapters

InformIT Promotional Mailings & Special Offers

Overview

Collection and Use of Information

Questions and Inquiries

Online Store

Surveys

Contests and Drawings

Newsletters

Service Announcements

Customer Service

Other Collection and Use of Information

Application and System Logs

Web Analytics

Cookies and Related Technologies

Do Not Track

Security

Children

Marketing

Correcting/Updating Personal Information

Choice/Opt-out

Sale of Personal Information

Supplemental Privacy Statement for California Residents

Sharing and Disclosure

Links

Requests and Contact

Changes to this Privacy Notice