Delete data in batches from large SQL database tables

If you have a Microsoft SQL Server database that contains specific data you want to remove, removing targeted table rows in a single transaction may require an extended amount of time to process, This article describes how to remove targeted table rows in batches. 
 

Note: Removing table rows in batches instead of a single transaction is time consuming, but a more reliable process.
 

1. Establish a remote connection to your SQL Server.
2. Open SQL Server Management Studio (SSMS).
3. Connect to the instance where your database is located.
4. Back up your SQL server database. 
5. Right-click on your database and select New Query.
6. Execute the following query, making sure to adjust the DELETE query to specify the table and criteria for which records have to be deleted (WHERE clause):
    

   Note: You may need to execute the query multiple times to achieve your desired result. If  "0 rows affected" displays after executing the query, all matching rows (or records) are deleted.
    

   -- Scripts are not supported under any SolarWinds support program or service.
   -- Scripts are provided AS IS without warranty of any kind. SolarWinds further
   -- disclaims all warranties including, without limitation, any implied warranties
   -- of merchantability or of fitness for a particular purpose. The risk arising
   -- out of the use or performance of the scripts and documentation stays with you.
   -- In no event shall SolarWinds or anyone else involved in the creation,
   -- production, or delivery of the scripts be liable for any damages whatsoever
   -- (including, without limitation, damages for loss of business profits, business
   -- interruption, loss of business information, or other pecuniary loss) arising
   -- out of the use of or inability to use the scripts or documentation.
   --
   USE YOUR-DB-NAME /* PUT YOUR DATABASE NAME HERE */
   GO
   DECLARE @batch INT
   SET @batch = 10000 /* SIZE OF BATCH */
   DECLARE @cnt INT
   SET @cnt = 0
   DECLARE @cntmax INT
   SET @cntmax = 10 /* NUMBER OF BATCHES */
   WHILE @cnt < @cntmax
   BEGIN
     SET @cnt = @cnt + 1
     DELETE TOP(@batch) FROM YOUR_TABLE WHERE... /* DELETE QUERY - ADJUST AS NEEDED */
   END

7. When the query is completed, close SQL Server Management Studio. The targeted database table rows are deleted.