Shrink Transaction Logs

  • Hello,

    I run dbcc sqlperf(logspace) and my database as the log as you can see:

    Database NameLog Size(MB)Log Space Used (%)Status

    MyDatabase 20832,55 90,91605 0

    After i run the following commands:

    backup log MyDatabase with truncate_only

    go

    dbcc shrinkfile (MyDatabase_LOG,5000) -- @FILENAME ,@MB

    go

    but the log file still appears with like this:

    Database NameLog Size(MB)Log Space Used (%)Status

    MyDatabase 19176,05 99,38473 0

    Can you tell me what should i do to shrink the transaction log without impact and can you explain me what should i must always do to shrink logs.

    Thanks and regards,

    JMSM 😉

  • The first question, what recovery mode are you running in? I sort of hope that you are in SIMPLE. (if not, then I assume that you are trying to reduce the log file because it has grown too large, and you are not expecting to grow to this size again (note, when you truncate the log, like you do, you break the log chain) :))

    Are you using replication? If not, you most likely have a transaction that is still running. You are using most of the log space in your transaction log, so most of it is active, therefore it cannot be shrunk. The reasons for the active part of the transaction log being active is on http://msdn2.microsoft.com/en-us/library/ms345414.aspx

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • The database is in Simple Recovery Model.

    How can i put in 'pause' any transactions that are running to shrink the log without impact with database performance? Is this possible?

    I've done this operation a lot of times but now i don't know what to do.

    I execute the dbcc opentran('MyDatabase') and in the output appears one user transaction that started in the following date: Start time : Nov 13 2007 5:54:41:733PM, then i execute dbcc inputbuffer(200), and in the result appears 'set implicit_transactions on' :blink:

    Transaction information for database 'MyDatabase'.

    Oldest active transaction:

    SPID (server process ID): 200

    UID (user ID) : -1

    Name : user_transaction

    LSN : (488:31647:1)

    Start time : Nov 13 2007 5:54:41:733PM

    SID : 0x010500000000000515000000896f7898e87b12646f6c242494010300

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I need to know exacly what should i do to shrink the log file with the minimum of impact in database performance.

    Once more, thanks for your help.

    Regards,

    JMSM 😉

  • Check who it is who is responsible for this transaction

    sp_who2 200

    If you think that this transaction was a mistake, then kill the 200 process (this will roll back that transaction), and you should be able to shrink the log file.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Truncating the log file and then shrinking it should work. Also, any file cannot be shrinked smaller than its initially specified size (during creation). If the initially specified file size is too big, then you may have to alter the database, to reduce its size.

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

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