having trouble deleting 500 million rows

Question

having trouble deleting 500 million rows

FatManLittleBoy

Valued Member

Points: 50
More actions
August 26, 2008 at 10:27 am

#71787

I have a database with one table that contains audit records, nearly a billion rows. It has grown to 270 gb and the disk is nearly full. We made a decision recently to trim it regularly to keep only one year of audit records (it currently is holding three years worth). So, I have a job that looks like this:
CREATE TABLE #tblAudit
(
AuditIduniqueidentifier
)
SELECT @intNumberToDelete =
(SELECT COUNT(*)
FROM Audit (nolock)
WHERE Created < DATEADD(yy, -1, GETDATE()))
WHILE (@intNumberToDelete > 10000)
BEGIN
BEGIN TRANSACTION
INSERT #tblAudit
(
AuditId
)
SELECT TOP 10000 AuditId
FROM Audit (nolock)
WHERE Created < DATEADD(yy, -1, GETDATE())
DELETE FROM Audit
WHERE AuditId IN
(SELECT AuditId FROM #tblAudit)
DELETE FROM #tblAudit
SET @intNumberToDelete = (@intNumberToDelete - 10000)
COMMIT TRANSACTION
END
DROP TABLE #tblAudit
The problem is, it's only deleting about a million records per day, meanwhile more are coming in. So, we are looking at two years! to clean this up? Any help would be greatly appreciated. AuditId is a guid and is the clustered PK. I'm using a temp table because I have tempdb on a different drive. The database is in simple mode. The main problem seems to be getting 10,000 records that are over a year old for each pass.
Thanks,
FMLB

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Do you have any index on Created field in audit table? If not then try creating a non-clustered index on it to make the rows retrieval faster. Would be better if you go thru execution plan once and see where exactly its incurring too much cost and try optimisation in case data deletion is too slow.

MJ

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 2

You could look at rolling partitions to move out months/years of data in one go.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

FatManLittleBoy Valued Member Points: 50 More actions · Answer 3

Thank you so much for your replies. I will certainly look into partioning asap. I have also discovered that removing all the temp table stuff and just using this instead makes a significant difference.

SET ROWCOUNT 10000

DELETE FROM Audit

WHERE Created < DATEADD(yy, -1, GETDATE())