Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jeaux
jeaux
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 120

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?





David A. Long
David A. Long
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 237

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





Nick Duckstein-319444
Nick Duckstein-319444
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 115
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
Nick Duckstein-319444
Nick Duckstein-319444
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 115
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
Nick Duckstein-319444
Nick Duckstein-319444
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 115
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
Nick Duckstein-319444
Nick Duckstein-319444
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 115
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.....
Marcos Leandro Rosa
Marcos Leandro Rosa
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 71
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

viiki.seth
viiki.seth
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 158
Option 1 is much better. your lsn chain is not broken. Smile
Festeron
Festeron
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 230
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]



GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search