Comments (3)

  1. Piotr Rodak says:

    Thanks for this post Bob, it is quite interesting. There is some more info about sparse files here: http://www.flexhex.com/docs/articles/sparse-files.phtml that confirms that the only way to ‘unset’ the sparse flag is to copy the file (creating it from scratch basically).

    This raises my question: how is it possible that sparse bit becomes ‘sticky’? When a snapshot is created, is a new sparse file created explicitly or there is some solution involving duplicating of file handles? Isn’t it in the end a bug in the SQL Server?

    And – why copying of data file doesn’t work in SQL Server 2005?

    Regards

    Piotr

  2. psssql says:

    [RDORR – MAY 10 2010]

    Got a question on this today and I had to go back and test.  The difference between SQL 2005 and 2008 is the is_sparse metadata state.  On SQL 2005 the SQL metadata does not disable the is_sparse once the issue is corrected where SQL 2008 will return the metadata to match the NTFS settings.

    So you can copy the file to a new file from an OFFLINE SQL Server 2005 and the FSUTIL shows no longer sparse but the query agianst sys.database_files will return the is_sparse = 1 setting and SQL Server may take the sparse code paths incorrectly.

  3. jorgepc says:

    After following these steps you may find that Windows fsutil reports the file as ‘no sparse’ while SQL Server keeps reporting the file as ‘sparse’ via sys.database_files. If this is the case, make sure you detach and re-attach the database files; this is needed because the sys.database_files information has to be updated.