Post reply

Database backup and transaction log file size

  • October 1, 2007 at 1:31 pm

    #178443

    Using SQL Server 2005 standard edition.

    Work in a small shop with no dba support. My background is as a developer.

    Noticed that a database transaction log file (ldf) is about twice the size of the table file (mdf).

    Performed a full backup, followed by differential and transaction log backups. The database recovery model is 'full'.

    After performing all three backups, the ldf file size remained the same. I was expecting it to reduce in size.

    Can anyone tell me why the ldf file didn't reduce in size?

  • October 1, 2007 at 1:47 pm

    #737867

    either you have a long running transaction, or you simply can shrink the log to reclaim the unused space. use dbcc shrinkfile to shrink the log

  • October 1, 2007 at 1:56 pm

    #737871

    Thanks, Adam.

    There is no transaction running.

    I've read that shrinking a database is not a good thing to do.

    Does dbcc shrinkfile shrink the database?

    Would I just issue the 'dbcc shrinkfile' command from a query window?

    Would you suggest including the 'shrink database' step in a database maintenance plan.

    Do you automate the 'dbcc shrinkfile' command, or just execute it when necessary?

    Thanks.

  • October 1, 2007 at 2:07 pm

    #737877

    since we have plenty of free space, we do not do any shrinks. dbcc shrinkfile is run in a query window. you will need to know the name of your log file, so in the database that needs shrunk, do a select * from sysfiles and look in the name column for the log name. then do a dbcc shrinkfile('logname',5)

    this would leave 5% free in the log. I suggest not shrinking it unless you have a space issue. hope this helps

  • October 1, 2007 at 2:14 pm

    #737879

    Thanks, Adam for an excellent explanation.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply