Stress is mounting for IT professionals, and for some fear is the dominant emotion of the day. Some realize the need for professional help. Let's look at a typical IT worker, Dave, who is experiencing "high anxiety."
The setting: A quiet therapist office at the Windows IT Pro building with Dave on his first visit to Dr. Avatar.
Doctor: So you're an IT administrator. Why don't you just fix it?
Dave: Because I'm afraid to touch it.
Doctor: Hmm… Odd phobia for an IT admin.
Dave: No, I mean touch the SQL Server database and the application. I don't know anything about them, except for the five minutes I spent on the phone with their support team, which cost me.
Doctor: Dave, there are different kinds of fears for Microsoft SQL server–based applications: irrational fear of SQL Server and a healthy fear of SQL Server. Let's see if we can help you develop the latter.
Dave: Well, it didn't help that the server installers from the Mega Vendor Server Corp. made the system partition so small, and that the vendor took all the defaults within the installation wizard.
Doctor: OK, Dave, that's in the past. It's your job to move forward. You can do this by gaining a better understanding of your server, MSSQL, and how databases work. Now do you have the most recent SQL Server service pack installed?
Dave: Well, I've downloaded it, but if something goes wrong and there is any down time, I'm afraid the owners will have my head—no offense, Doc.
Doctor: None taken, it goes with the territory. So how much space is available on the drive now?
Dave: 27MB.
Suddenly, Dave's cell phone goes off with a text message: There's an error on the accounting package again.
Dave: You see, this is what I mean, I'm going crazy! Do you mind if I use your laptop over there to get a session to my server?
Doctor: Not a problem, perhaps I will get a better understanding of what is happening.
Dave: OK, here's what I've been doing. Every time they get a message like this, I compress some files on the C drive or move them off, but I'm running out of options. The size of the partition is 20GB.
Doctor: It just so happens that we run the same program at the office here, and even though I'm a doctor I have to wear many hats in this economy.
Let's take a look at my SQL Server—at the data subdirectory of my SQL Server folder in the programs folder. I'm running Dynamics CRM to keep track of my patient history. Notice the file extensions, Dave?
Dave: Yes, they're MDF and LDF. But your LDFs are real small. Mine are really large, and I stopped to back up because they were filling up the drive. I feel like I'm caught in a web!
Doctor: Not to worry Dave, not to worry. The first thing we must do is to alleviate your fear of running out of disk space for the next few days. Let's start with understanding what the LDF file is. Understanding is the key to overcoming irrational fear of SQL Server.
Dave: So why are my LDFs so big?
Doctor: Well, you have to understand that the first place that data is placed in SQL is in the transaction log. Then there is a process called the commit process that runs every 10 seconds by default and writes or commits that data to the MDF file.
Dave: So why are they bigger than my MDF files?
Doctor: Well, we all like head room. People often like tall ceilings for rooms, except when they have to change a light bulb up there. SQL Server likes head room, too, and whenever it thinks that a decent amount of data is coming its way, it expands the room, so to speak, to keep that head room. The problem is if you don't take care of your SQL Server transaction logs, they don't contract. That means that you have this big file that has basically nothing in it, yet it takes up lots of space on your drive. In this case, it's your C drive.
Dave: Can I just erase them?
Doctor: Well you could, but you'd have to shut down the Microsoft SQL Server service, and then your users wouldn't be able to reach the server and do work. You could detach the database and re-create the log file and point it to another drive and folder on your server.
Dave: Sounds scary. That's the kind of stuff that keeps me up at night.
Doctor: Well, there's a first time for everything. After all you learned how to ride a bike, didn't you?
Dave: Uh… that's another issue.
Doctor: Well that being said, you can also "shrink" those transaction logs right now while your users are connected.
Dave: Really? Can I do that through the GUI?
Doctor: There is an option to shrink files through SQL Server Management Studio, but in some instances people have less success than others. But there is T-SQL code.
Dave: Yikes! Did you say code? I'm not a developer!
Doctor: No worries, I'm sure you've done batch files before. It's not much different.
Dave: OK, now I think I'm breaking out in hives.
Doctor: Well, we won't get into the registry right now. So then, open up SSMS. Notice that in Management Studio, we have to option to shrink a database file. Either the MDF file or the LDF file.
