I don’t consider myself an expert on Microsoft SQL server, as a matter of fact I don’t know much at all and depend almost entirely on the Internet to solve my problems.
So on Monday morning when I came into the office to find that our Intranet wasn’t working because the database server was full I didn’t know where to start. After searching around for a while on the server I discovered that the database log file was about 9.5GB!
Before I shrunk the log file I had to backup the database and the log file, but to do that I needed space, which I didn’t have on the hard drives in the machine (You can’t use a network share for this because of how the SQL Service runs). Also, it was a SCSI only machine, and I didn’t have any working SCSI drives around to use. I ended up pulling in an old IDE RAID controller and 2 x 40GB IBM Hard Drives to do a backup of the database and log files. Unfortunately one of the two hard drives had failed, so I ended up with a single hard drive and no mirrored set-up like what I originally wanted.
After finally backing up the database and log files I then went on a search to figure out how to shrink the log file. Keeping in mind that I know pretty much nothing about Microsoft SQL I was totally lost when I read the Microsoft Support page on Shrinking the Transaction Log in SQL Server 2000.
I finally found a tool that totally saved my bacon on The Code Project.
The Transaction Log Shrink Wizard was basically built for SQL ignorants like me to do exactly what I needed. It’s a very useful tool and brought my log file down to 100MB. I only wish that it hadn’t taken me 3-days to figure out everything else before getting to the point of being able to use this tool.