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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27128 Visits: 7489
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; Theyll drag you down to their level and beat you with experience

Jared Karney
Jared Karney
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36039 Visits: 3703
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
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7407 Visits: 2913
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27128 Visits: 7489
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; Theyll drag you down to their level and beat you with experience

MyDoggieJessie
MyDoggieJessie
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27128 Visits: 7489
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; Theyll drag you down to their level and beat you with experience

Jared Karney
Jared Karney
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36039 Visits: 3703
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27128 Visits: 7489
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; Theyll drag you down to their level and beat you with experience

Jared Karney
Jared Karney
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36039 Visits: 3703
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27128 Visits: 7489
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; Theyll drag you down to their level and beat you with experience

Jared Karney
Jared Karney
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36039 Visits: 3703
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