Delete Data:Offline or Online?

[SQLBill]

SQL Server 2000

I have a database with 9 months worth of data and am trying to get it down to 6 months worth of data. The database was created by a third-party application and I have to use a 'purge' command they supply. I'm trying to find the quickest way to delete a set amount of data (not all of it).

Currently, I am deleting data while the database is online and in use. It takes about 3 hours to delete around 1 million rows.

I am unable to test doing the delete with the database offline and we don't have a test system. What I would like to know is: has anyone done a comparison of the difference in time when deleting from an offline database vice an online database?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

By offline do you mean one connection only, if so this should be faster since there will be no blocking/locking from other connections
Another way is to do the delete in chunks of let's say 10000


set rowcount 10000
delete table where date < '6/1/2005'

just repeat until you get a 0 records affected message, or put it in a while loop


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

My third-party application already breaks it down to chunks. But it's still pretty slow. I believe, like you state, that doing the delete in single-user mode will be quicker...but I need facts. I'm hoping that someone has tried it both ways and can tell me an estimate of how much faster it is to do the delete in single-user mode.

I'm getting about a million records in each day and it's taking 3 hours to delete a million records, plus the delete slows down the application. So, I have to do it during slow periods. This is making it take a long time (it will probably take me a little overa month to delete two months data).

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

What about?

Inserting the records you want into a new table (select into of course)
dropping the old table
and renaming the new table so that the name is the same as the old table
Would that work for you?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

Nope....way too many records and too many connected tables. Plus as I said this is a third-party database and whenever we suggest something like that we get "sure, but then we won't support it any longer".

-SQLBill

Posting advice: FAQ481-4875

 

[yelworcm]

While you are doing the delete, run this query, and check the output against Q article 822101:

select waittype, lastwaittype, waitresource
from master..sysprocesses
where spid = (spid doing the delete)

If you are getting a lot of blocking (waittypes 0x01 through about 0x15), then you would probably get faster performance if you had the data to yourself. If it is all latch waits (0x42_), and log waits (0x81), then being alone won't make as much difference. I would bet that a good deal of the time you are waiting is going to be retrieving the data from disk, so the data can be marked deleted. How many rows are in this table? And about how many MB?

 

[SQLBill]

The primary table is 50.454 GB and 187,106,993 rows.

I'll try that query when I next do a delete.

-SQLBill

Posting advice: FAQ481-4875