Quick one today.

Occasionally, in my development environment, when I run CHECKDB, I run into this error:

ERROR: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.

The error message is pretty vague and does not give me any leads to troubleshoot the issue. But upon some head-scratching, I realized this.

When we run CHECKDB, Sql Server internally runs CHECKCATALOG, CHECKTABLE, CHECKALLOC on all the objects. This is a pretty resource intensive operation, so it needs a lot of resources. CPU, Memory and I/O. There is one more thing, Disk. Yes it needs some disk space to run its calculations.

From what I read from Paul Randall, when you run CHECKDB, Sql Server creates a database snapshot of the database and runs checks on the snapshot. This snapshot needs some disk space. If you have a VLDB with a lot of traffic coming in to make changes, then this Snapshot takes up more space. How big does this snapshot get? I can’t tell, but if there are many changes (UPDATEs/INSERTs) happening on the tables, then snapshot database gets bigger due to ‘copy-on-write’.

Just make sure you have enough disk space on the drive that have the original database’s data files.

Hope this helps,
_Sqltimes