February 6, 2012 at 4:10 am
Dear Experts,
I have a requirement to purge a table which is holding 5 millions of records.
Can you please any one suggest me how to delete these 5 million records without creating new issues like log space issues.
Please advice me the best practices in above case.
February 6, 2012 at 4:13 am
All 5 million, not keeping any rows at all?
If so:
TRUNCATE TABLE <table name>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 4:20 am
Yes, i have to delete the complete data(~5 million) from the table . So can i go with truncate option?
February 6, 2012 at 4:21 am
And also if possible, can you please tell me how much time it will take to truncate 5 million records
Thanks in advance...
February 6, 2012 at 7:26 am
Ok but i have more Prof way
The first option seems to take the shorter time to execute but I didn’t want to go through the effort of removing and adding foreign keys and renaming tables. So I went with the second option. Here’s the script I used for SQL Server which deletes 10,000 rows at a time and commits them.
DECLARE @continue INT
DECLARE @rowcount INT
SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 10000
BEGIN TRANSACTION
DELETE FROM Transactions WHERE TradeDate IS NULL
SET @rowcount = @@rowcount
COMMIT
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
February 6, 2012 at 7:28 am
Sorry but i forget to post you another important link of your issue
http://social.msdn.microsoft.com/Forums/br/transactsql/thread/844fdfbd-d7bc-41bb-9264-73c5c9285f04
February 6, 2012 at 7:43 am
Per BOL:
TRUNCATE TABLE (Transact-SQL)
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Truncate Command will have least impact on transaction logs. How much time it will take? That you can measure on test server.
February 6, 2012 at 7:59 am
mohan.pariveda 18256 (2/6/2012)
Yes, i have to delete the complete data(~5 million) from the table . So can i go with truncate option?
Yes, if you are going to clear the table completely. It should be milliseconds, it doesn't delete the rows, just deallocates the pages and, if there are a lot of pages, the deallocations are in the background.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 8:11 am
Are the 5mm records all the records in the table or are there more than 5mm? If that's the whole table, use truncate. If not, you can batch the deletes in buckets of 10,000 or so. The optimal number for your system depends on hardware and load, but I've had good luck with 5,000-20,000 at a time. Try a few batches and see how quickly this works.
As you delete, the log will fill, but you can run additional log backups to keep the log size reasonable.
Timing totally depends on many factors in a delete. Hard to gauge.
Be sure you check if there are FKs on this table. That may cause issues with deletes.
February 7, 2012 at 7:48 am
Thank you all for your most valuable suggestions . I have used the truncate table option to make the table complete empty and Gila Monster said it was taken only m.secs to complete the task.
Thanks again for your time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply