0

I have a SQL Server Azure table which has grown to about 300 gigs. The table has about 100,000 rows. The table holds uploaded [content] as varbinary in one of the columns. The app that uses this table has been updated so that the data is now stored in Azure Storage, instead of in the database. But the table is still used to track the files.

Each row has up to 40 megs of data. What is the best way to clean this up?

I have tried setting the data to a default value, but the operation takes a great deal of time. Like up to 1 second per row. So 60 rows is a minute and it is going to take some hours to run and then shrink the database.

What is the fastest way to accomplish this? This task has been harder than it first appeared as I did not expect it to take so much time to process.

For example, should I just drop the column, and then recreate it with a default value?

Or copy the data to a different table, truncate the actual table and then copy the data back?

The goal is to get the database size down to less than 10 gigs as the rest of the data is really not that large, it is only the file bytes hogging the space.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Greg Gum
  • 33,478
  • 39
  • 162
  • 233
  • After updating the tables with some placeholder value, or `null`, you're likely to find that the database files haven't actually been reduced in size because the data pages will only have been freed for use - not deleted and removed from the files. – AlwaysLearning Apr 11 '23 at 04:02
  • Regardless of space reclamation, if the desired end state is to have the `content` column have the same value at the end of your operation, the 'drop column → re-add column with default value' maneuver you outlined is going to be the fastest. – Ben Thul Apr 11 '23 at 14:39
  • @BenThul, thank you, that is exactly what I ended up doing. If you want to post as an answer, I will accept. – Greg Gum Apr 11 '23 at 23:09

0 Answers0