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.
Dave: So, Doctor, I could do this every night?
Doctor: No not really. Remember that SQL Server likes head room, and if you lowered the ceilings every night, then the transaction log would have to grow on the physical disk every morning. Often a VAR will deploy SQL Server and leave the databases in a "mode" called simple.
Dave: I like "simple."
Doctor: Well, maybe not. If you lost all the input from your users today and you had only a backup from last night, you wouldn't be able to restore the transactions for the data to the last backup.That's why some IT shops back up the transaction logs every few minutes and restore them to another SQL Server as a stand-by SQL Server.
Dave: This is starting to validate my fear.
Doctor: Well, with simple mode, the transaction log growth is restricted. Notice how I have this set up here, if you right-click on the database and choose Properties, then Options.
We can hit the drop-down arrow and choose full recovery. Now before transaction backups will work, you have to back up the MDF file first with a full backup. Then transaction log backups can be done. But now comes your responsibility.
You see, Dave, in full recovery, each data-change operation is recorded in the log. Now your databases are in full recovery mode, so the log files will grow and, if not truncated, will fill up your drive. At that point, SQL Server will stop working—which would, I'm afraid, cause panic!
But there are more geeky details. You see, we can reduce the risk by using a feature called autogrow. To enable autogrow on your database give the logs file a reasonable startup size. Then select the check box for autogrowth and enter a percentage. You could give it a maximum size, but when the log fills up, the database won't work.
Dave: Yikes! That's as bad as the monster underneath my bed!
Doctor: Yes, well perhaps next week we can address that. So we use this feature to allow the database to give itself the head room it needs to keep working. Sure, it uses system resources like disk, CPU, and RAM, but you can sleep at night.
Dave: So how do I get these transaction files under control now?
Doctor: Well, now, we use that T-SQL code I spoke about, and you'll drive.
Dave: Me?
Doctor: Yes, Dave. I have complete confidence in the mission, I mean, you.
Dave: Say what?
Doctor: Sorry. I've been stuck with that phobia since 2001.
Dave: I guess everyone has them.
Doctor: Yes, once in a while I space out.
Dave: About the transaction logs.
Doctor: Oh yes, open up a query window by hitting that big New Query button. Now let's do a smaller one so it won't take much time, and you can do the rest of them yourself.
I see a database called Probar on your server. First, let's have a look at the MDF and LDF file sizes. Type the following in the query window:
Use Probar
select * from sysfiles
Now highlight them, right-click, and choose Execute.
You'll notice the results below.
The files can be identified by fileID, and each file has a number, so you could use that number to refer to the file later. Now let's back up the file, but we are only going to truncate it with this backup.
We will type:
Backup log probar to disk = 'g:\probar.trn , TRUNCATEONLY'
Now, let's highlight that again and execute. Notice that I didn't back up to your C partition, but I saw that you had a 500GB partition on your server. Notice that I can specify the path in the T-SQL statement to either a local drive on the server or another server or device that I have access to. Why did the consultant from the VAR use that drive?
Dave: Drive blindness, I guess. Kind of like refrigerator blindness.
Doctor: So notice the results in the lower pane.
Processed 108774 pages for database 'probar', file 'ProBar_Log' on file 1.
BACKUP LOG successfully processed 108774 pages in 19.653 seconds (45.340 MB/sec).
Doctor: Be sure you give him my number, OK? So now, let's shrink this transaction file that's a bit larger than its MDF file.
We will type:
DBCC SHRINKFILE (2,TRUNCATEONLY)
and execute that. Then we will type:
DBCC SHRINKFILE (2,500)
and execute that.
Notice the results. The size has not changed yet.
Dave: So when does it shrink?
Doctor: You have just a few more steps to get your disk space back. Now we have to do a regular backup of the transaction file. We use a statement like the truncating one:
BACKUP LOG PROBAR TO DISK = 'G:\PROBAR.TRN'
Notice the results:
Processed 789 pages for database 'PROBAR', file 'ProBar_Log' on file 1.
BACKUP LOG successfully processed 789 pages in 0.240 seconds (26.914 MB/sec).
Now let's rerun that statement "DBCC SHRINKFILE (2,500)" and then that Select statement again. Now, Dave, what do you see?
Dave: Wow! Down from 109,704 to 128! That's a big reduction. So I can do this to my big transaction logs every night?
Doctor Avatar: Dave, Dave. Baby steps Dave, baby steps. We are just starting to deal with the result of your SQL Server psychoses and the obvious other ones. For now, let's get your disk space back, and then we'll explore your SQL Server or the reasons why your transaction logs have such an inflated view of themselves, in your next visit.
Dave: Doctor Avatar, I still have one big fear: the bill for this visit?
Doctor: Don't worry, Dave. Your subscription to Windows IT Pro magazine has you covered.
Dave: Thanks, Doc!
