SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hidden Tricks To SQL Server Table Cleanup


Hidden Tricks To SQL Server Table Cleanup

Author
Message
Edward.Polley 76944
Edward.Polley 76944
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 Visits: 264
Comments posted to this topic are about the item Hidden Tricks To SQL Server Table Cleanup
Jonathan AC Roberts
Jonathan AC Roberts
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5291 Visits: 2212
I think the creation of a separate view is unnecessary when you can have the view inline:

DECLARE @Rowcount int
SET @Rowcount = -1 -- Initialise
WHILE @Rowcount <> 0
BEGIN
;WITH CTE AS
(
SELECT TOP(2000) *
FROM EventTracking.dbo.Event
WHERE EventCloseDate < GETDATE() - 6 -- remove rows older than 6 days
ORDER BY Eventid
)
DELETE CTE
SET @RowCount = @@ROWCOUNT
END


Alternatively:
DECLARE @Rowcount int
SET @Rowcount = -1 -- Initialise
WHILE @Rowcount <> 0
BEGIN
DELETE EventTracking.dbo.Event
WHERE EventId IN (SELECT TOP(2000) EventId
FROM EventTracking.dbo.Event
WHERE EventCloseDate < GETDATE() - 6 -- remove rows older than 6 days
ORDER BY Eventid)
SET @RowCount = @@ROWCOUNT
END


Dave Poole
Dave Poole
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54717 Visits: 3914
Most people would never go beyond the bounds of a BIGINT with an IDENTITY(1,1) but for those of us who do, remember that reseeding identity values is incompatible with this method.

Mind you at those sorts of scales you probably have enterprise edition and can do partition switching to do even faster deletes with minimal IO impact!

I take it that the EventCloseDate as a NULL field means that you don't know in advance what that close date will be?

If you did know the EventCloseDate upfront then I'd look at putting the clustered key on it. Either that or put persist the EventStartDate and put the clustered key over the EventStartDate and Duration. Obviously this would need evaluating and testing as events going in out of sync will cause some degree of fragmentation. Whether such fragmentation would be significant or an issue for you is something only you can determine. All standard "It Depends" stuff.

LinkedIn Profile
www.simple-talk.com
karthik babu
karthik babu
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2938 Visits: 939
Alternatively, we can use this too.. it takes about an hour to delete a million records

set rowcount 50000
DELETE FROM EventTracking.dbo.Event
while @@rowcount>0
begin
set rowcount 50000
DELETE FROM EventTracking.dbo.Event
END
set rowcount 0

______________________________________________________________
Every Problem has a Solution; Every Solution has a Problem: :-)
SQLBoar
SQLBoar
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 203
Agree with last poster. Basically, DELETE TOP 1000000 FROM EVENT doesn't work, but DELETE TOP (1000000) FROM EVENT does. Similarly ROWCOUNT works, but the former (TOP), has the benefit that you can use a variable. So one of the statements is very misleading, except if you're using SQL Server 2000 - when DELETE TOP (1000000) won't work.

Although this tip is useful for me, as I have groups of tables I have to delete in chunks and order of last ID in the same sequeunce across the sets of tables.

This DBA says - "It depends".
Jonathan AC Roberts
Jonathan AC Roberts
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5291 Visits: 2212
The code in the article doesn't check that the EventCloseDate older than 6 days when it does the delete. Is it that the EventCloseDate is always present and monotonically increasing with the EventId?

It also won't always delete all the rows, for example, if there are 5000 rows that need deleting it will only delete the first 4000.
P Jones
P Jones
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9626 Visits: 1575
Itzik Ben-Gan teaches
WHILE 1=1
BEGIN
DELETE TOP (5000) FROM table WHERE .....
IF @@ROWCOUNT < 5000 BREAK
END
and you can add a WAITFOR to delay between iterations

Adjust the 5000 to suit your table.
jims-723592
jims-723592
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 103
Why keep counts at all?
Let all logic live in the view, then while any entry in the view, remove

ALTER PROCEDURE [dbo].[usp_VTrickleDeleteEvent]
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
BEGIN
WHILE exists (Select 1 from VEventCleanup)
BEGIN
BEGIN TRAN RemoveEvent
DELETE FROM VEventCleanup –- delete from view with order by
COMMIT TRAN -- COMMIT to release locks
END
END
jcunningham
jcunningham
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 174
What about the transaction log? I have worked in an environment where disk space was hard to come by. I had a similar need, but I also had to add CHECKPOINTs in the code, because the tran log was filling up the disk space.

You might be asking, "Why not just add more disk?" You would have to understand the company I worked for. BTW, I don't work there anymore Smile.
Brian Smithson
Brian Smithson
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 24
This is excellent. We also use the DELETE TOP (###) syntax. Works great.

BTW, it is 'stored procedure' not 'store procedure.' Sorry for picking the nit.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search