You must Sign In to post a response.
  • Category: SQL Server

    Need to delete old data in SQL table

    Hi All,

    I have a SQL table with bulk amount of data. Client wants to keep last 3 months data and delete remaining items. But there is no date field in that table. How can we identify the last 3 months data? Please provide your suggestion on this.

    Best Regards,
    Simiyon A
  • #767227

    In your query Previously you did not handled date Field so we canot Do.
    try to tracking manually based on Records note from your side or try to track id based checking .

    Name : Dotnet Developer-2015
    Email Id :


  • #767228

    One approach is when you have set Change_Tracking enabled for a perticular table.
    you can check whether it is enabled or not by selecting your Table--> Properties--> Change Tracking.
    Change Tracking works on primary key column on perticular table. If any changes are done like insert/update/delete on table which is enabled for Change_Tracking then sql server creates one internal table named as 'change_tracking_<object_id>' and stores the updated info for that primary key column in it.
    If Change_Tracking enabled is true then you can check CHANGE_RETENTION period for that table and retrieve or delete records present inside that depending on primary key column.

    Please find detailed sql queries on Change_Tracking over here:

  • #767230
    Hi Shasi,

    Really Change_Tracking concept will helpful in fututre and it will helpful to track the data changes.

    Thank you!!!

    Simiyon A

  • #767589
    you have to add date column in your current table , then update that with current date . make your date column default getdate() and not null,

    removing older then 3 month data every month can create performance problem for you.
    1. when you will go to delete records SQL server will take row lock , but after approx 2000 row locks it will do lock escalation which means table lock, so your table wont be accessible until that operation complete.
    2. deletion will make your index fragmented and also change modifcation counter for stats , which will again impact performance, as stats need to be updated , and we you keep index fragmented it will take long time for running select queries against the table.

    Solution: for best solution you should have enterprise edition of sql server. if your answer is yes then read below part otherwise you can stop reading this comment at this place only.

    one time operations
    1. create a partitioning function which will have range for each month, suggested to use left range
    2. create a partitioning scheme using that function
    3. drop you main table clustered index and recreate it with new partitioning scheme.
    4. change table lock_escalation level to auto
    5. create a exact replica of table with name _history

    monthly operation which you can schedule as a job
    1. schedule a job to add range in partition function for next six month, you should be always in 6 month advance(good practice)
    2. schedule a slide window job that will move older than 3 month partition to _history table(only meta data operation will take couple of seconds)
    3. after 15 days (for safer side) you slide window job run a truncate table on _history table.

    if you want to read more about table partitioning

    Many Thanks
    Tejinder Singh Barnala
    /*I have the simplest tastes. I am always satisfied with the best*/

  • #767590
    check below link

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • Sign In to post your comments