September 27, 2011 at 7:30 am
This SQL (or a version of it) used to work for me. To perform MASS delete's, this SQL would incrementally DELETE 5000 rows and COMMIT, then DELETE 5000 rows and COMMIT etc.. Not sure this is working in SQL 2008 R2. Does anyone have a better sample solution to incrementally DELETE/COMMIT mass quantities of rows? thx in advance
Use MyDatabase
GO
DECLARE @x INT, @y INT
SELECT @y = 1
WHILE @Y > 0
BEGIN
SET @x = 1
SET ROWCOUNT 5000
SELECT @y = COUNT(*) FROM My_Table
WHERE My_Last_Update_Date < (SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(YEAR,-1,GETDATE())), 0)) -- < Resets @Y to current RowCount
WHILE @x > 0
BEGIN
BEGIN TRAN
DELETE My_Table WHERE My_Last_Update_Date < (SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(YEAR,-1,GETDATE())), 0))
SET @x = @@rowcount
COMMIT TRAN
END
END
September 27, 2011 at 7:37 am
while @@rowcount <> 0
DELETE TOP (5000) FROM dbo.Table where Dt....
You can then include waitfor to give the server time to breathe and also commit every few runs.
You select count(*) is extremely counter productive. Just ask for the delete. If the @@rowcount = 5000 then you need to keep going. If not you're done.
September 27, 2011 at 8:39 am
Lynn Pettis wrote a geat article on this subject:
http://www.sqlservercentral.com/articles/T-SQL/67898/
-- Gianluca Sartori
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply