Sql-server – SQL Merge Replication Issue – problem where backup and maintenance job which is running till morning and most of the server jobs are stuck

merge-replicationsql-server-2008

We have a merge replication environment which includes

Publisher: Server1 where only one database of 125 GB in replication and also Distributor is also configured on same server hosted in a HyperV environment.

Subscribers: We have approx 280 servers with Distributor(push subscription) configuration.

NOTE: we have a environment with lots of DML changes in day so we perform many checks on a daily basis on every server through maintenance and backup jobs which includes step1 : check integrity for complete database step2: Rebuild/Reorganise Index step3: Backup/Verify step4: Old backup jobs clean up step5: Old job history clean up

Also this job completes in 4-5 hrs on a daily basis but it varies sometimes on server to server basis.

Snapshot Agent runs at daily at 00:05 AM

PROBLEM:

Since sunday night we are having this problem where backup and
maintenance job which is running till morning and most of the server
jobs are stuck on step1 dbcc checkdb ('my database') (and Clients are
also complaining that application is crashing , can't login , very
slow)

Upon checking, my backup spid is waiting on OLEDB wait type and we do not have configured linked server connections on Publisher and other subscribers except few which are our head office servers from where we do our major imports / small updates / if we need to push out any database changes.

While checking the below query i can see the percentage is moving for my dbcc spid id but doesn't really tell me why it is stuck on this step?

select session_id
, percent_complete 
from sys.dm_exec_requests 
where percent_complete > 0

And it doesn't really matter which time I run this job or single step i.e. dbcc checkdb, it did not complete.

Also Check with our Windows team there was no update rolled out on weekend and no changes have been made on application side.

Any suggestions of what could be the problem ?

Best Answer

I came to know the Anti virus was updated last week and past weekend all servers were rebooted. To test on one server we disabled some of the install anti virus features, and my job ran successfully in a reasonable time (4-5 hrs). Hope this work globally too. Fingers crossed!

Related Solutions

Sql-server – SQL Server 2008 transaction log growing since SP3 Installed

I'd suggest the 2 are unrelated.

Has anything changed:

in the maintenance plan setting
recovery model of the database (is it now FULL where it was SIMPLE)
did you take log backups before (FULL) in the maintenance plan? Do you still do them?
did you have auto shrink in the maintenance plan? This is bad anyway
have you any open transaction that is preventing a log backup (or shrink)

Also, restore a database from pre-SP3 on another server and compare the sys.databases entries

Sql-server – Msg 2534 : DBCC CHECKDB allocation errors

If the sequence is:

DBCC CHECKDB reported errors.
REPAIR_REBUILD fixed the database, no errors reported.
DBCC CHECKDB reporting errors again.

You probably have failed or failing disks in an array or some other component of the IO subsystem is broken, or breaking. Personally, I'd want off the problem server ASAP!

Take a tail-log backup.
Restore last known good full backup and diff/log sequence to a different server.
Switch service to the second server.
Get a fresh cuppa and start diagnosis on the problem server.

Addendum: Root cause of the problem:

as it turned out, I found that as we are using Diskeeper, we are (for corporate reasons) still using an old version : 14.0.896. With this version we run into messages like :

The operating system returned error 1784(failed to retrieve text for this error. Reason: 15100) to SQL Server during a write at offset 0x000000010a4000 in file 'D:\Application-Data\MSSQL\Data\<Database>.mdf:MSSQL_DBCC17'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Server <Server>, Line 1
During redoing of a logged operation in database '<Database>', an error occurred at log record ID (2349664:1503:4). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 1823, Level 16, State 1, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Server <Server>, 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, Server <Server>, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Server <Server>, 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

Found an explanation on Paul Randal's blog here

Link to DiskKeeper Fix found here

Interesting Diskeeper white paper on database defragmentation here

Best Answer

Related Solutions

Sql-server – SQL Server 2008 transaction log growing since SP3 Installed

Sql-server – Msg 2534 : DBCC CHECKDB allocation errors

Related Question