MS SQL Server File Sizes

10-15-2004, 08:25 AM
I have a log file for my development DB on my SQL Server. This log file is 2GB large. In Enterprise Manager, I:

Right-click the DB
Select Backup Database
Run a backup of both DB and Log Files
Right-click the DB
Select Shrink Database (3549MD allocated, 2637 Space Free)
Select 10% for Maximum free space in file after shrinking
Click OK
Get MsgBox saying Shrink is done.

When I check the size of the log file in MyComputer, the log file is still 2GB when the DB is only 1.5GB. So then I:

Right-click the DB
Select Shrink Database
Click Files... command button
Select the log file name in the dropdown box
It shows Current Size: 2014MB, Space Used 20MB
Try Compress pages ... option, file still 2GB
Try Truncate free space... option, file still 2GB
Shrink file to: and set it to 50MB ...file still 2GB

No matter what I do, the log file remains at 2GB. What the heck is going on? If Enterprise manager says the file uses merely 20MB, why won't the file shrink any less than 2GB???

10-15-2004, 06:08 PM

What OS are you working with? Does this happen to be XP SP2? If so, there are some problems regarding timestamps...but I will not carry on regarding this without knowing if you are using this OS :)

Does this problem happen frequently? Can you re-produce it elsewhere?

10-18-2004, 11:28 AM
The server is on Windows2003. It turns out that I scheduled the transaction log backups periodically during the day, and when I came in to work today the physical file size shrank down to about 200MB ... much better than 2GB! I don't know if this happens frequently yet, because I never knew I had to backup the transaction logs in order to have them shrink. :o :o I never had the responsibility of handling a production backup scheme on SQL Server before. I just dabbled in it :) . But so far, scheduling the backups of the trans log seems to have taken care of things.

I wonder if it was a bug in Ent Mngr? This situation led me to looking at the DB Maintenance Wizard, and that has LOTS of evil bugs. It doesn't always set the backup folder you specify, and using the GUI to make changes produces errors and will not save some changes.

I have to look into creating my own backup scripts to learn what really should be done, and how it should be done.


EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum