Let me start this off by saying that I chose the title of this blog post very carefully. You’ll note that it says “fragmentation may cause corruption” not that it will cause corruption, so before you go saying “Denny says that if you have any fragmentation of the database it’ll become corrupt” be sure to read the entire blog post.
I recently ran across a very interesting Microsoft knowledge base article titled “A heavily fragmented file in an NTFS volume may not grow beyond a certain size“. The basic jist of this article is that if you have files, specifically large files (like oh say database files) which are VERY heavily fragmented you may get error messages back from the OS saying that there was a problem. If you are running a Vista or newer Kernel (Windows Server 2008 and newer) you’ll see Error 665 (The requested operation could not be completed due to a file system limitation). If you are running a kernel older than Vista (Windows 2003 R2 or older) you will see Error 1450 (insufficient system resources exist to complete the requested service).
The reason for this is that when a file is fragmented the NTFS file system needs more space than expected to save the location of the file allocations. Information about the location of the allocations are stored within file records by the file system. When there are multiple file records for a file the file system uses something called an ATTRIBUTE_LIST to store information about the file records. The problem comes into play when we run out of ATTRIBUTE_LIST structures for the specific file.
There is no way to figure out if (or when) you are going to hit the error message. The reason is that there is no way to figure this out is that it would require knowing the sizes of the fragments and knowing how these fragments are begin described within the file tables and the ATTRIBUTE_LISTs.
There doesn’t appear to be any real workaround for sparse files (which SQL Server users for DBCC and snapshots). So if you start seeing these error messages your best bet will probably be to defrag the disk so that when SQL Server creates a sparse file the file isn’t fragmented. Backing up the database and restoring it may help as well as the disk would no longer be fragmented. In either case a call to CSS would probably be in order. In any case this is just another good reason to pre-allocate your files so that all your free space on the disk is at the end, and not scattered through out the disk.
Denny
