Microsoft SQL Server forum - Deletion old data in replication environment

Author

Deletion old data in replication environment

Marco

2005-06-29, 7:23 am

Hi to all

I have a question about deletion of amount of data:

My production environment is this one:
- one publisher with a database (historycal events)
- 50 subscribers with the prev database in unidirectional replication
unidirectional (from subscribers to publisher)

My target was capturing events from the subscribers to send them to
publisher (later I can do reports on it).
Once the data is on the server i don't need them any more in subscribers.

Now I would like to delete the oldest data (year 2003) of some table on the
publisher (remember that replication is unidirectional S->P).

The tables contain about 6-7 millions of records.
I delete one month per time. The process is about 30 minutes long and the
merge agent subscribers changes in retry state.

Can I use these queries to make faster this process? Eventually what kind of
problems can I have ?
DELETE FROM mydb WITH (PAGLOCK) WHERE mydb.dbo.mydate Between date1 and
date2
or
DELETE FROM mydb WITH (ROWLOCK) WHERE mydb.dbo.mydate Between date1 and
date2

Thank you very much for your support.

Marco

Sponsored Links