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

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)