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

7
  • 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
    Commented Aug 11, 2015 at 14:04
  • What is the percentage of rows that need to be removed?
    – Marco
    Commented Aug 11, 2015 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
    Commented Aug 12, 2015 at 8:17
  • 1
    please do not forget to do a full backup before and after you run your script. Commented Aug 12, 2015 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
    Commented Aug 12, 2015 at 8:53

3 Answers 3

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
  • 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. Commented Aug 12, 2015 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
    Commented Sep 29, 2015 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.

3
  • 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
    Commented Aug 11, 2015 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. Commented Aug 12, 2015 at 8:42
  • I find that sp_whoisactive is a lot better than sp_who2 Commented Aug 12, 2015 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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