[SQL SMO Error] SQLServer DBCC CHECKDB failed

[x-gugi]

Hi,

we're running on SQL Server 2012 SP4 on Windows Server 2012 R2 with SnapManager for SQL Server version 7.2.3.2841 and

our database backup / verification fails every time for one Database with about 2.5 TB: 

The following lines are logged in the log file:

Attaching database [MyDatabase__Clone]...

CREATE DATABASE [MyDatabase__Clone] ON PRIMARY 

(NAME = 'MyDatabase', FILENAME = 'M:\MP\MPDisk001\MSSQL11.MSSQLSERVER\MSSQL\Data\MyDatabase_Data.mdf'), 

(NAME = 'MyDatabase_Data_2', FILENAME = 'M:\MP\MPDisk001\MSSQL11.MSSQLSERVER\MSSQL\Data\MyDatabase_Data_2.ndf')

LOG ON 

(NAME = 'MyDatabase_Log', FILENAME = 'M:\MP\MPDisk002\MSSQL11.MSSQLSERVER\MSSQL\Data\MyDatabase_Log.ldf') FOR ATTACH

Database attached.

DBCC CHECKDB (N'MyDatabase__Clone') WITH NO_INFOMSGS, PHYSICAL_ONLY

6 hours later...

[SQL SMO Error]: An exception occurred while executing a Transact-SQL statement or batch.

Timeout abgelaufen. Das Zeitlimit wurde vor dem Beenden des Vorgangs überschritten oder der Server reagiert nicht.

Der Wartevorgang wurde abgebrochen

WARNING: SQLServer DBCC CHECKDB failed.

Maybe the following lines in debug log can help:

I enabled debug logging and found the following lines before DBCC starts 

CSnapMgrSqlBase::AttachDatabasesByTSQL_SMO() - Finished hr = 0x0

CSnapMgrSqlBase::AlterDatabaseAutoshrinkOFF_SMO(MyDatabase__Clone) start

CSnapMgrSqlBase::AlterDatabaseAutoshrinkOFF_SMO() return hr = 0x0

CSnapMgrSqlBase::DmoAttachDatabase_SMO() - Finished hr = 0x0

CNtapSqlVerify::AttachDatabaseThenDBCC4 - Calling RunSQLCmdDBCC2

CSnapMgrSqlBase::RunSQLCmdDBCC2_SMO - Started

m_dwSmoStatementTimeout: 10800

I guess that's 180min / 3hours which doesn't describe the error after 6 hours, but since the original error message says [SQL SMO ERROR] it may be related.

Smaller databases within the same instance / on the same LUNs run without any problems (but dbcc finishes in less than 3 hours). Datafiles and Logfiles are located on different volumes and the source database instance is used for verification.

We already increased the RemoteBackupThreadTimeout without any success (https://community.netapp.com/t5/Data-Backup-and-Recovery-Discussions/SMSQL-remote-verification-timeout/td-p/42309)

Any ideas? Does someone know a way to increase the SmoStatementTimeout? I couldn't find an option in GUI / registry / config files so far.

Thanks in advance!

[matte]

Hi

Why did you change the  RemoteBackupTreadTimeout if what is failing (timing out) is the verification and not the remote backup?

So please Perform the following procedure:

1- Check if manual DBCC Check can be performed successfully and how long it takes. (the steps to do that are the same you have in   the logs above..)

2- Be sure that you are using latest 7.2.2p1 SMSQL version

3- Make a copy of windows registry.

4- Navigate to: HKEY_LOCAL_MACHINE/SOFTWARE/Network Appliance/SnapManager for SQL Server/Server/

5- Create a DWORD named SmoStatementTimeout Change the value in decimal to relevant timeout (in seconds)

6- Restart SMSQL service.

let us know