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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12214 Visits: 7444
Since yesterday afternoon I've been seeing this in the Agent History:
Executed as user: Domain\User. Time-out occurred while waiting for buffer latch type 2 for page (1:5019346), database ID 25.


After checking the SQL Error Logs I find:

A time-out occurred while waiting for buffer latch -- type 4, bp 0000000089FC6C80, page 1:5019346, stat 0x4c0010b, database id: 25, allocation unit Id: 72057594054967296, task 0x0000000008027048 : 0, waittime 300, flags 0x3a, owning task 0x0000000008008988. Not continuing to wait.
Error: 845, Severity: 17, State: 1.

A time-out occurred while waiting for buffer latch -- type 3, bp 0000000089FC6C80, page 1:5019346, stat 0x4c0010b, database id: 25, allocation unit Id: 72057594054967296, task 0x000000000541C508 : 0, waittime 300, flags 0x3a, owning task 0x0000000008008988. Not continuing to wait.

A time-out occurred while waiting for buffer latch -- type 2, bp 0000000089FC6C80, page 1:5019346, stat 0x4c0010b, database id: 25, allocation unit Id: 72057594054967296, task 0x0000000005409048 : 0, waittime 300, flags 0x1a, owning task 0x0000000008008988. Not continuing to wait.

Error: 14151, Severity: 18, State: 1.


Overall the server appears to be running okay, replication is current...but this job won't run. We had issues with two of our production publications over the weekend (the ohter two didn't have issues at all) so I redid those and everything went ok. This error doesn't exactly give me a warm, fuzzy, feeling...

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12214 Visits: 7444
To update this toublesome distribution DB, the differential backup that ran this morning also failed:

Microsoft(R) Server Maintenance Utility (Unicode) Version 10.0.4064
Report was generated on "MYSERVER".
Maintenance Plan: MAINT - Distribution
Duration: 00:06:07
Status: Warning: One or more tasks failed.
Details:
Back Up Database Task (MYSERVER)
Backup Database on Local server connection
Databases: distribution
Type: Differential
Append existing
Task start: 2012-02-20T03:45:00.
Task end: 2012-02-20T03:51:08.
FailedSad-1073548784) Executing the query "BACKUP DATABASE [distribution] TO DISK = N'\\\\MYS..." failed with the following error: "Time-out occurred while waiting for buffer latch type 3 for page (1:5019346), database ID 25.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:EXECUTE master.dbo.xp_create_subdir N''\\NETWORKLOCATION\MYSERVER\distribution''

GO
BACKUP DATABASE [distribution] TO DISK = N''\\NETWORKLOCATION\MYSERVER\distribution\distribution_backup_2012_02_20_034500_7672884.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''distribution_backup_2012_02_20_034500_7672884'', SKIP, REWIND, NOUNLOAD, NO_COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''distribution'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''distribution'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''distribution'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''\\NETWORKLOCATION\MYSERVER\distribution\distribution_backup_2012_02_20_034500_7672884.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Divine Flame
Divine Flame
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4395 Visits: 2816
Have you run DBCC CHECKDB on it?


Sujeet Singh
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12214 Visits: 7444
I had run it a couple days ago and it had completed ok...
"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."

But not since all these issues have arisen. I am trying to run it now but it is being blocked by two system processes:
1. DB: distribution, Command: GHOST CLEANUP

I've attached a snap of the what I'm seeing on my end...

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Attachments
distribution.png (24 views, 47.00 KB)
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12214 Visits: 7444
After attempting to run the CHECKDB I got this:

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 3 for page (1:5019346), database ID 25.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Divine Flame
Divine Flame
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4395 Visits: 2816
I am not sure if your problem has been resolved. However, did you try o run the DBCC CHECKDB in offline mode? OR you may restore the backup of this database to some other test server & run DBCC CHECKDB on it there.


Sujeet Singh
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12214 Visits: 7444
I see where you're going and yes I am able to back up the most recent backup of the distribution database to another server - CHECKDB returns no errors, however, this is the last successful backup I've been able to take of the DB (2/17/2102), every attempt to backup the DB since the 17th results in the errors above.

Any other ideas?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13544 Visits: 3697
Have you tried deleting and rebuilding the subscription(s)?(obviously a problem if there are too many) Publication(s)? Something just seems kooky here... Have you tried stopping the distribution agents and restarting them? Log readers?

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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12214 Visits: 7444
"Thought" about it dropping/recreating the subscriptions...but having the guts to actually do it during the day - No.

This is a production OLTP system) so basically praying to make it to the weekend...

I will try stopping one of the agents and restarting it...never know...

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13544 Visits: 3697
Sometimes when replication is acting "flakey," I will open up replication monitor and find the subscription and simply stop it, then start it. It actually helps in strange cases like these.

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