I am working on a project to delete entries from a database. I have a script that goes through about 4000 tables and deletes entries that meet a certain condition. Basically, I am batching the tables into 200 tables and running a cursor loop that reads a table with a bunch of table names, takes the names and creates a statement and puts it into a variable. Then the script runs the statement using sp_executesql. It takes around 8-11 hrs per batch to run through the script. I am trying to shave some more hours off run time. What are some settings I can disable to make it run faster? I have tried to disable all triggers and that did not help it? Is possible to disable backup or transaction log? Is using sp_executesql slowing down the process? Should I just do multiple delete statements for each table (ie DELETE FROM TABLE WHERE X = Y)?

migration rejected from superuser.com Jan 15 at 20:56

This question came from our site for computer enthusiasts and power users. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by jadarnel27, Michael Green, mustaccio, Max Vernon, LowlyDBA Jan 15 at 20:56

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • My advise is disable trigger before starts deletion, create a index at the column just for the delete statement if its possible and split your delete statement to not fulfill the log – Krismorte Jan 10 at 2:19
  • 2
    To help us understand better can you please edit the question to show a representative sample of the string you construct that is then executed. – Michael Green Jan 10 at 10:07
  • 1
    How many rows / gigabytes are removed in that 8hr runtime? – Michael Green Jan 10 at 10:09