CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Did your backup program/utility leave your SQL Server running in an squirrely scenario?

Did your backup program/utility leave your SQL Server running in an squirrely scenario?

  • Comments 3

See update: http://blogs.msdn.com/b/psssql/archive/2011/02/21/did-your-backup-program-utility-leave-your-sql-server-running-in-an-squirrely-scenario-version-2.aspx

Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
  • 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

  • [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.

  • 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.

Page 1 of 1 (3 items)