Why does my table keep getting corrupted after removing large amounts of data?
0
votes
2
answers
455
views
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)
Asked by B West
(1 rep)
Sep 24, 2020, 03:08 AM
Last activity: Jan 15, 2025, 01:00 AM
Last activity: Jan 15, 2025, 01:00 AM