use of Curser to purge data+sql server 2005
-
Hi,
i want to purge data from one of table.. i want to retain ony 1 year data .. i want to purge all data older than 1 year .I want to use curser for that ..
if i use simple procedure like
here in that procedure:
name of database-"dbsprtCHAT "
name of procedure - "dbo.Purge_Test_sprt_chat_log_archive"
name of table-"Test_sprt_chat_log_archive"
//start of procedure :
Use dbsprtCHAT
GO
IF OBJECT_ID('dbo.Purge_Test_sprt_chat_log_archive') IS NOT NULL
DROP PROC dbo.Purge_Test_sprt_chat_log_archive
GO
CREATE PROCEDURE dbo.Purge_Test_sprt_chat_log_archive
AS
WHILE 1 = 1
BEGIN
DELETE TOP (10000)
FROM Test_sprt_chat_log_archive
WHERE DATEDIFF(Day, logged, getdate() ) > '365'
IF @@ROWCOUNT = 0
BREAK
END
GO
//end of procedure
as
there are 2 milion records presenr in that table which i want to purge.it is taking so much time.. will suffer outage for application . so i want to use cursor for efficient and fast data purging activity..
please help me how to write cursor to do this..
or suggest any other efficient way..!!
Thanks for ur help !!
Waiting for immediate reply ...:)
Regards,
Snehal.:)
-
Your procedure doesn't actually use a cursor. You are using a simple while loop.
I've used code like yours for years to do deletes and it seems to be about the fastest way to do it.
I've seen some posts here that recommend 5000 records at a time rather than 10000. Other than that the code looks fine.
Todd Fifield
-
I'd experiment with the counts. I've had systems run better with 1000 than 10,000, but it's varied. Test a little, maybe for few batches and get timings.
-
snehal.gamaji.career (2/19/2009)
Hi,i want to purge data from one of table.. i want to retain ony 1 year data .. i want to purge all data older than 1 year .I want to use curser for that ..
i
The other way of doing this is moving current year's data to a new table.
drop old table.
rename the new table with original name.
create indexes/constraints (if any)
This would be helpful and fast if your table is holding 3-4 or more years' data and you want to retain data for only 1 year.
Pradeep Singh -
I would also add a BACKUP LOG after the IF... BREAK if your datbase is using the BULK_LOGGED or FULL recovery models. This will keep your transaction log file size manageable as well.
-
Lynn,
Your point on the Backup Log is very well taken. I forgot to mention it. Without it I've seen transaction logs grow from around 100 MB to over 2 GB.
Todd Fifield
-
Thank you all for your reply..
but as the table which i want to purge contains 2 million records ,the procedure which i written taking so much time.. and face outage problem..
so i want to use cursor for that..
I have written Cursor for that as below :
//start of query //
DECLARE @ID char(10)
DECLARE DeleteCur CURSOR READ_ONLY
FOR
SELECT id
FROM Test_sprt_chat_log_archive
WHERE DATEDIFF(Day, logged, getdate() ) > '365'
OPEN DeleteCur
FETCH NEXT FROM DeleteCur INTO @ID
WHILE (@@FETCH_STATUS = 0 )
BEGIN
Delete from Test_sprt_chat_log_archive where id=@ID
FETCH NEXT FROM DeleteCur
INTO @ID
END
CLOSE DeleteCur
DEALLOCATE DeleteCur
//end of query//
but it is also taking so much time for a temp table having 32711552 records which i created for testing purpose , my actual table contains 2 million records..
so please tell me how do i accomplish this purging task?
i don't want to use "Truncate " option ,as i want logs so have to use delete ..
please help me .. Kindly Waiting for immediate reply ..!!
Thanking you.
With Regards !
-
First, make sure you have an index on the column [logged]. If this is the clustered index on the table, even better for this process.
Second, you don't need a cursor. Here is the code I'd use:
CREATE PROCEDURE dbo.Purge_Test_sprt_chat_log_archive
AS
BEGIN
DECLARE @RowsDeleted int,
@PurgeDate datetime;
SET @RowsDeleted = 10000;
SET @PurgeDate = DATEADD(Day, 365, getdate());
WHILE @RowsDelete <> 0
BEGIN
DELETE TOP (@RowsDeleted)
FROM
dbo.Test_sprt_chat_log_archive
WHERE
logged < @PurgeDate
SET @RowsDeleted = @@ROWCOUNT
-- BACKUP LOG [dbsprtCHAT] ... -- Backup transaction log if
-- -- if database in Full or Bulk Logged recovery model
END
END
GO
I have a partial start for a BACKUP LOG in the above code. If your database is using the FULL or BULK LOGGED recovery models, you will want to run a transaction log backup after each batch deleted to keep the transaction log from growing too much during this process.
Any questions, please let us know.
-
thank u for your reply 🙂
i have written another logic to purge data from two table at each execution of job.
query written as follows :
Declare @cutOff datetime
select @cutOff =min(logged) from sym_test1_sprt_chat_log_archive
set @cutOff=cast(cast(@cutOff as integer) as DateTime)
if (DATEDIFF(Day, @cutOff, getdate()) >'365')
DELETE FROM sym_test1_sprt_chat_log_archive WHERE cast(cast(logged as integer) as DateTime) =@cutOff
select @cutOff = min(logged) from sym_test2_sprt_chat_log_archive
set @cutOff=cast(cast(@cutOff as integer) as DateTime)
if (DATEDIFF(Day, @cutOff, getdate()) >'365')
DELETE FROM sym_test2_sprt_chat_log_archive WHERE cast(cast(logged as integer) as DateTime) =@cutOff
please let me know whether it is efficient way to purge data ?
Kindly reply.
Thank u .
-
More efficient, I believe so. Please look at my code I posted earlier.
-
Hi,
Thank u 🙂
i think i will go with below query :
DELETE TOP (10000)
FROM Test_sprt_chat_log_archive
WHERE DATEDIFF(Day, logged, getdate() ) > '365'
and schedule job for this query.it will be more efficient na ?
Thank u .
Regards,
Snehal.
-
Lynn,
Beware the typo. You put 365 instead of -365.
snehal.gamaji.career,
Create a clustered index and go with Lynn's suggestion. Your WHERE clause is not SARGable, and will perform a scan even with an index.
-
Richard Fryar (3/20/2009)
Lynn,Beware the typo. You put 365 instead of -365.
snehal.gamaji.career,
Create a clustered index and go with Lynn's suggestion. Your WHERE clause is not SARGable, and will perform a scan even with an index.
How is 365 wrong? If I am purging data more than 365 days old, I sure don't want -365. The value in logged should be less than the value of getdate() (which is now), so you should almost always have a positive value returned from DATEDIFF(dd, logged, getdate()) unless logged is a future date.
Edit: Nevermind, i was looking at the code from the OP, not my code. Sorry and Thank you!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply