Shrinking the transaction log

  • Comments posted to this topic are about the item Shrinking the transaction log

  • One more time (as usual ? as often ? ) , "all of these apply".


    My MCP Transcript (ID : 692471 Access : 109741229)

  • I'm confused, what is the question asking? I'd like to answer it today, but with this question it would all be a shot in the dark.

  • Lynn Pettis (12/10/2009)


    I'm confused, what is the question asking? I'd like to answer it today, but with this question it would all be a shot in the dark.

    ( It would all be a shot in the dark ). That's what I did. Checked all and got points. I am not sure about this option. Will have to test it on some server. I thought its same and never observed time in seconds.

    -- If a log file has lots of free space, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000.

    SQL DBA.

  • Not a very interesting or up-to-date question. I would have expected comparisons between 2005 and 2008.

  • It was bit confusing.



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/
  • Another one busted the dust out on me...

    What you don't know won't hurt you but what you know will make you plan to know better
  • 3 of the 4 options are correct. But, this statment can't be true:

    "Reducing the physical size of a log file requires shrinking the file."

    You can truncate a transaction log and that will reduce the physical size of the log file, therefore shrinking is not required.

    Sorry for the late reply. I'm playing catchup on old questions I skipped over. 😀

  • wbeaton-664441 (3/18/2010)


    3 of the 4 options are correct. But, this statment can't be true:

    "Reducing the physical size of a log file requires shrinking the file."

    You can truncate a transaction log and that will reduce the physical size of the log file, therefore shrinking is not required.

    Sorry for the late reply. I'm playing catchup on old questions I skipped over. 😀

    I am behind on all of them but I am playing catchup anyway 🙂

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • wbeaton-664441 (3/18/2010)


    3 of the 4 options are correct. But, this statment can't be true:

    "Reducing the physical size of a log file requires shrinking the file."

    You can truncate a transaction log and that will reduce the physical size of the log file, therefore shrinking is not required.

    Sorry for the late reply. I'm playing catchup on old questions I skipped over. 😀

    Hate to be the bearer of bad news, but truncating the transaction log, either by changing the recovery model from FULL or BULK_LOGGED to SIMPLE, or using the depreciated command BACKUP LOG <DBNAME> WITH TRUNCATE_ONLY, simply marks the space used by the committed transactions as reusable. To physically shrink the ldf file, you must use the DBCC SHRINKFILE.

  • I guess I got confused by semantics... my point was that, if you use the depreciated command "BACKUP LOG <DBNAME> WITH TRUNCATE_ONLY", the physical file would literally decrease in size from say 10GB to 10MB... thus "shrinking" the file size. But, I agree that it's not technically accurate.

Viewing 11 posts - 1 through 10 (of 10 total)

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