Share via

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

  • Article

See update: https://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

Comments

  • Anonymous
    November 17, 2009
    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

  • Anonymous
    May 10, 2010
    [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.

  • Anonymous
    November 24, 2010
    (This comment has been deleted per user request)