Compressing the LDF!

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.

COFFEE DETOX: DAY 3

I am now three days off coffee. My last cup was Friday morning. I slept like the dead last night and decided to forgo my usual Monday morning eye-opener. Instead I had two cups of an energy-oriented green tea – it’s definitely not the same, but it’s better than nothing.

Yesterday afternoon I began reading Eats, Shoots & Leaves: The Zero Tolerance Approach to Punctuation by Lynne Truss and today I find myself unusually wary of my grammar and spelling. I’d hate to sacrifice style for structure, but I don’t know that I’d really feel comfortable making mistakes in such a public forum, even if no one is seeing them.

Well, I’ve had about enough of work this morning; I am off to lunch.

Workday

I was so flippin’ productive today I ought to pat myself on the back.

I wrote a killer LDAP query to implement a query-based distribution group for our Exchange clients, made some good solid security enhancements in some of our Group Policy objects, resolved an issue with one of our DHCP servers’ authentication, and made significant progress in the design of our new and enhanced network topology.

All of that is going on my resume.

Have a great weekend!