Log Your Changes

Every stable environment remains that way because of change management that minimizes disruption
and provides a means to undo the changes. Every single production SQL Server you manage should
have a log associated with it for changes you may make to data, configuration, etc. If you do not have
one, stop reading this article and do the following:

  1. Open Excel
  2. Enter the following information in the first 4 columns of the first row:
    • Date
    • Database
    • User
    • Change
  3. Save this in the following format ON YOUR NETWORK: .xls

Now, you have a log for your SQL Server. If you are still reading and still don’t have a log, back up a
paragraphs and follow the instructions before I will explain why.

Why log?

OK, now that everyone has a log for his or her SQL Server, Why should you have a log? Because it will
save you worry, anguish, and potentially your job. I cannot even begin to count the number of times that
having a log has substantially cut down the time it takes to fix a problem, has saved a server rebuild or
database restore, or provided justification to my management.

We are all human (at least all DBAs I know) and we all make mistakes. It’s part of life and it is going to
happen. So accept this, try to learn from your mistakes and get better at your job. You are also going to
forget things. That’s the other part of being human. My kids forget everything I tell them at least once, but so did I when I was
eight. I got better over the years and so will they, but I still make changes to a server and forget what I did
because I may make dozens of changes to multiple servers on the same day. Even my neurosurgeon
friend with the photographic memory forgets things and is forced to document his work to be sure he can
track his progress.

So, since you are going to make mistakes (and so is everyone you work with) and going to forget things,
it behooves you to keep a record of these changes. There are more reasons that I can list why this is a
good idea, but all well-managed shops implement some type of change management. If you are a single-
person IT department with one or two servers, you might be able to memorize the changes and keep a
mental log, but I wouldn’t recommend it. Eventually you will get busy and forget something, and the
change that you made two weeks or months ago may be the cause of the problem. One of the last
reasons is that when you run out of ideas to fix your problem and seek help from Microsoft or some other
resource, the first question you will be asked is “What changed?”.

Since you are human (remember the start of this section) and you will forget things, having the log will greatly increase the quality of help you
get from someone else. As with everything else in this business, if bad data is input, then bad data will
come out. If you cannot provide a tech support resource with good data, it is less likely you will get good
data from them. One form of managing change is to keep a log for the server that contains all changes
made to the server.

How Do I Use the Log?

Now that you created a log (you did create a log, didn’t you), what do you do with it? I have a very simple
rule: I log everything. And everyone that works for me logs everything as well. If they do not, then I get
upset and eventually they will likely be searching elsewhere for employment. I think it is that important.
Now I hate to create extra work and try to keep everyone functioning as efficiently as possible. In order to
ensure that we do not spend too much time logging changes, everything is scripted. These scripts are
then saved in our version control system as well as on the DBA computers. Then when you run a script,
you make a note in the log with the script name and the parameters or changes to the script. We have
decided that you must provide enough detail in the entry to allow another DBA to recreate the item that
was run. Not for a network administrator or a developer or anyone else, only a DBA.

In order for the log to work correctly, you also have to log everything in a timely manner. At the very least,
you must complete all log entries before leaving that day. I prefer that you log things ASAP after they are
run so that if you walk away or go on an extended bathroom visit and something breaks, I can check the
log quickly.

Now, we do not have Excel on every computer and it is on none of the servers, so what do we do?
Everyone has to carry a pad of paper with them anytime they go into the server room. That way they can
make notes as they perform some action. These notes need to be transcribed into Excel that day, but we
do not make that many changes at the server console and it is not a big deal.

Conclusion

Logging is prudent and a good habit to get into. Once you start, I am confident that you will quickly find
that it pays dividends quite often, especially when working with a team of DBAs. As with any advice I
offer, you will probably have to adapt this to your environment and make changes. But you should
implement some form of logging.

We are starting to enhance this and port our logging environment to the web for our Intranet. It needs to
be convenient and easy so people will use it, so I am in the process of testing different interfaces and
trying to make this as quick and simple as possible for others to use.

I welcome comments and suggestions for ways to better ensure you have a stable and manageable
environment, so please email any you have.

Steve Jones
copyright 2000 dkranch.net

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles