May 23, 2008 at 4:32 pm
Regular database files (mdfs, ndfs, ldfs) are never sparse. Period.
The only files that SQL Server creates that are sparse are those used by database snapshots - either explicitly created or implicitly created by one of the DBCC check commands. Looks like your backup software is picking up a database snapshot in some way.
I only cursorily read through the whole thread - let me know if I missed a question.
Oh yes, instant file initialization doesn't use sparse files at all - it simply skips doing the zero-initialization that is usually done when a data file is created or grown. Note the log files are *always* zero-initialized.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 30, 2008 at 3:17 pm
Paul:
I much appreciate you input to this thread as you experience is very widely recognized. However, I am definitely not imagining it, I have seen an mdf tagged as sparse. Unfortunately, the client has blown away the database now as it was only a dev one. I have no idea how it became sparse and neither had the client.
The only things of note was that it was on an HP SAN and that it was a DB for Microsoft Dynamics Ax.
September 24, 2009 at 1:49 pm
I know this post is a bit old but we are experiencing a problem with our main database files being marked as "sparse". We have been working with Microsoft and I wanted to post some of our findings.
Our problem has manifested in 2 ways:
1. Our DR software Double-Take/GeoCluster has a problem replicating "sparse" files to our DR server
2. We are receiving messages in our SQL log when running some reindexing scripts:
"Timeout occurred while waiting for latch: class 'ACCESS_METHODS_BULK_ALLOC', id 000000010D94B700, type 2, Task 0x0000000000C3EC58 : 12, waittime 600, flags 0x1a, owning task 0x0000000005F93048. Continuing to wait."
When running select * from sys.master_files, the is_sparse column for our database files is set to 1 (should be 0).
After analyzing many files collected from our system, Microsoft has confirmed that the timeouts are caused by the files being marked as "sparse". Here's a link they sent to us describing their internal discussions on "sparse" files. http://blogs.msdn.com/psssql/archive/2009/09/23/did-your-backup-program-utility-leave-your-sql-server-running-in-an-squirrely-scenario.aspx
The "sparse" flag can also be set on a file using FSUTIL from the command line but it is not as easy to "un-sparse" a file. http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/fsutil_sparse.mspx?mfr=true
To "un-sparse" a file, you can:
1. detach the database
2. make a physical copy of the database (must copy not rename)
** according to MS, copy does not keep the sparse attribute
3. remove or rename the old file
4. rename the copy using the original name
5. attach the new copy
You can test this by creating a test database, using the FSUTIL utility to set the attribute, confirm the sparse column is '1' using select * from sys.master_files then follow the instructions above to reset it.
I hope this helps someone because it seems very hard to find anyone with a working knowledge of this problem/bug (inside or outside microsoft).
October 23, 2009 at 2:04 am
I've seen SQL with sparse files, although how they became sparse I have no idea. The Customer was using a development database and I think he just blew it away and recreated it.
According to Mr Randall, they shouldn't be sparse and if you look at his profile, he ought to know.
What's you problem with Double-Take?
October 23, 2009 at 7:29 am
I do not question Mr. Randall's credentials but our problem is real and confirmed by Microsoft engineers. Here's a MS blog discussing the problem:
This post does need updating because our problem still remains. The copy described above worked in a test environment when a file was "sparsed" from the OS but ours do not show as "sparse" from the OS, only via SQL 2005. We are currently working on a plan to recreate the data files and export all data, SP's, functions, views, etc. This has proven problematic because we have both primary and secondary data files. At this point, we have not successfully exported the files correctly. The export dumps everything in 1 file while leaving the others 99% empty. We have tried restoring the files from backup and the "sparse" flag remains. We have tried using 'copy database' and the flag remains.
We use Double-Take for many servers and it is generally a good, stable product. This system is actually a cluster using the Double Take-GeoCluster product. We believe it was somehow related to the "sparse" problem but that is only a theory at this point.
I will try to keep this thread updated as we have new developments.
October 23, 2009 at 7:52 am
How about mgrating data? Create an empty DB, then use the migration wizard to migrate the lot from the old DB to the new DB?
October 23, 2009 at 8:29 am
I'm not sure if I am familiar with the migration wizard. Can you elaborate? Currently, I am scripting out the database and creating a new db with the script. I am then using Tasks-> Export Data from Mgmt Studio to export the tables then the views into the new db. I have also scripted out the SP's and functions which I then use to recreate in the new database. The main problem is that the database has a *.MDF, 3 *.NDF's and the log. The export is dumping everything in the MDF and leaving the NDFs empty. The database is about 250GB so everything takes a while when testing. Any suggestions are appreciated.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply