Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DBCC LOGINFO has a status 2 that I can''t seem to get rid of Expand / Collapse
Author
Message
Posted Tuesday, April 19, 2005 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 7:32 AM
Points: 7, Visits: 87

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?




Post #175822
Posted Tuesday, April 19, 2005 10:26 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:36 PM
Points: 631, Visits: 236

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




Post #175923
Posted Friday, March 5, 2010 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 9:30 PM
Points: 9, Visits: 77
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
Post #877972
Posted Friday, March 5, 2010 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 9:30 PM
Points: 9, Visits: 77
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
Post #877981
Posted Friday, March 5, 2010 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 9:30 PM
Points: 9, Visits: 77
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
Post #877985
Posted Friday, March 5, 2010 3:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 9:30 PM
Points: 9, Visits: 77
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.....
Post #877993
Posted Friday, November 4, 2011 8:00 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 1:43 PM
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
Post #1200584
Posted Friday, November 4, 2011 9:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 12:11 PM
Points: 61, Visits: 158
Option 1 is much better. your lsn chain is not broken. :)
Post #1200715
Posted Tuesday, October 1, 2013 10:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 3:11 PM
Points: 166, Visits: 200
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]



Post #1500485
Posted Tuesday, October 1, 2013 10:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
Please note: 8 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1500495
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse