SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Huge Transaction on Database with Merge Replication, Simple Recovery Model


Huge Transaction on Database with Merge Replication, Simple Recovery Model

Author
Message
kirkysql
kirkysql
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 364
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



sergi0
sergi0
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 732
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.
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