This Knowledgebase article provides specific advice on configuring SQL Server 2005 Database Consistency Checking (DBCC) tasks in order to avoid ApplyDiskFull events during normal operations.
Starting with V4.6.1, Neverfail Heartbeat will replicate the Alternate Data Streams (ADS) linked to each file present within the protected set.
During normal operations, Neverfail Heartbeat will replicate sparse files within the protected set in a non-sparse format on the passive server. Each time Heartbeat encounters a sparse file, it will log an event highlighting its presence, and replicate the file in a non-sparse format.
By design, SQL Server 2005 DBCC tasks (CheckDB, CheckCatalog, CheckAlloc, CheckTable, CheckFileGroup) use read-only internal database snapshots to perform integrity checks and to avoid applying exclusive locks to the database under maintenance. These internal database snapshots are actually sparse files created as Alternate Data Streams for each database checked. For more information, please see the following MSDN articles:
http://msdn2.microsoft.com/en-us/library/ms188796.aspx (DBCC (Transact-SQL))
http://msdn2.microsoft.com/en-us/library/ms176064.aspx (DBCC CHECKDB (Transact -SQL))
http://msdn2.microsoft.com/en-us/library/ms175823.aspx (Understanding Sparse File Sizes in Database Snapshots).
When DBCC tasks are executed against protected SQL Server databases, the following event may be recorded in the Windows Application Event log on the passive server, resulting in replication being automatically stopped:
Neverfail Heartbeat Yellow Alert: Failed to update the Passive because the disk/Quota is full. This happened at <date and time> on the Secondary <server name> server while Passive. Further information if available: [N27]Failed to write information for the file: <affected database file>.mdf:MSSQL_DBCCxx. Please see KB 248.
The Neverfail log files (nflog.txt and nflog.txt.*) will contain the following type of events:
INFOxxxxxx[Events](Events) - Multicasting ApplyDiskFullEvent
ERRORxxxxx[Events](com.neverfail.unsupportedfeatures.UnsupportedFeature) - The following file system object : <databasename>.mdf:MSSQL_DBCCxx has an unsupported file system feature : Sparse Files associated with it.
INFOxxxxxxx[Events](Events) - Multicasting UnsupportedFeatureUserEvent
INFOxxxxxx[Interceptor$ExceptionsFetcher](com.neverfail.interceptor.Interceptor) - INTERCEPTOR** RECEIVED exception com.neverfail.interceptor.FeatureDetectedException(truncated = false, type = 6, data = 0, fileName = <databasename>.mdf:MSSQL_DBCCxx)
INFOxxxxxx[AfterLogDispatcher](com.neverfail.newapply.JavaDataSet) - OutOfDiskSpace | NativeInfoString: FileID: 01602183, SeqNo: yyyyyyyy, MethodInfoText: UpdateFile_niSetEof, DiskIOInfoText: Entered nfSetEndOfFile [hFileHandle: 3656, qNewFileLength: yyyyyyyyyyy. CurrentFileLength: 00] | NativeErrorString: FileID: yyyyyyyyy, SeqNo: yyyyyyyy, MethodErrorText: UpdateFile_niSetEof --> Could Not Set End Of File, DiskIOErrorText: nfSetEndOfFile - Cannot Set EOF. FileHandle 3656. Position To Set: 192-1462763520, CurrentFileLength: 00, WinErrorCode: 112, WinErrorText: There is not enough space on the disk. | ObjectName: <databasename>:MSSQL_DBCCxx
To restart replication on the server pair, manual intervention is required.
During the execution of the DBCC tasks mentioned above, Heartbeat will try to replicate the temporary ADS sparse file along with the protected database. As the sparse ADS file is a snapshot of the database, it actually has the same size of the database during maintenance. Since Heartbeat replicates the sparse file in non-sparse format, the available Disk Space on the passive server might not be sufficient to accommodate the temporary snapshot created in non-sparse format. When trying to create it, the Apply component will receive an OutOfDiskSpace error from the file system on the passive server.
On the active server, the temporary snapshot is saved as a sparse file and therefore requires less disk space than it would in a non-sparse format.
The location of the internal database snapshot created by the DBCC tasks cannot be changed and will always be saved as ADS on the database during maintenance. Heartbeat cannot be configured to not replicate an ADS linked to a file within the protected set.
This behavior might be seen in two cases:
The DBCC tasks are executed on larger databases and the available disk space on the passive server cannot accommodate an additional copy of the affected database file.
The DBCC tasks start at the same time on several databases– the passive server cannot accommodate all the copies of the databases checked at the same time.
If you suspect that you have encountered this situation, please contact Neverfail Support for diagnosing and verification that this is in fact the situation.
In order to avoid the ApplyDiskFull events and prevent replication from stopping, Neverfail recommends the following workarounds, for each case presented above:
Modify the DBCC job to run on a manually created database snapshot, outside of the protected set.
This workaround is to be followed in cases where a larger database file exists and the passive server does not have enough disk space to accommodate a second copy of it. The procedure below requires alteration of the current SQL Server Agent jobs – please backup the current configuration before proceeding.
Create a temporary database file as a snapshot of the current database, specifying its location outside of the Neverfail protected file set.
Execute the DBCC tasks on the manually created database snapshot.
Once complete, drop the temporary created snapshot.
Reschedule the DBCC jobs not to run on all databases at once.
This workaround is to be followed in cases where DBCC tasks start at the same time on several databases. This procedure assumes the passive server has enough free space to accommodate a new copy any given database but not enough to accommodate all of them at once.
Reschedule the DBCC tasks to start on one or more databases at the same ensuring the passive server has enough free space to accommodate the temporary snapshots in non-sparse format (full database sizes).
Neverfail recommends using the entire out-of-office period for regular maintenance tasks on the protected application. This will reduce the backlog created, which Heartbeat will have to replicate.
CheckDB example default script:
The following example shows a modified CheckDB script that creates a snapshot for the DBX database using a snapshot file held in a temporary location ('D:\TEMP_DB\' in this example) that is not replicated. The snapshot file is a sparse file and typically is not very large (it holds what is called 'copy on write' database content) and so does not create a large overhead in terms of disk space or time to run the CHECKDB.
CheckDB proposed modified script:
CREATE DATABASE TEMP_DBX_SNAP ON
(Name = TEMP_DBX, FILENAME = 'D:\TEMP_DB\TEMP_DBX.snap')
AS SNAPSHOT OF DBX;
DBCC CHECKDB (TEMP_ DBX)
DROP DATABASE TEMP_DBX