Do You Need a Shrink?

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!