Bulk Deletes and Bulk Copy

We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
â'Archivingâ' process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> â'Archivingâ' process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
> > We are currently facing some issues with bulk deletes of records during
> > Archiving.
> >
> > Our application logs all the requests and responses in the log tables. After
> > 10 days these requests are supposed to be archived (to a different database
> > â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> > â'Archivingâ' process is taken care by a stored procedure. Also, please note
> > that there are certain restrictions (like request must have been
> > successfully) on a request before it can be archived and deleted.
> >
> > What is the problem?
> >
> > We are facing issues both while copying the data to archiving database and
> > deleting the data from Main database.
> >
> > 1. When records are to be copied from Main to Archiving database, locks are
> > acquired on the log tables in Main database and the log tables are blocked
> > there by hindering any further activity.
> >
> > 2. The same problem occurs while deleting the records from log tables.
> >
> > What are we looking for?
> >
> > Would be great to have some best practices around archiving and bulk deletes
> > in SQL server.
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.