Can''t truncate log becuase of pending replication

Question

Can''t truncate log becuase of pending replication

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

David Gray-367412 Say Hey Kid Points: 700 More actions · Answer 1

From what I've read sp_repldone kills off all the publications/subscriptions so it's kind of an emergency measure. May come to this anyway as the logfile is growing at a rate of knots.

When I've fixed the problem I will post the resuults on here.

Thanks to all who responded.

Dave

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

You can try:

sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

That's what I've done, but to solve a slightly differnt problem. I wonder why you have the problem to start with ?? Once it's resolved, I would think you should be able to do normal scheduled shrinks of the log file without jumping through these hoops.

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 3

What does it do if you only drop the subscribers and don't touch the publications? My understanding is that log file truncations are held up when subscribers have not received their updates and not the fact that you may even have old transactional publications defined.

glamey Grasshopper Points: 15 More actions · Answer 4

Here is the solution/script I used to solve this problem:

-- When you cannot trunctate the log on a database that was involved in replication and was

-- restored to another server where it is not necessary for it to be in a replication environment

-- such as in a DEV or QA environment.

-- The key to this is setting the parameters to ignore the Distributor

-- because the Distributor is not setup on this server

exec sp_replicationdboption 'SafariStaging_Baseline','publish',true, @ignore_distributor = 1

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

EXEC sp_replicationdboption 'SafariStaging_Baseline','publish',false, @ignore_distributor = 1

-- run to verify no pending transactions need replicated

dbcc opentran

-- truncate the txn log

backup log SafariStaging_Baseline with truncate_only

-- shrink the log if necessary

use <Database you are shrinking the log on>

GO

dbcc shrinkfile('Log file logical name', #) -- # size in MB you want the log to be sized to

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please note: 4 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