2

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.

Improve this question
0

1 Answer 1

Reset to default
2

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.

Take a look here for details about changing the filesystem settings, which may be an option.

Improve this answer
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 Commented Aug 31, 2021 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.