1

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:

  1. Select * into temp table where (filtering a small part I want to keep)
  2. Select the rest of rows WITH a NULL value for the BLOB into same temp table.
  3. Truncate the original blob table
  4. 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...?

TY

  • I realized I forgot to change the recovery model. It was set to FULL. "The recovery model SIMPLE and BULK_LOGGED perform "best" contrary to the recovery model FULL." Could this cause the effect? – Magier Aug 11 '15 at 14:04
  • What is the percentage of rows that need to be removed? – Marco Aug 11 '15 at 14:13
  • 1
    I plan to do this durung a regular system maintenance with no user access + enough time. A first shot with production data copy took < 15 mins. Also I modified the procedure and simply wiped out step no 4, because it doesn't make sense to copy all data back to the original table, if the intermediate table already holds an exact copy of the data I want. So I rename the intermediate table and recreate all keys, indexes and constraints there. Since there is no FK present and no identity columns, this looks as the easiest and fastest approach to me. – Magier Aug 12 '15 at 8:17
  • 1
    please do not forget to do a full backup before and after you run your script. – Henrik Staun Poulsen Aug 12 '15 at 8:53
  • 1
    Good idea to do it when there is no user access. The rename part is also much faster than the re-copy but this also can only be done when there is no user access. – Marco Aug 12 '15 at 8:53
2

I figured it was the Revovery Model that was left set to FULL. As the article linked in the question says, it is required to change it to simple of bulk: "The recovery model SIMPLE and BULK_LOGGED perform "best" contrary to the recovery model FULL." So, after setting it to simple the process performed within couple of minutes.

  • 2
    If you can live with the Recovery Model as Simple, it is a lot leaner on the transaction log. But you cannot do point-in-time restores. You can also use Bulk_logged, but that requires work if you want your point-in-time restores to work as expected. – Henrik Staun Poulsen Aug 12 '15 at 8:47
  • Please point me to what maybe the result of switching to SIMPLE and afterwards back to FULL in my maintenance sctipt? Production systems run with FULL, so should I better not change it there or can I do it if I prepare / consider something around this? – Magier Sep 29 '15 at 9:24
1

One reason why it is taking so long could be the AutoGrow settings on the database files and log files. If that is set to a very low value, you might end up with many many, synchronous, auto grow events. You can avoid this by pregrowing the data/log files and/or increasing the AutoGrow settings.

0

are you beeing blocked by another process?

you can check that by using sp_whoisactive (see http://whoisactive.com/)

here you can also see how far your job is, and what the execution plan look like.

  • I looked for blockings with sp_who2, nothing there. Meanwhile I cancelled. Cancelation also takes a lot of time and still runs... sp_whoisactive shows Blocking NULL, percent completed NULL... – Magier Aug 11 '15 at 14:10
  • yes, a Rollback takes quite a while; it is single threaded. I often see laptops outperforming the main server, which I find strange, but the single-user is probably a greater advantage than I expect. – Henrik Staun Poulsen Aug 12 '15 at 8:42
  • I find that sp_whoisactive is a lot better than sp_who2 – Henrik Staun Poulsen Aug 12 '15 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.