In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Blobs quickly:
Select * into temp table where(filtering a small part I want to keep)
Selectthe rest of rows
NULLvalue for the BLOB into same
- Truncate the original blob table
- copy all records back from the temp table to the original table.
I did a test run on a local machine (notebook) and Step No. one took about 20 seconds for about 2 Milion records.
The whole script took about 4 minutes while the original table held about 2.5 Milion REcords.
In a staging server env. step No. one meanwhile takes > 25 Minutes to copy 3.2 Million records!!! And SSMS actually stopped interacting with me. I estimated (using calc.exe) it might take about 1 Minute...
Now I wonder what is wrong...?