Deleting in Batches

Kevin Feasel

2019-07-26

T-SQL

Jana Sattainathan gives us a few options to delete data in a way which avoids major log growth:

Today, I had to perform a DELETE that happened to remove quite a bit of data. This caused two issues for me

1. Log file growth which fills up the Transaction Log of the DB
2. TempDB was getting full

To avoid both problems I deleted in chunks.

Click through for two ways of doing it.

Related Posts

Enabling Database-Level Change Tracking

Tim Weigel continues a series on change tracking: If you don’t provide a retention period, SQL Server’s default is 2 days. Auto-cleanup defaults to ON unless you tell it otherwise. Easy! The table level commands aren’t any more complicated. Before we get started, please note that change tracking requires a primary key on the table […]

Read More

Isolation Levels and Dynamic SQL

Max Vernon points out how transaction isolation levels work when combined with sp_executesql: Imagine you have a piece of code where you don’t care about the downsides to the “read uncommitted” isolation level, and do your due diligence by adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; at the start of your code. The code following that statement […]

Read More

Categories

July 2019
MTWTFSS
« Jun Aug »
1234567
891011121314
15161718192021
22232425262728
293031