1

We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665.

The fragmentation level of the disk drive is 0% as it is automatically defragmented weekly. The database is 24/7 OLTP database. Only way the checkdb completes is when we turn off the application. Now online solution suggests

  1. Increase the size of the disk drive, I have 800gb free space on the drive which has a database with 550 gb used space
  2. Defragment the data drive- Currently automatically defragment weekly
  3. Break the database into smaller files- Currently not possible due to downtime
  4. Consider placing the database files on ReFS volume which does not have the same ATTRIBUTE_LIST_ENTRY limits that NTFS presents. You must reformat the current NTFS volume using ReFS. – Again not possible due to downtime

Currently the checkdb checks with the option Physical only and using Ola Hallengreen script

My short term proposal is to do the checkdb into another server. But I am running out of ideas if there are other ways to cure the problem without any downtime.

2
  • error 665 being:The requested operation could not be completed due to a file system limitation? I think this is something your average DBA will not be familar with(i'm not)
    – rois
    Apr 22 at 17:06
  • Defragment with SQL Server shut down maybe? Defrag won't move open files. I don't think you have much choice here, your only other option is moving tables to a new filegroup one by one Apr 23 at 5:43
0

I don't know if you've found an answer, but I had the same problem as you, and here are some things that I did to get rid of the problem.

  • I splitted my database into different smaller filegroups : I was able to do that without a downtime, wile running an index rebuild on the new filegroup online. It did have a performance impact but at least we didn't have a down time.
  • I implemented the check table divided in 7 days as written in Paul Randall's blog (My database is more than 1.5 terabytes)
  • I implemented a data maintenance for history data and was able to shrink down the database to around 1 terabyte in size
  • I stop all non essential jobs while the CHECKTABLE runs and start them back again after.

I still get the problem sometimes, I'm considering formatting my drive with the /L option as recommended in Microsoft's article but I'm not sure yet if I'll do it.

1
  • 1
    Thanks for sharing . I was able to overcome this by deleting some indexes that was not needed that reduced the database a lot. After a while the problem started again and then i also experimented with the physical option only and so far it s running Aug 31 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.