Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ashkar
Starting Member
6 Posts |
 Posted - 2011-09-12 : 10:23:45
|
| [b][b]I have a customer who is using SQL 2000 and 2005 and he is requesting to purge the data from some tables which is more than 365 years old, Appreciate assistance from any of the masters as I assume it is just 2 minutes job for them, As per the customer request,customer want us to create an automatic / manual processing / job query which will delete all the data that is more than 365 days old customer needs only 365 days of data to keep regularly, We are using date time convertor to get the date from SQL tables, our SQL value for 2011-09-12 16:20:00 will be (datetimeorigination) '1315830000'Kindly help me |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-12 : 10:29:54
|
| you need to add a sql agent job which delete the data from tables based on logicdelete from table where datefield < dateadd(dd,datediff(dd,0,getdate()),-365)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-12 : 10:39:21
|
| If you are deleting a LOT of data you may need to consider impact on other users during the deletion, and how much stress will be placed on the TLog. We delete "stale" data in a loop so that we can control how much data is deleted in each "tranche" and also how much demand is placed on the system - so we can allow concurrent access. We also increase the rate of TLog backups to be every 2 minutes during bulk data deletion to stop the log files being extended unexpectedly. |
|
|
|
|
|
|
Automatic Data Purging on SQL 2000 and 2005