Post reply

Reclaim Transaction Log Space

  • January 17, 2008 at 7:05 am

    #769201

    Adam,

    Thanks for catching that. I corrected my posting.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • January 17, 2008 at 7:09 am

    #769205

    NP 🙂

    I knew what you meant. I just wanted to correct it for other posters.


    My blog: http://jahaines.blogspot.com

  • January 17, 2008 at 7:21 am

    #769216

    Damon Wilson (1/16/2008)

    SELECT name AS NameOfFile,

    size/128.0 as TotalSizeInMB,

    CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpacesUsedInMB,

    size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB

    FROM dbo.SYSFILES

    go

    Thanks, Damon, for this tasty bit of code - I like it!:D

  • January 17, 2008 at 2:28 pm

    #769454

    Simple is probably the correct setting, but I realize it seems odd that the log is 2x the size of the db, but we are only talking about 30g. Is this really that important? The log grows all at once because of how you are loading the DB. Once you do the backup, the space is freed in the logfile. It shouldn't grow again. If it does, then something is going on that is generating changes... Which you would want to backup.

    If you log is growing, you are changing data. The log is growing because changes you told it to track haven't been backed up yet. If you don't care about those changes, stop tracking them. (which is where the simple mode comes in).

  • January 17, 2008 at 9:09 pm

    #769536

    Thanks a lot everybody,

    I feel much more confident about handling the log files after all this discussion with fellow boarders.

    For the record, I've put my DB in simple mode with a Full DB Backup everyday & Incremental Backup every 4 hrs.

    I'm hoping I wouldn't stuck in a new problem.

    Thanks Adam, Suji & Damon for your valuable inputs.

    I'll get back to the forum if I encounter any other problem.

    Wish you all a good day.

    Ankit Mathur

  • August 14, 2009 at 7:20 am

    #1039308

    I'm facing a similar issue. I have a transaction log that I am unable to shrink, as the recovery model is Simple on the database. This is not a database that will require a point-in-time restore, as its used for reporting. What is the best way to manage the log file? It is currently 42GB log size for a 7GB database. The option to shrink files on the log file shows 96% free space available. But because the database is set to simple, the log file cannot be shrunk.

    I'm not sure what the best approach is to maintain the size of the log file, and disk space has become an issue. Any advice on how to manage database log files when the recovery mode is set to Simple?

    Thanks,

    Kay

  • August 14, 2009 at 7:28 am

    #1039312

    kwilt (8/14/2009)

    I have a transaction log that I am unable to shrink, as the recovery model is Simple on the database.

    Having the DB in simple doesn't stop you from shrinking the log. DBCC ShrinkFile, same as in any other recovery model.

    Just make sure that you've fixed whatever caused it to grow in the first place, or it may just grow again.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 14, 2009 at 7:32 am

    #1039320

    I tried shrinking the file using the GUI option in Management Studio (right-click > Tasks > Shrink> Files > Log.)

    The log didn't shrink, so I thought it couldn't be done. If I were to use a script instead of the GUI, would the file shrink?

  • August 14, 2009 at 7:43 am

    #1039334

    No. The GUI runs the same script you'll run. Could be there's an open transaction of something preventing the shrink. Maybe wait a few hours and try again. It often works.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 14, 2009 at 7:55 am

    #1039351

    All I'm trying to do is shrink the log file to a reasonable size. Since the recovery model is Simple, there are no transaction log backups and I was under the impression that the only way to shink a log file is to have two consecutive log file backups and then the shrinkfile will work. If there is way to shrink a log file on a database in Simple recovery mode, that is what I'm looking for. The space this log is consuming is far too large for the size of the database.

  • August 14, 2009 at 8:12 am

    #1039383

    kwilt (8/14/2009)

    I was under the impression that the only way to shink a log file is to have two consecutive log file backups and then the shrinkfile will work.

    No, not at all. Shrink doesn't depend in the slightest on log backups.

    The only thing that log backups would do is to free up space inside the file in Full and Bulk-logged recovery to allow the shrink to reduce the size of the file, and you'd only need one of them, not two. In simple, because the log space is automatically freed up (whenever a checkpoint runs), there's no need for anything like that.

    If there is way to shrink a log file on a database in Simple recovery mode, that is what I'm looking for.

    DBCC SHRINKFILE, same as in any other recovery model. This is exactly the command that the GUI runs when told to shrink a file.

    It's possible that there's an open transaction that's preventing the log records from been moved and the log from being shrink. Maybe try waiting a few hours (for any transactions to have ended and for the active portion of the log to have moved in the file) and try again.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 14, 2009 at 8:17 am

    #1039389

    Thanks so much for the response and information. I will use the DBCC SHRINKFILE and report back on the results.

  • March 25, 2012 at 3:28 am

    #1463987

    Hi Friends,

    I am facing an issue on reclaiming space from transaction log with my sql server 2008 with sp2. It grew up to 200 GB and running out of space. Then i backed up the transaction log. Now i can see that the log space used is only 5.26 gb out of 200 gb. When i used DBCC Shrinkfile, it is not reclaiming the space from the transaction log.

    I do not know how to proceed with it further.

    Please help.

    Regards

    Govind

  • March 25, 2012 at 4:29 am

    #1463992

    Please post new questions in a new thread. Thank you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 25, 2012 at 11:15 am

    #1464013

    govindarajan69 (3/25/2012)

    Hi Friends,

    I am facing an issue on reclaiming space from transaction log with my sql server 2008 with sp2. It grew up to 200 GB and running out of space. Then i backed up the transaction log. Now i can see that the log space used is only 5.26 gb out of 200 gb. When i used DBCC Shrinkfile, it is not reclaiming the space from the transaction log.

    I do not know how to proceed with it further.

    Please help.

    Regards

    Govind

    I kind of like having all the questions related to this problem in a single thread so, let me ask, which recovery mode are you using?

    Also, has this taught you that you really need to do transaction log backups on a regular basis?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 33 total)

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