Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts

Friday, 12 October 2018

Purging SQL Table without impacting the users


This happens very rarely but sometime I have been asked to conditional purge the SQL table without impacting the Users (So we can not use the truncate which is quick and harsh  :) ), These kind of request is pain when client says this table is business critical and used by different application 24/7 so can not take offline and delete the data. So I came up with SQL script or Stored Procedure which will purge the data without impacting the users, though this Proc can run slow or fast based on the Batch Size you have selected, Smaller (not Smallest) the batch size lower the deadlock risk. You can select the Batch and Total deleted row count based on your need. 


Hope, This proc will be useful for you and let me know if required any improvement. I have written this for SQL Server, you can rewrite for any other DB as well after small tweaking

-



Like the below page to get update  
Facebook Page      Facebook Group      Twitter Feed      Google+ Feed      Telegram Group