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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 purging old data

Author  Topic 

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-04 : 13:51:58
I need to create a store procedure which purges old data before a certain date, it also needs to calculate the date based on the eventtime column. I would like to set this up to run manually first

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 13:53:54
Show us what you have so far.

The key is that you'll need to loop through the data to avoid a large transaction. We purge in 1000-10000 row batches. We also use TRY/CATCH to handle deadlocks (error 1205).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-04 : 13:56:38
I dont have anythign at all. dont knwo where to start...all i know is that i need a stored procedure
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 14:13:49
This is a really old blog of mine, but it should help: http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspx

It's written for SQL Server 2000, so there are better ways to do it now (use TOP instead of SET ROWCOUNT). But the key is doing the deletes in batches, so the blog should help you with the loop.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-05 : 08:05:59
thank you
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-05 : 11:13:33
You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -