March 4, 2013 at 7:41 am
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 LSNOperationTransaction ID(No column name)
0045ec56:00000445:0001LOP_BEGIN_XACT0000:903b4a9bCheckDb;0x0105000000
0045ec56:00000445:0002LOP_MODIFY_ROW0000:903b4a9bDeallocated 0001:000
0045ec56:00000445:0003LOP_LOCK_XACT0000:903b4a9b
0045ec56:00000445:0004LOP_MODIFY_ROW0000:903b4a9bDeallocated 0001:002
0045ec56:00000445:0005LOP_HOBT_DELTA0000:903b4a9b
0045ec56:00000445:0006LOP_MODIFY_ROW0000:903b4a9bDeallocated 0001:007
0045ec56:00000445:0007LOP_HOBT_DELTA0000:903b4a9b
0045ec56:00000445:0008LOP_MODIFY_ROW0000:903b4a9bDeallocated 0001:007
0045ec56:00000445:0009LOP_HOBT_DELTA0000:903b4a9b
0045ec56:00000445:000aLOP_MODIFY_ROW0000:903b4a9bDeallocated 0001:007
0045ec56:00000445:000bLOP_HOBT_DELTA0000:903b4a9b
0045ec56:00000445:000cLOP_MODIFY_ROW0000:903b4a9bDeallocated 0001:000
0045ec56:00000445:000dLOP_HOBT_DELTA0000:903b4a9b
0045ec56:00000445:000eLOP_MODIFY_ROW0000:903b4a9bDeallocated 0001:001
0045ec56:00000445:000fLOP_HOBT_DELTA0000:903b4a9b
0045ec56:00000445:0010LOP_MODIFY_ROW0000:903b4a9bDeallocated 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
March 4, 2013 at 3:54 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply