Link to home
Get AccessLog in
Avatar of jtequia
jtequia

asked on

How do you shrink an LDF file in MS SQL 2005

I have a SQL DB MDF file that's 300MB but the LDF file is 3gig, I checked the loggin and it's set to simple, I backup nightly what am I missing?
Avatar of chapmandew
chapmandew
Flag of United States of America image

dbcc shrinkfile('ldfname', 0)
backup will not shrink the file, just try to keep it from growing more.

DBCC SHRINKFILE command is what you need to run to shrink the file actually.
Avatar of jtequia
jtequia

ASKER

Is there a setting in the DB properties where I can set this once and forget about it?
>Is there a setting in the DB properties where I can set this once and forget about it?
no.

you "could" set up a maintenance plan to do regularly a db/file shrink, but that would be poor database administration, as once you shrink the file, it will start growing again, just wasting I/O (and getting your file(s) spread over the disk)
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Another method is to set up a maintenace plan to backup the logs periodically.  This will truncate the log files, and at the same time you will have a backup of the logs.  I normally do this by creating a mainentance plan, then scheduling it.
>>This will truncate the log files

Ummmm...not really.  It will remove inactive transactions from the logs, but it will NOT truncate them.
>> It will remove inactive transactions from the logs, but it will NOT truncate them.

"Truncating the log" means precisely that: removing the inactive transactions. You are right that backing up the log will not SHRINK the log but it will truncate it. XUPH18RLG is quite correct about that.

I mention this because many people get confused about the difference between shrinking and truncating. They are not the same thing.
Autoshrink property
Well, that is not the best choice because it will cause fragmentation of your ldf file... shrinking file in a frequently fashion make us thing that the administration of the database is not going fine
Right click your database -> Properties -> Options: in the Automatic group you will see Autoshrink
Autoshrink is BAD. The only reason to go look for that feature is to make sure it is turned OFF!
My mistake, for some reason I got thinking "truncate"...as in truncate table.  Anyways, dportas, you're 100% right about the truncation upon the log backup.

You're also right that autoshrink is BAD.
I hate to interrupt all of this, but it would appear that the database recovery model is set to simple, at least that is what could be understood by this: "I checked the loggin and it's set to simple"
Could it be an unclosed transaction? the cause of the unexpected grow?