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 ««123»»

Distribution cleanup job failing - transactional replication Expand / Collapse
Author
Message
Posted Tuesday, February 21, 2012 11:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
No such luck in this case . With the checkdb not being able to create the snapshot sounds to me like a reboot is needed. Having to go this route however, makes me a little nervous as it the db might not come back up properly (as a checkdb will automatically run when the mssql service gets restarted). If that happens, we are forced to rebuild all the subscriptions and possibly publications (which would take use several hours)

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1255490
Posted Tuesday, February 21, 2012 11:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
Sorry!

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1255494
Posted Tuesday, February 21, 2012 11:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:17 AM
Points: 2,917, Visits: 2,537
What size is your distribution database? How many packages and subscribers do you have? How long do you keep your history?

I found that in larger replication systems that the clean-up proces will actually time out because of the amount of data that is there and what the cleanup process is doing.


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #1255500
Posted Tuesday, February 21, 2012 12:57 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
1. The data file is about 80GB, logfile is about 25GB, the actual usage of that portion varies, in fact most of the time the data file is only 15-20% full

2. There are 4 main publications (for transactional replication) and 4 minor publications (for snapshot replication) - there is a subscriber for each of the publications so that's 8 subscriptions.

3. History is kept for 72 hours...not that it matters though because the distribution cleanup agent stopped working at the same time as all this began



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1255537
Posted Tuesday, February 21, 2012 1:22 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
Rebooting of the server has allowed a checkdb to at least start, currently it's running (very slowly i.e it's been at 1% for the past 9 minutes) but it is running:
Recovery of database 'distribution' (25) is 1% complete (approximately 54242 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1255548
Posted Tuesday, February 21, 2012 1:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
I think I can... I think I can...

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1255552
Posted Tuesday, February 21, 2012 2:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
Success!

To tell you the truth I am blown away that there wasn't more information on this on BOL or MS forums...almost makes me feel special that I AM THE ONLY ONE who's ever ran into this...


1421813 transactions rolled forward in database 'distribution' (25). This is an informational message only. No user action is required.

0 transactions rolled back in database 'distribution' (25). This is an informational message only. No user action is required.

Recovery is writing a checkpoint in database 'distribution' (25). This is an informational message only. No user action is required.

CHECKDB for database 'distribution' finished without errors on 2012-02-15 21:08:54.840 (local time). This is an informational message only; no user action is required


A strange observation: There errors arose the entire time the distribution DB was recovering:
Error: 18456, Severity: 14, State: 38.
Login failed for user MYCOMPANY\ThatUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

After the Recvoery completed, the errors stopped. I've verified that user has in no way anything that ties it to the distribution database???


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1255571
Posted Tuesday, February 21, 2012 2:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
MyDoggieJessie (2/21/2012)
Success!

To tell you the truth I am blown away that there wasn't more information on this on BOL or MS forums...almost makes me feel special that I AM THE ONLY ONE who's ever ran into this...


1421813 transactions rolled forward in database 'distribution' (25). This is an informational message only. No user action is required.

0 transactions rolled back in database 'distribution' (25). This is an informational message only. No user action is required.

Recovery is writing a checkpoint in database 'distribution' (25). This is an informational message only. No user action is required.

CHECKDB for database 'distribution' finished without errors on 2012-02-15 21:08:54.840 (local time). This is an informational message only; no user action is required


A strange observation: There errors arose the entire time the distribution DB was recovering:
Error: 18456, Severity: 14, State: 38.
Login failed for user MYCOMPANY\ThatUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

After the Recvoery completed, the errors stopped. I've verified that user has in no way anything that ties it to the distribution database???


Could have been a user specified for creds to the subscriber?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1255574
Posted Tuesday, February 21, 2012 2:31 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
Yep that was exactly it :)

It slipped my mind (until you said that) that I set up the security using the default "Impersonate agent process account (Windows Authentication)" (I know, this isn't reccomended)



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1255597
Posted Tuesday, February 21, 2012 2:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
At least you found the source of the problem and were able to rectify it! Happy Fat Tuesday!

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1255610
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse