Sometimes, when you try to delete a large number of rows from a table the transaction file may grow to the point that you will run out of disk space. In other occasions, when the transaction log growth is limited, the delete can fail with the following message:<\/p>\n
The transaction log for database ‘database name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases<\/strong> 1)\u00a0\u00a0\u00a0 If you want to delete all rows in the table, consider using TRUNCATE TABLE instead of delete. Truncate will not fill the transaction log. Remember that TRUNCATE will also reset any auto incrementing keys. a) \u00a0\u00a0 Copy to the rows you want to keep to another table<\/p>\n b) \u00a0 Truncate the original table<\/p>\n c) \u00a0\u00a0 Copy the data from the temporary table back to the original table.<\/p>\n 3)\u00a0\u00a0\u00a0 Delete manually in chunks. You can run: delete TOP (10000) from [your big table]<\/strong>. Remember to commit between deletes.<\/p>\n 4)\u00a0\u00a0\u00a0 Use script to delete in chunks with commit every number of rows. DECLARE @Count INT While (@Count < @for_delete) COMMIT TRAN Sometimes, when you try to delete a large number of rows from a table the transaction file may grow to the point that you will run out of disk space. In other occasions, when the transaction log growth is limited, the delete can fail with the following message: The transaction log for database ‘database name’ […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[23,25,4,24],"class_list":["post-44","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-delete","tag-full","tag-sqlserver","tag-transaction-log"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/44","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=44"}],"version-history":[{"count":4,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/44\/revisions"}],"predecessor-version":[{"id":248,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/44\/revisions\/248"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=44"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=44"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nThere are several ways to overcome this issue:<\/p>\n
\n2)\u00a0\u00a0\u00a0 In case you need to keep some of the data in the table, depending on the ratio between the numbers of rows you are keeping to the number of rows you wish to delete, you may want to:<\/p>\n
\nThis script for example commits every 100000 rows. Change it to fit your specific scenario:<\/p>\n
\n<\/strong><\/p>\n
\nDeclare @for_delete INT
\nDeclare @chunk_size INT
\nSELECT @chunk_size=100000
\nSELECT @Count = 0
\nselect @for_delete=count(*) from [Your big table] where\u00a0 [Your Where Clause]
\n<\/strong><\/p>\n
\nBEGIN
\nSELECT @Count = @Count + @chunk_size
\nBEGIN TRAN
\nDELETE top(@chunk_size) FROM\u00a0 [Your big table] where\u00a0 [Your Where Clause]<\/strong><\/p>\n
\nEND<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"