Dell has this great piece of software called “IT Assistant” which I use to keep an eye on all of our Dell servers, switches, desktops, et cetera. It uses SQL Server to maintain a database of all the information it has to keep track of. It uses the database A LOT! The problem with this is that the LDF for the database can grow quickly if you have a few managed systems that are reporting errors on a frequent basis. Like, a server with whose RAID array status goes from healthy to degraded Friday evening. The server keeps telling the ITA database about it, and each time it does, the SQL transaction log, as I believe the LDF file is called, makes a note of it. This audit file can grow to to the point that there is no space left on the server by the time you come in on Monday morning and you realize, “Hey, that server is out of disk space! WTF?”
I’d remembered encountering this problem once before (not sure if it was this exact same scenario or something with SMS Server 2003’s SQL database), but the point is that I had to do something about ITAssist_Log.LDF taking up 10 GB of space on a 20 GB volume. I did a quick search for “compress” and “LDF” and the first result I got was from sqlteam.com’s forums. And it was right on the money. Apparently, this has been googled a few times already.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32238
To quote robvolk on the sqlteam.com forums to shrink the LDF for the database one should run the following statements against the database in question:
USE nameOfDatabase
DBCC SHRINKFILE(2, 250)
I suppose the DBCC SHRINKFILE(2, 250) is the money statement. I looked it up on MSDN and it’s really way to deep to get into. Bascially, DBCC stands for Database Console Commands. After calling that function you specify one of four different types of statements: maintenance, miscellaneous, informational, and validation. Although it doesn’t say so in the article, I would assume that the SHRINKFILE statement is of the maintenance variety. The curious can find the MSDN entry here.
The way to avoid this from happening in the future is to set up a database maintenance program which includes regular backups. The backups clear the log files. The IT Assistant isn’t really mission critical, so it doesn’t need to get backed up.
QuantumTom 