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

Return to Steve Jones Home