Applied Tech

How to manage database log files gone wild

Last week, we upgraded our ERP at Westminster College, the college at which I am the CIO.  At the same time, we moved from SQL Server 2000 to SQL Server 2005.  For the past week, our DBA has been taking manual backups of the database while our network person, who handles long-term backup, was busy with other priorities.  Unfortunately, we did not realize that the log files were growing out of control.  Our 16GB ERP database had just over 100GB of associated log files, successfully filling up the disk volume on which they were stored.

Of course, all of this happened on the same day that I was leaving for a conference and the DBA and his backup both called in sick.  So, from afar, I was on the hook to fix the problem.  I'm not an expert when it comes to SQL Server, but I do know my way around and can figure things out.  Furthermore, listserv queries to others are always handy in order to make sure that what we're seeing is truly unusual and not just a function of the new ERP or database software.

Here's what we did:

1. I added a scratch volume from our SAN to the server so that we had some place to which we could copy files or take backups as necessary.

2. I called the person that handles backups and asked him to make the new database server a priority to get into a permanent, automated backup schedule -- as opposed to the existing manual method -- and to take an immediate backup of the database.

3. Once that was complete, I did a manual backup of the log files using SQL Server Management Studio.

4. Next, I shrunk the log files  --  again, using SQL Server Management Studio.

5. Once shrunk, I took another manual backup of the database, just for good measure.

By the end of the process, we went from having 90MB free on a 100GB volume to having 98GB free.  And, with the database server now a permanent part of the backup cycle, we shouldn't run into this problem again.

Lessons learned

I learned a couple of things from this fiasco:

• We should have simply instituted the permanent backup solution instead of getting by with manual backups.  Next time, we'll prioritize the work differently.  Of course, all of the work is important and it's only through hindsight that we see some things that should have been handled differently.

• No upgrade is without problems.  Of course, this is something well-known by anyone that's spent any time at all in IT, but it gets reinforced every time an upgrade happens.  No matter how much planning goes into the project, or what steps you take to get around short-term problems (in this case, taking manual backups), something will always come up.