Did your backup program/utility leave your SQL Server running in an squirrely scenario? (Version 2)

[mssql-support]

First published on MSDN on Feb 21, 2011

My colleges asked me if 'squirrely' is a technical term and for this post the answer is yes.  CSS is not going to deny support to customers but SQL Server was not tested in this scenario so you may have chased yourself up a tree, hence I use the term squirrely.

SQL Server 2005 introduced snapshot databases and modified DBCC to create secondary snapshot streams for online DBCC operations.   The online DBCC creates a secondary stream of the database files that is SPARSE.  CSS has found that if a 3rd party backups and utilities or NT Backup is used against the database files the SPARSE setting may get incorrectly, propagated to the parent stream.   In the case of DBCC this is the original database files(s).

Repro

• Create Database MyDB
• DBCC checkdb(MyDb)   -- Completed without error
• Utility like NT Backup touches the database files  (Incorrectly makes SPARSE sticky on on the main file stream)

The next time the database is opened (recovery, restart, etc…) the sparse attribute it detected by SQL Server and the status shown in sys.database_files is updated to indicate is_sparse = TRUE .

use MyDB
select is_sparse, * from sys.database_files

If the is_sparse is not equal to 0 it indicates that SQL Server is treating the file as a sparse file.  This causes alternate, inappropriate, code lines to be used in areas such as auto grow. Future releases of SQL Server may contain additional messages in the error log when this situation is encountered.

The case I am working on today shows a primary database file (not a snapshot or secondary sparse stream) using the Windows API DeviceIoControl to zero the contents during an auto grow.   This is not the normal code line as the DeviceIoControl to zero the contents is only used for sparse files.   The MSDN documentation associated with this ( http://msdn.microsoft.com/en-us/library/aa364597(VS.85).aspx - FSCTL_SET_ZERO_DATA) indicates that the processing may deallocate other locations in the file while handling the request.

"If the file is sparse or compressed, the NTFS file system may deallocate disk space in the file. This sets the range of bytes to zeroes (0) without extending the file size."

SQL Server does not support backup and restore of snapshot/sparse files so when the primary database is treated as sparse the SQL Server is running in untested situations and the support boundaries blur.

What Should I Do?

Run a query against each of your databases and look for the is_sparse <> 0.  For any files that are not snapshot databases you need to copy the data out of the file, drop the file, create a new file and load the data.  I.E.: Transfer your data to a new file.

Then determine what utility is touching the file and propagating the sparse attribute and configure it to avoid the SQL Server files.

[Sep 27 Update]

One of the things I love about the blog interactions.  I have had a great list of questions related to this blog already so I would like to add a Q/A section.

Are you saying that if any Snapshots exist when using these third party tools that it could cause the is_sparse flag to be turned on?
[[rdorr]] No it is a Windows based issue.  When DBCC runs it creates a sparse stream.   SQL destroys the stream at the end of DBCC but the sparse bit becomes ‘sticky’ and will get upgraded to the primary stream.

Is the problem because the snapshots exist _ while _ those backups are taking place?
[[rdorr]] No, the DBCC just needs to be executed so at some time the secondary steam existed.   It does not apply to snapshot databases (Create database for snapshot)

In the case of the DBCC activity – is the problem only going to happen if the DBCC is taking place while the third party utility kicks in?
[[rdorr]] No it can occur after DBCC has completed successfully.

You talk about copying the contents out of one file and putting them into another file -- i'm assuming you are meaning a new FileGroup and moving the objects into a new filegroup to transfer all the data to the new filegroup.
[[rdorr]] Yes a new file in the same file group or a new file group.

The question I have there is that if it is the filegroup that contains the system objects for the database, how do we get that information over into the new file?
[[rdorr]] New database you can't move system objects. Is it not possible to change the SPARSE attribute on the file system back so that SQL treats the file correctly?
[[rdorr]] Not cleanly.  If you have such a situation the file system is tracking it as a sparse file and we have all kinds of unknowns.

This sounds to me like a very dangerous situation that could easily result in data loss.
[[rdorr]] Should not result in data loss.  NTFS tracks the correct allocations.  The problem is that sparse files are limited in size so you are running along and you can’t grow anymore or a backup may not restore.  This is where you get into possible data loss.
!!! NOTE !!! This brings up a good point.  After the problem is corrected you should take a full backup.

Is the problem only encountered during growths?   if so, should we disable growth until we can reset the file attribute or move the contents somehow to another file(group)?
[[rdorr]] Grow shows the behavior but the file is already getting tracked as sparse.

Continued work on this has revealed SQL 2005, SQL 2008 and Denali differences.    Windows has published various KB articles on how to change a file at the NTFS level from sparse to non-sparse.   The basics are a file copy.   Copy expands the destination file (does not retain the sparse attribute).

FIX STEPS for SQL 2008

!!! WARNING !!!  Take appropriate backups and precautions when attempting these corrections.  A failed step in this process could render the database unusable and you may lose data.

For SQL Sever 2008 you must detach the database, copy the files and attach the database to correct the NTFS and SQL Server state from IsSparse = 1 to IsSparse = 0

sp_detach_db 'dbISSPARSE'

ren dbISSPARSE.mdf dbISSPARSE.mdf.orig
copy dbISSPARSE.mdf.orig dbISSPARSE.mdf
fsutil sparse queryflag dbISSPARSE.mdf

sp_attach_db 'dbISSPARSE', 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAdbISSPARSE.mdf'
select is_sparse, * from sys.master_files where name = 'dbISSPARSE'     (!!! IS SPARSE = 0, CORRECT AT NTFS AND SQL Level !!!)

FIX STEPS for SQL "DENALI"

!!! WARNING !!!  Take appropriate backups and precautions when attempting these corrections.  A failed step in this process could render the database unusable and you may lose data.

• Close the database files: ALTER DATABASE MyDB OFFLINE
• Validate the sparse setting using an elevated admin cmd prompt  Examples shown below.

C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATA>fsutil sparse queryflag dbTest.mdf
This file is NOT set as sparse
C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATA>fsutil sparse queryflag dbTest.mdf
This file is set as sparse

Note: File that are 'set as sparse' that are NOT databases created using 'CREATE DATABASE FOR SNAPSHOT' should be corrected.

• Rename the database file: ren MyDB.mdf  MyDBSparse.mdf
• Copy the data into a new file.  (Note:  Do not copy with overwrite as copy will retain the sparse attribute): copy MyDBSparse.mdf MyDb.mdf
• Use FSUtil to validate all files for the database are no longer sparse.
• Bring the database back online: ALTER DATABASE MyDB ONLINE
• Validate that is_sparse returns the expected outcome: use MyDB; select is_sparse, * from sys.database_files

BACKUP

Take a full SQL Server backup of the database and make sure other backups are no longer causing the issue to re-occur.

FIX STEPS for SQL 2005

!!! WARNING !!!  Take appropriate backups and precautions when attempting these corrections.  A failed step in this process could render the database unusable and you may lose data.

Use the database copy wizard to copy the data into a new database or upgrade to SQL Server 2008 and use the steps outlined above.   I tested the sp_detach_db on SQL 2005 with an sp_attach_db on SQL 2008 and the is_sparse status can be restored to the expected running state

Bob Dorr - Principal SQL Server Escalation Engineer