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.
like
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
use http://sqlbaba.com/table-partitioning/introduction-to-table-partition/
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
