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

Huge Transaction on Database with Merge Replication, Simple Recovery Model Expand / Collapse
Author
Message
Posted Monday, March 04, 2013 7:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 17, 2014 4:42 AM
Points: 62, Visits: 277
We have a database which normally has a 100GB transaction log.
This database is a publisher in a merge replication with one subscriber.

The transaction log has grown to 235GB

When I query sys.databases for log_reuse_wait_desc I get "REPLICATION" for this database.


We recently had a corruption issue (CHECKSUM ERROR) in this database, which was fixed last thursday by running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option (only 1 page was flagged as corrupt and business decided it was better to lose this than have to restore the database)

The affected table is part of the publication.


dbcc opentran with tableresults gives:

<databasename>OPENTRAN
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (4582486:1093:1)

Checking the transaction log:
select [Current LSN],[Operation],[Transaction ID], Left([Description],20)
from::fn_dblog('0x0045EC56:00000445:0001','0x0045EC56:00000445:0010')

Gives

Current LSN Operation Transaction ID (No column name)
0045ec56:00000445:0001 LOP_BEGIN_XACT 0000:903b4a9b CheckDb;0x0105000000
0045ec56:00000445:0002 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:000
0045ec56:00000445:0003 LOP_LOCK_XACT 0000:903b4a9b
0045ec56:00000445:0004 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:002
0045ec56:00000445:0005 LOP_HOBT_DELTA 0000:903b4a9b
0045ec56:00000445:0006 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:007
0045ec56:00000445:0007 LOP_HOBT_DELTA 0000:903b4a9b
0045ec56:00000445:0008 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:007
0045ec56:00000445:0009 LOP_HOBT_DELTA 0000:903b4a9b
0045ec56:00000445:000a LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:007
0045ec56:00000445:000b LOP_HOBT_DELTA 0000:903b4a9b
0045ec56:00000445:000c LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:000
0045ec56:00000445:000d LOP_HOBT_DELTA 0000:903b4a9b
0045ec56:00000445:000e LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:001
0045ec56:00000445:000f LOP_HOBT_DELTA 0000:903b4a9b
0045ec56:00000445:0010 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:002

As far as I understood, merge replication isn't supposed to have an impact on transaction log.

The transaction log appears to be growing ever since.

Can anyone tell me why this is happening?

Thanks



Post #1426253
Posted Monday, March 04, 2013 3:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 31, Visits: 630
Is Replication working at all? If records cannot come over to the Subscription database, they will pile up in the Log file.

Have you dropped and recreated the Publication after fixing the Page?

In your case, I would first run the Replication Creation scripts. If that does not fix the issue, I would drop and recreate the whole Publication and Subscription. Make sure to run sp_removedbreplication on the replicated database after you run the Drop scripts.
Post #1426499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse