Databases
StackOverflow2013
Postsdbo
POProblem running a query against a large table in MS SQL 2005 using all the server resources
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblem running a query against a large table in MS SQL 2005 using all the server resources
    primarykey
    data
    text
    <p>I have an MSSQL 2005 database with GBs of orphaned useless data. This bad data causes the database to be hard to work with, taking 4 hours to back up and 7 hours to restore. All the bad data is in one table.</p> <p>I have decided to import the data into another table and delete the records I believe are bad. So I am running a simple sql query like this:</p> <pre><code>Insert databaseB.dbo.table (col1,col2) select col1,col2 from databaseA.dbo.table </code></pre> <p>My last attempt to run the above query ran for 12 hours without completing before I had to cancel and restart the sql services because any application that used any database on that server would not respond. </p> <p>After this operation completes I will be running simple delete “delete databaseA.dbo.table where condition=1” which the last time I tried it locked the server as well.</p> <p>I do not need any locks on the table I am trying to update.</p> <p>Any suggestions on how I can limit this query to not affect this or other production systems? Are there any flags or options I can set to make this run quicker or smoother? (like changing the recovery model) It can run for days if necessary I just need to somehow throttle the resources used. Thanks in advance for any advice and let me know if I have provided enough information.</p> <p>It has occured to me to backup and restore the database to another location. Truncate the table and then import the good data back to the table in question. But since it would take a minimum of 11 hours to complete step 1, and step 2 is an unknown length, I do not like this option.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USClodoaldo Neto
    1. USMatthew Bierman
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POProblem running a query against a large table in MS SQL 2005 using all the server resources
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload