Massive delete

[bobfin]

We have a table in MS SQL Server 2000 that has over 185 million records. We archived 155 million of those records and now want to delete those records from the original table. When we ran the following script, we got a message about an unnamed problem:
Delete
From dbo.notes with (nolock)
Where user0 = 'Boost'
No records were deleted. So we tried to delete a fixed number of records with the following script:
Delete dbo.notes
From (Select Top 2 user0 From dbo.notes with (nolock) Where user0 = 'Boost') As X1
Where dbo.notes.user0 = X1.user0
After 3.5 hours, we cancelled the it. If this script takes that long to delete just 2 records, deleting 155 million records would take forever.
Is there a better way to delete these records?

 

[FoFa]

The TOP 2 will select ALL the rows first, then delete the first two, so you are not saving much there.
According to help, NOLOCK is not an option on the DELETE FROM, only:
HOLDLOCK
PAGLOCK
READCOMMITTED
REPEATABLEREAD
ROWLOCK
SERIALIZABLE
TABLOCK
TABLOCKX
UPDLOCK


I would just remove it, and make sure your log file is large enough to hold data.