Good call... just make sure to put the waitfor at the top of the loop so that it does not affect the rowcount!... or save that value in a variable...

Are those errors in the non clustered indexes or in the base table?

You don't need any cursor. Do a test on a smaller table to understand how it works.

Just use the script as I put it, it will delete all the...

One last idea that me be real fast and not cause any troubles.

Can you load all the new data into an exact copy of the base table, then in a...

The progressive delete is quite easy and often much faster than doing a single delete with millions of rows :

SET ROWCOUNT 10000

DELETE FROM dbo.Table WHERE WhatEver

WHILE @@RowCount = 10000

BEGIN

--make...

What have you tried to repair them?

Create a reporting table.

Run a query daily to load the data into that reporting table.

Run your reports against that table with proper indexing and you should get very efficient queries.

Make sure you have a backup nearby.

Run diagnostics on the DB to validate the integrity.

The 2 options I found to repair this problems are use a good backup to restore...

One way to be sure about the hd fragmentation would be to drop the DB althogeter. Run defrag. Then recreate the DB file at their correct size. ...

Here's another one Steve, it's at least the 10th time I see people asking for a way to see the last posts with the reply button... anyway to make those...

In that case you don't really have a choice to do it the way you're doing it (or similar).

For the db fragmentation. Check the current DB size on the...

Let me be the first to suggest you to save the execution plan in a .sql file, zip it and attach if to your post. Noone's going to read...

The_SQL_DBA (9/26/2008)

---

We are planning to delete a bunch of old data. When I do the test delete on a table with 250million + rows it takes 25 hours to delete...

As a side note, isnumeric has many gotchas... those string are all considered valid numeric values :

'1'

'1.00'

'1E10'

You might consider using this if you need something that checks for IsAllNumbers...

That's called short-circuiting. Sql server will stop evaluating the where condition(s) as soon as the whole statement cannot be true.

Run this and you will see what I mean. ...