Post reply

This query keep on executing , not ending

  • October 18, 2011 at 12:23 am

    #245486

    Delete from t1 from account t1 join Resource t2 on t1.RID = t2.RID

    Hi All,

    The above query is deleting the similar columns in accounts table , it is not working, it is keep on running , and the log file is keep on growing, we have to delete 10 million of records.

    but why it is keep on running

    and log is increasing lot of size

    but with the same querry it is working with the example table which we created for testing

    plz help on this

    Thanks in advance

  • October 18, 2011 at 6:55 am

    #1395880

    trarunprasanna (10/18/2011)

    Delete from t1 from account t1 join Resource t2 on t1.RID = t2.RID

    Hi All,

    The above query is deleting the similar columns in accounts table , it is not working, it is keep on running , and the log file is keep on growing, we have to delete 10 million of records.

    but why it is keep on running

    and log is increasing lot of size

    but with the same querry it is working with the example table which we created for testing

    plz help on this

    Thanks in advance

    You could change it to a sub query so you would know how many records you are trying to delete.

    This will give you the total number of records to be deleted:

    select count(t1.rid) from account t1 join Resource t2 on t1.RID = t2.RID

    delete from account where

    rid in (select t1.rid from account t1 join resource t2 on t2.rid = t2.rid)

  • October 18, 2011 at 7:13 am

    #1395897

    DELETEs are fully logged, and you're trying to delete 10 million rows IN ONE TRANSACTION. You need to break this up into more manageable pieces. You can use the TOP() clause to adjust the size of the batches.

    DELETE TOP(10000) YourTable

    FROM YourTable

    INNER JOIN YourOtherTable

    ON YourTable.[ID] = YourOtherTable.[ID]

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • October 18, 2011 at 7:19 am

    #1395904

    ... and maybe increase log backups frequency while the process runs.

  • October 18, 2011 at 7:40 am

    #1395931

    yes , you are correct , i check the row count it is reduced as per our requirement but , the query is keep on running and it keep increase the log size , in this log size is increasing 20 for 1 hour

  • October 18, 2011 at 7:45 am

    #1395938

    Then run a log backup every 4-5 loops.

    It'll get done eventually without killing the server.

    The other option if you keep very little data is to export the data you want to keep to a new table. Drop the old table and rename the new one (make sure to keep indexes, constraints, triggers, etc.).

  • October 18, 2011 at 8:26 am

    #1396001

    yes correct, the table have index, and SP's.

    but my table is have the IDENTITY coloumn , it may reset? , for this it may cause any problem in feature .

  • October 18, 2011 at 8:56 am

    #1396041

    SET IDENTITY INSERT dbo.table ON

    Remember to put if back to off.

  • October 18, 2011 at 9:36 am

    #1396095

    Excellent , Thank you very much , it is working fine :-):-) :-):-)

Viewing 9 posts - 1 through 8 (of 8 total)

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