SQL Server Performance Forum – Threads Archive
Delete Query TimeoutI have to delete records from a table having millions of resords.
I am deleting the records having timestamp greater than 60 days through my vb appication.
But as the records grew up in the table it starts giving me error. I have used Delete from TABLE1 Where Datediff("d",TimeStamp,Getdate()) >= 60
To delete …. the records. I have already marked the timestamp field for index (Not Clustered). Can any body give me the exact solution
Thankx in advance…..
Did you try with index tunning wizard. —————————————-
No I haven’t because there is only one field in the where Clause which i have already marked for Index.
If the number of rows for less than 60 days condition is not as big as compared to total count on the table, then you can rename the current table as an archive table and then recreate the required table with a SELECT INTO clause.
In future make sure to perform this archive task atleast once in a week to avoid the issue again. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.
If the TimeStamp column is indexed this can improve it Delete from TABLE1 Where
TimeStamp<DateAdd(day,Datediff(day,TimeStamp,Getdate()),-60) Are you using SQL Server or Access? Madhivanan Failing to plan is Planning to fail
i am using SQL Server 2000
Then try the method I suggested Madhivanan Failing to plan is Planning to fail
where timeStamp < DATEADD (day, -60, getDate()) That way the index will speed-up the query. If there are millions record to delete first time you run the query, use the method recommended by Satya.