Post reply

DBCC LOGINFO has a status 2 that I can''t seem to get rid of

  • April 19, 2005 at 1:07 pm

    #85540

    I have done as many tricks as I know (forcing a shrink with dummy records, backing up tx log regularly, shrinking files, etc.) and while this has worked well in the past I seem to have a transaction stuck in my active portion of my log that I can't get rid of.  This is indicated by the status 2 transaction being at the top (not the bottom), having several VLF's with 0 status and then the usual active portion at the bottom.

    Is there any way to know what transaction this is and to force it to the inactive portion of the log?

  • April 19, 2005 at 10:26 pm

    #553630

    DBCC OPENTRAN

    Displays information about the oldest active transaction

    I use this with sp_who to find and kill the process that is holding a transaction open, as long as it is open it will remain in the "active" portion of the transaction log.

    I have had to resort to setting the database to simple recovery mode, and dump the transaction log to overcoe this issue. I would also suggest after shrinking the log file, set the recovery mode back to Full, and perform a full database backup.

    Andy

  • March 5, 2010 at 2:27 pm

    #1129467

    What is DBCC OPENTRAN shows that there are no open transactions?

    DBCC LOGINFO shows 871 records in my case. The first 864 all have a status of 2.

    My log file is 40gb and I'll really like to knock it down.

    I've tried everything around shrinking, setting to SIMPLE then FULL recovery, doing FULL backups, numerous TLOGs, etc.

    I'm all ears about what to try next.

    Thanks in advance.

    Nick

  • March 5, 2010 at 2:38 pm

    #1129475

    Actually, there is some infor that comes back from DBCC OPENTRAN:

    Transaction information for database 'MDS'.

    Replicated Transaction Information:

    Oldest distributed LSN : (0:0:0)

    Oldest non-distributed LSN : (1306902:1328:1)

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

    I don't know what to do with it though.

    Nick

  • March 5, 2010 at 2:42 pm

    #1129478

    Ok, that LSN turns out to be 98 out of 871 returned from DBCC LOGINFO. It has a status of 2.

    Regarding DBCC LOGINFO results the first two have a status of 0 and the last eight have a status of 0. The rest have a status of 2.

    Nick

  • March 5, 2010 at 3:12 pm

    #1129484

    So I found one more thread that says to turn on replication for that db, run sp_repldone, and then turn it back off as follows:

    EXEC sp_dboption 'MDS', 'Published', 'true';

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

    EXEC sp_dboption 'MDS', 'published', 'false'

    However, the first command doesn't work because replication was never even configured on this server.

    So I configured a distribution database (scripted it), ran the above commands, and then dropped dropped replication on the server.

    Then all the backup log and shrink file stuff worked.

    7 hours later.....

  • November 4, 2011 at 8:00 am

    #1403236

    DBCC LOGINFO shows the extends not empty into your log file. Shrink commands will only work when you have NO entry on the log file extends marked as 2 (in the head of the file)....

    If you want to knock down this 40GB ldf, then follow below steps:

    Option 1 (saving the trn files)

    1 - Take transaciotn log backups till your DBCC LOGINFO gets all extends with 0

    2 - Run the shrink command as the extends are now free and the head of the ldf file is oka to be shrunk.

    Option 2

    1 - Run a backup with truncate_only to your database

    2 - Shrink the log file

    3 - run a full backup of your database otherwise All other backup log will fail!

    Regards,

    Marcos Rosa

    Best Regards,
    Marcos Rosa / marcosfac@gmail.com

  • November 4, 2011 at 9:47 am

    #1403348

    Option 1 is much better. your lsn chain is not broken. 🙂

  • October 1, 2013 at 10:09 am

    #1654970

    I think that you will never see all zeros because one VLF is always in use. What you need to see is 2 near the beginning and zeros at the end. We can't shrink a file from the middle, only by cutting off the end.

    Also, the word is "extents", not "extends".

    Also, DBCC LOGINFO shows one row per VLF, not per extent [thank goodness, that would be horrible]

  • October 1, 2013 at 10:40 am

    #1654979

    Please note: 8 year old thread.

    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

  • October 1, 2013 at 1:43 pm

    #1655046

    thanks to reminer our friend it is a very old post 🙂

    Best Regards,
    Marcos Rosa / marcosfac@gmail.com

  • October 2, 2013 at 10:07 am

    #1655259

    GilaMonster (10/1/2013)

    Please note: 8 year old thread.

    Yes, but is what I said false because of that?

    Besides, Marcos's post was only 2 years old 🙂

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

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