{"id":3079,"date":"2012-11-26T19:09:41","date_gmt":"2012-11-26T19:09:41","guid":{"rendered":"http:\/\/craftydba.com\/?p=3079"},"modified":"2012-11-26T20:09:21","modified_gmt":"2012-11-26T20:09:21","slug":"deleting-large-data","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3079","title":{"rendered":"Deleting Large Data"},"content":{"rendered":"

\"\"<\/a>Most of the time, the transaction log<\/a> is your friend since it can be used for a point in time recovery. However, when deleting large amounts of data from a database, it can be your enemy. I will be reviewing THREE<\/b> different TSQL patterns to delete data. <\/p>\n

Today, we will be using the MATH database that contains prime numbers from 2 to 6 million. I will post the actual files on this article so that they can be detached and reattached as needed for each test.<\/p>\n

We need to remove the foreign key constraint before we can delete data from the primary table we are deleting from – [TBL_PRIMES].<\/p>\n

-- Select correct db\r\nUSE [MATH]\r\nGO\r\n\r\n-- Remove unwanted constraint\r\nALTER TABLE [dbo].[TBL_TOP_500_PRIMES] DROP CONSTRAINT [FK_TBL_PRIMES]\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
The FIRST<\/b> design pattern is good for small amounts of data.  On the other hand, it will lead to rampart log growth if you are not careful. Since the delete occurs inside a transaction, it can be rolled back if we do not remove the correct amount of data.  This is great for preventing unwanted deletions.<\/p>\n
Algorithm<\/u>
\n    Get count of matching records.
\n    BEGIN TRAN (start a T-SQL transaction).
\n    DELETE matching records.
\n    COMMIT or ROLLBACK transaction.<\/p>\n
--\r\n-- Delete inside a transaction\r\n--\r\n\r\n\r\n-- Get before info\r\nsp_helpdb [MATH]\r\nGO\r\n\r\n-- Get count of records\r\nSELECT COUNT(*) AS Total FROM [MATH].[dbo].[TBL_PRIMES];\r\nGO\r\n\r\n-- Use transaction 2 delete\r\nBEGIN TRAN;\r\nDELETE FROM [MATH].[dbo].[TBL_PRIMES];\r\n-- ROLLBACK;\r\n-- COMMIT;\r\n\r\n-- Get after info\r\nsp_helpdb MATH\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
The before image of the database shows a transaction log file size of 32 MB.<\/p>\n
\"\"<\/a><\/p>\n
The after image of the database shows a transaction log file size of 196 MB.<\/p>\n
\"\"<\/a><\/p>\n
 <\/p>\n
The SECOND<\/b> design pattern is great for large amounts of data.  It uses the fact that the transaction log will be truncation upon a backup.  This applies to both the FULL and BULK LOGGED recovery models. A while loop is used to delete 10 K of records per batch until there a no more records to delete.<\/p>\n
Algorithm<\/u>
\n    Make sure we are not in SIMPLE recovery mode.
\n    Get count of matching records.
\n    WHILE there are matching records.
\n    {
\n        DELETE matching records in 10 K batch.
\n        BACKUP LOG to disk (truncate log)
\n    }<\/p>\n
--\r\n-- Delete in batches w\/ log backup in FULL recovery mode\r\n--\r\n\r\n-- Select correct db\r\nUSE [MATH]\r\nGO\r\n\r\n-- Set to full mode\r\nALTER DATABASE [MATH] SET RECOVERY FULL;\r\nGO\r\n\r\n-- Get count of records\r\nSELECT COUNT(*) AS Total FROM [MATH].[dbo].[TBL_PRIMES];\r\nGO\r\n\r\n-- Delete in batches\r\nDECLARE @VAR_ROWS INT = 1;\r\nDECLARE @VAR_CNT INT = 1;\r\nDECLARE @VAR_FILE VARCHAR(128) = '';\r\n\r\nWHILE (@VAR_ROWS > 0)\r\nBEGIN\r\n\tDELETE TOP (10000) FROM [MATH].[dbo].[TBL_PRIMES]; \r\n\tSET @VAR_ROWS = @@ROWCOUNT;\r\n\tSET @VAR_FILE = 'C:\\MSSQL\\BACKUP\\MATH-' + REPLACE(STR(@VAR_CNT, 3, 0), ' ', '0') + '.TRN';\r\n        BACKUP LOG [MATH] TO DISK = @VAR_FILE;\r\n\tSET @VAR_CNT = @VAR_CNT + 1;\r\nEND;\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
The THIRD<\/b> and last design pattern is exceptional for large amounts of data.  Since we are putting the database into a simple recovery model, I suggest performing a full back up before and after the deletion.<\/p>\n
Algorithm<\/u>
\n    Make sure we are in SIMPLE recovery mode.
\n    Get count of matching records.
\n    While there are matching records.
\n    {
\n        Delete matching records in 10 K batch.
\n        Use checkpoint to truncate log.
\n    }<\/p>\n
--\r\n-- Delete in batches in SIMPLE recovery mode\r\n--\r\n\r\n-- Select correct db\r\nUSE [MATH]\r\nGO\r\n\r\n-- Set to simple mode\r\nALTER DATABASE [MATH] SET RECOVERY SIMPLE;\r\nGO\r\n\r\n-- Get count of records\r\nSELECT COUNT(*) AS Total FROM [MATH].[dbo].[TBL_PRIMES];\r\nGO\r\n\r\n-- Delete in batches\r\nDECLARE @VAR_ROWS INT = 1;\r\nWHILE (@VAR_ROWS > 0)\r\nBEGIN\r\n\tDELETE TOP (10000) FROM [MATH].[dbo].[TBL_PRIMES]; \r\n\tSET @VAR_ROWS = @@ROWCOUNT;\r\n\tCHECKPOINT;\r\nEND;\r\nGO\r\n\r\n-- Set to full mode\r\nALTER DATABASE [MATH] SET RECOVERY FULL;\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
Both design patterns 2 & 3 do not growth the log file since it is truncated after each batch deletion.  Please see database size from SQL Server Management Studio (SSMS), database properties window.<\/p>\n
\"\"<\/a><\/p>\n
 <\/P><\/p>\n
I purposely skipped talking about database snapshots.  You can use them if you want added protection and quicker recovery from a bad delete statement.  The down side of using them is to have disk space equal to or larger than the data you are deleting.<\/p>\n
In summary, be careful when deleting data.  Use the BEGIN TRAN<\/a> statement for added protection when doing small deletions.  Be cognitive of managing transaction log file growth (BACKUP LOG<\/a> or CHECKPOINT<\/a>) during larger deletions. Use DATABASE SNAPSHOTS<\/a> for added protection if have the processing power and space capacity on your server.  <\/p>\n
Best wishes on clearing your database of unwanted records.<\/p>\n
MATH DATABASE<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Most of the time, the transaction log is your friend since it can be used for a point in time recovery. However, when deleting large amounts of data from a database, it can be your enemy. I will be reviewing THREE different TSQL patterns to delete data. Today, we will be using the MATH database that contains prime numbers from 2 to 6 million. I will post the actual files on this article so that they can be detached and reattached as needed for each test. We need to remove…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[254,628,311,632,31,634,627,629,12,631,15,633,630,28,29],"class_list":["post-3079","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-backup-log","tag-begin-tran","tag-checkpoint","tag-commit","tag-database-developer","tag-database-snapshot","tag-delete-batches","tag-delete-top-from","tag-free-code","tag-full","tag-john-f-miner-iii","tag-rollback","tag-simple","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3079","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3079"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3079\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3079"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3079"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3079"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}