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

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.