Log Your Changes
October 14, 2000
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:
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.
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.
ConclusionLogging 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.