Why does my table keep getting corrupted after removing large amounts of data?

Question

I have a database that is about 400 gigs. The majority of the space is consumed by images of utility bills stored in string form as varchar(max) in a single table. It's a poor database design that I inherited. I have already exported the utility bills as pdfs to a network folder. Now I am trying to replace the images in the database with an empty string. I have been updating in small batches of 40k to 80k records at a time. The problem is that after a few batches, the table gets corrupted. I'll get a message that the update failed. When I run CHECKDB I get

Msg 8909, Level 16, State 1, Line 1 Table error

It's happened twice so far and I have to restore the database from a backup.

I need to know why this is happening and if there is a way to avoid it. I'm doing this in a test environment and I need to get this problem figured out before I can work on the production database.

Thank you for your help.

Edit: Changed binary to string. One of the images is stored as a continuous string of 65,535 characters.

Edit: This is happening on MSSQL 2014 SP3 (Version 12.0.6024.0)

Answer 1

Did you really mean varbinary(max)? Seems strange to have non-text data in a text-type column.

Anyhow, corruptions shouldn't occur. Your first step would be to make sure that you are up to date within the version of SQL Server you are using. I.e., (service pack and) CU.

If it persists, then I'd suspect a bug in SQL Server or that a corruption already existed before your updates. I.e., do a checkdb first and see if it is clean. If it is clean and gets corrupted by an update, then you either have some storage/machine issue (less likely if it is repeatable) or a bug in SQL server.

Answer 2

As already suggested, use DBCC CHECKDB (or perhaps CHECKTABLE for a more directed check, but when corruption s suspected I'd err in the direction of checking everything and it sounds like this table is the majority of your DB anyway).

If you can restore a backup from before you started the process and check that too, preferably on a different machine, you'll confirm if there was a problem from the start or if it did begin with all the new activity.

If the problem has been sat there waiting for ages then there is probably little you can do other than repair around it (and maybe lose a few records). If the records are for archive purposes and rarely accessed then the original issue could have happened years ago. If the problem did start with your process I would suspect a hardware issue first or failing that a bug. For the former check your OS logs for anything serious, SMART readings on all your drives, verify network connections if you are not using local storage, etc. For the latter make sure you are fully patched up for your version of SQL Server, Windows. For both also check for hardware drivers/firmware updates the kit may not have been given, particularly the firmware for your main system board and any RAID (or other IO) controllers.

I would not expect the work you describe to cause corruption unless there is a hardware issue or something is out-of-date so has unfixed bugs.