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

  • 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?

  • 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

  • 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

  • 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

  • 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

  • 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.....

  • 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

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

  • 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]

  • 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
  • thanks to reminer our friend it is a very old post 🙂

    Best Regards,
    Marcos Rosa / marcosfac@gmail.com

  • 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