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.