Hidden Tricks To SQL Server Table Cleanup

  • keyurgondalia

    SSC Enthusiast

    Points: 136

    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.

  • Vlad-207446

    Right there with Babe

    Points: 773

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Great idea, thanks.

  • keyurgondalia

    SSC Enthusiast

    Points: 136

    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.

  • Jonathan AC Roberts

    SSCoach

    Points: 17100

    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.

  • happycat59

    One Orange Chip

    Points: 29189

    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 96 (of 96 total)

You must be logged in to reply to this topic. Login to reply