September 8, 2015 at 10:10 am
If I have more than one table than is it good to use View. Means I have 20 tables and I have to delete data from them. Kindly give me suggestion to delete data without affecting server.
September 8, 2015 at 10:19 am
keyurgondalia (9/8/2015)
If I have more than one table than is it good to use View. Means I have 20 tables and I have to delete data from them. Kindly give me suggestion to delete data without affecting server.
how did you end up with 20 huge tables?
this techniques is for cleaning up live db where couple of tables have grow beyond reasonable size and need to be cleaned in a live mode.
are you saying that you have a production db where 20! tables have been allowed to grow in millions of records and they all need to be cleaned live?
WOW
September 8, 2015 at 2:59 pm
Great idea, thanks.
September 13, 2015 at 6:07 pm
Yes, i have more than 10 tables where data is in millions. One job is running to delete data, but when it goes in a table where there is millions data than while it finish to delete data other table will fill up data.
September 13, 2015 at 6:36 pm
keyurgondalia (9/13/2015)
Yes, i have more than 10 tables where data is in millions. One job is running to delete data, but when it goes in a table where there is millions data than while it finish to delete data other table will fill up data.
Just set up different jobs for different tables. Then you can run them together.
September 16, 2015 at 6:16 pm
tlocke-512364 (3/6/2013)
How would this affect replication? Would replication flow smoother than doing a delete * from the large row table?
If you are planning to delete large numbers of records and replication is involved, I would implement the logic in a stored procedure and replicate the action of that stored procedure. As part of the definition of the article for that stored procedure, tell replication to replicate the actions for the stored procedure.
By default, replication only replicate the definition of the stored procedure. When you execute the stored procedure, the results (the deletes) are recorded in the transaction log as individual deletes of each record in the table being deleted. The deletes are "copied" from the transaction log into the distribution database and then applied to the subscriber.
By replicating the action of the stored procedure, the stored procedureitself is executed on the subscriber so, instead of replication having to execute several million DELETE statements (including storing them in the Distribution database), replication will simply execute the stored procedure in the subscription database.
This technique reduces the size of the relevant records in the Distribution database and reduces network traffic between the distributor and the subscriber
Viewing 6 posts - 91 through 95 (of 95 total)
You must be logged in to reply to this topic. Login to reply