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


Distribution cleanup job failing - transactional replication


Distribution cleanup job failing - transactional replication

Author
Message
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6682 Visits: 7394
No such luck in this case Sad. 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" ;-)
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
Sad Sorry!

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3756 Visits: 2904
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6682 Visits: 7394
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" ;-)
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6682 Visits: 7394
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" ;-)
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
I think I can... I think I can... :-)

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6682 Visits: 7394
Success! Cool

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 :-P 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" ;-)
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
MyDoggieJessie (2/21/2012)
Success! Cool

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 :-P 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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6682 Visits: 7394
Yep that was exactly it Smile

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" ;-)
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
At least you found the source of the problem and were able to rectify it! Happy Fat Tuesday!

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
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