Post reply

delete records in history table

  • June 29, 2012 at 5:59 pm

    #263099

    We are trying to delete records in some history tables to save space.

    This is a database setup using recovery mode as simple mode.

    declare @dateToDelete datetime

    set @dateToDelete = CAST(FLOOR(CAST(dateadd(dd,-820,getdate()) AS float))AS datetime)

    exec trimTable @table = 'tblsystem_state_rollup', @deleteDate = @dateToDelete

    I see the messages in out put window is like below:

    1000 rows deleted from tblsystem_state_rollup at 16:37:46:380

    1000 rows deleted from tblsystem_state_rollup at 16:37:47:210

    1000 rows deleted from tblsystem_state_rollup at 16:37:47:880

    1000 rows deleted from tblsystem_state_rollup at 16:37:48:473

    1000 rows deleted from tblsystem_state_rollup at 16:37:49:037

    1000 rows deleted from tblsystem_state_rollup at 16:37:49:520

    1000 rows deleted from tblsystem_state_rollup at 16:37:50:180

    1000 rows deleted from tblsystem_state_rollup at 16:37:50:693

    1000 rows deleted from tblsystem_state_rollup at 16:37:51:430

    ...

    It runs on and on, is it really delete something?

    I found out if put the cutoff date to older data, stop the query and run again, then change the date, run it agian, -- that mean run the delete in small chunk, then it runs quickly, but I have to stop it and run it again, then I found it only takes half an hour to delete what I want, but if I use above sql command to delete data for two years, and it runs forever, I wonder if it is really deleting something.

    Is it hanging?

    Thanks

  • June 30, 2012 at 1:06 am

    #1507427

    First of all you need to increase the batchsize to aorund 5000.Second Can you post the procedure code which is deleting the data i.e. trimTable?

    Also, it is possible that when you pass a date which has much more data then it might be opting for say clustered index scan and when you do in chunks then it might be using proper indexes to find the 1000 rows to be deleted and thus is faster.

    But execution plan and the code of the trimTable proc will be useful to give a proper answer.

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply