During routine integrity checks one of our Virtual-DBA customers encountered an error 17053 was encountered causing the job performing these tasks to fail. Investigating the problem further this information was also included with the error in the SQL Error Log:
Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
The error is very misleading almost implying that we have violated a file size constraint of the file system. The file system on this server was NTFS and well apparently it has a huge “theoretical” file size limit of 16 Exabyte’s (1 Exabyte = 1,000,000 Terabyte). Given that the server had only 2 TB on the drive that the database files lived at it was not the issue.
The database files accumulated a total of 1.8TB nearly consuming the entire available disk space all on a single volume.
Looking into how DBCC CHECKDB works is where we can find the answer to the problem. In order for DBCC CHECKDB to keep the databases online while it performs its operations is to create a snapshot file that records all of the transactions applied against the database during this time. Furthermore this file is created in the same location as the database data files. And to make matters worse the location of where these snapshot files are created cannot be modified or configured.
Due to the size of the database the check integrity task was taking long periods of time to complete. This server also had a good amount of transactions running on it at any given time thus causing the snapshot file to grow beyond the 2TB limit of the volume.
To fix this a couple ideas come to mind. One way might be to extend the current size of the volume increasing the space available. Another option might be to create new volumes, create a new file group, span data files across the disks, move largest tables to the new file groups which will distribute across all the data files in the file group, and finally shrink the original database files.
It is important to keep those data files in check and not let them get to large. If you notice one getting to big it may be time to add on another data file to your file group. I would recommend keeping at some free and available space on your volumes for these types of overhead processes. How much space will vary depending on several factors to include database size, database use, and how long the integrity check will take to complete.
We might have come across this scenario last month, not sure. The SQL Cluster node had over 1.7 million recurring Event 17053 entries in the Application Logs along with lots of 665 events in the SQL logs. SQL DBA teams stopped and offlined the instance and on-lined it again and things were better. I work on the Wintel side and am not a SQL DBA but am trying to perform a Root Cause Analysis on our issue from back then. Question – shouldn’t a situation like this have generated an out of space alert on SQL monitoring tools? SQL DBA said he did not get any alerts.
Thanks.
Benny,
This event would have likely eaten away at the disk space as it progressed through the process. I would expect that there would be an alert generated, of course depending on how you monitor disk space, this could have happened between polling intervals.