Have you tried anything on your own?

Now, there's a lesson learned... Thanks, Serqiy.

No effect... maybe size of TempDB?  Mine is set to 1 gig...

Ok... replaced the CROSS JOIN with INNER JOIN like it was supposed to be...

DELETE AD1

FROM   DBO.AILMENTDETAIL AS AD1

       INNER JOIN DBO.AILMENTDETAIL AS AD2

ON  AD1.AILMENTID = AD2.AILMENTID

      ...

That's what I get for answering email before the necessary amount of coffee...

Heh... I don't think performance is the "black art" that so many think and, I agree... sometimes new hardware and SQL Server upgrades just don't do a thing...  

3 years ago,...

Dang... I gotta get 2k5...

Heh, I noticed the Peter converted the code to the good'n'proper ANSI style joins so that it's apparent that...

It was a funny comment, indeed.

No, we're not make fun of anyone except for each other  

But, I was serious about needing to...

Now, THAT's funny!

Perfect...

Obviously, you need to test this on a copy of the table before you try it on the real table...

 DELETE ad1

   FROM dbo.AilmentDetail_Test ad1,

        dbo.AilmentDetail_Test ad2

  WHERE ad1.AilmentID      ...

Heh... if you find the "other guy", give him a punch in the nose for me, huh

I agree with Michael... step 1 should...

I'm thinking there better be a reference in the two tables that directly relates the rows, as well...

smknox

The reason we need to see the rest of the table's definition is because we need to identify a column with some...

Those are all good suggestions and I'll suggest a bit more to go with those... put the Archived years in their own "read only" database, back it up once, and...

Susan,

Please post what you figured out... thanks.