In addition to Russell's comments, what kind of indexes do you have on the
table?
These can be an important factor in DELETEs, for example from BOL, if there
is no clustered index:
"Deleting Rows from a Heap
When rows are deleted from a heap the Database Engine may use row or page
locking for the operation. As a result, the pages made empty by the delete
operation remain allocated to the heap. When empty pages are not deallocated,
the associated space cannot be reused by other objects in the database."
Or if the delete criteria are not covered by an index, the DELETE statement
may have to scan the whole table, instead of being to use the index.
Obviously your isolation level is a factor also.
wBob
"Russell Fields" wrote:
> damian,
>
> By reducing the size of the deletes you will change the severity of locking.
> Deleting 800,000 rows may have been enough to trigger a table lock, but
> deleting a smaller quantity may not escalate as much. And even if it does
> escalate, the lock will be shorter. I usually use 10,000 as a rule of
> thumb.
>
> Write a little loop of code, something like this:
>
> DECLARE @TargetDate DATETIME
> DECLARE @Rows INT
>
> SET @TargetDate = '2006-07-01'
> SET @Rows = 1
> SET ROWCOUNT 10000
> WHILE @Rows > 0
> BEGIN
> DELETE MyTable WHERE Date < @TargetDate
> SET @Rows = @@ROWCOUNT
> END
> SET ROWCOUNT 0
>
> RLF
>
> wrote in message
> news:a172ac43-3744-49dd-a274-3941ad7257c8@i29g2000prf.googlegroups.com...
> >I am trying to archive old data from a SQL Server 2000 database. The
> > data has been copied away to the archive database, now I want to
> > delete the the old data from the live database.
> >
> > I want to delete rows (approx 800,000) from a table before an archive
> > date.
> >
> > I want to be able to perform inserts to the table while the delete is
> > taking place. The inserts will have a date after the archive date.
> >
> > At the moment the Delete Statement puts an exclusive lock on the whole
> > table and prevents any inserts from taking place for about 7 minutes.
> >
> > Is there a way to lock only rows before before the archive date?
> >
> >
> > Thanks
> > Damian
>
>
>