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 Sunday, February 19, 2012 10:49 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,025, Visits: 7,172
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"
Post #1254536
Posted Monday, February 20, 2012 8:02 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,025, Visits: 7,172
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.
Failed-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"
Post #1254746
Posted Monday, February 20, 2012 8:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 1,344, Visits: 2,646
Have you run DBCC CHECKDB on it?


Sujeet Singh
Post #1254762
Posted Monday, February 20, 2012 8:34 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,025, Visits: 7,172
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"


  Post Attachments 
distribution.png (4 views, 47.48 KB)
Post #1254772
Posted Monday, February 20, 2012 8:44 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,025, Visits: 7,172
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"
Post #1254777
Posted Tuesday, February 21, 2012 2:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 1,344, Visits: 2,646
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
Post #1255075
Posted Tuesday, February 21, 2012 9:45 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,025, Visits: 7,172
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"
Post #1255395
Posted Tuesday, February 21, 2012 9:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1255404
Posted Tuesday, February 21, 2012 10:42 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,025, Visits: 7,172
"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"
Post #1255448
Posted Tuesday, February 21, 2012 10:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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
SQL Know-It-All

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

Add to briefcase 123»»»

Permissions Expand / Collapse