May 29, 2012 at 12:25 am
Hi Everyone,
Hope all is well.
I wanted to delete older data from 2 different tables. I am running the delete query put below as a whole from SSMS instead.
My Query:
DECLARE @cmd NVARCHAR(100)
DECLARE @parm NVARCHAR(100)
DECLARE @number VARCHAR(10)
DECLARE @number_out VARCHAR(10)
DECLARE @Date Varchar(25)
SELECT @cmd = N'SELECT @number_out = CONVERT(VARCHAR(10), GETDATE()-45, 23)'
SELECT @parm = N'@number_out VARCHAR(10) OUTPUT'
EXECUTE sp_executesql @cmd,@parm,@number_out = @number OUTPUT
Set @Date=@number+' 02:55:00.000'
Delete VR FROM test VR INNER JOIN test1 PP ON VR.ID = PP.ID INNER JOIN
INNER JOIN test2 PJ ON PP.testID = PJ.testID where PJ.[columnname] IN (2,3) and PJ.CompletedAt<@Date
Delete PM FROM table1 PM INNER JOIN table2 PP ON PM.ID = PP.ID INNER JOIN
table3 PJ ON PP.testID = PJ.testID where PJ.[columnname] IN (2,3) and PJ.CompletedAt<@Date
I first ran a select before running the above query to know count of records that will go. The first query shd delete 7 millionn records and the second shd delete 4 million records. I am verifying the number of records in the table from the SSMS object explorer when I execute the delete statements.
The issue here is we have a very short downtime. when the query is running I see that the 7 million records got deleted from table test as expected and it got deleted in almost 1 hr. But the second delete would not start right after the first delete deletes data from the first table. It took another 1 hour (total 2hours) before the second delete started. Can someone please throw some light on me on this behaviour? Is this because once the records are deleted its taking time to release space in the transaction log?? I verified the free space in the transaction log after the first delete and before start of second delete. The free space increased from 48% to 79%. So is that why it took so long??
Thanks.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
May 29, 2012 at 1:38 am
How many records are there in total in your table?
The reason I ask is that you could SELECT the records you want to keep using a SELECT INTO statement to create a Temp "staging table", then TRUNCATE your original table, then re-insert the records (or delete the original table and re-name the staging table)
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 29, 2012 at 8:38 am
Thanks for the reply andy. Each table has more than 11 Million records and daily I will delete almost 2 million from each table except for last night since it was run for the first time.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply