Data Backup and Recovery Discussions

Highlighted

[SQL SMO Error] SQLServer DBCC CHECKDB failed

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!

1 REPLY 1
Highlighted

Re: [SQL SMO Error] SQLServer DBCC CHECKDB failed

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

NetApp Insights To Action
All Community Forums