Submit a ticketCall us
Home > Success Center > Network Performance Monitor (NPM) > Delete data in batches from large SQL database tables

Delete data in batches from large SQL database tables

Table of contents
Created by Mariusz Handke, last modified by Rodim Suarez on Mar 31, 2017

Views: 68 Votes: 2 Revisions: 8

Overview

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.

Environment

All Orion Platform versions

Steps

 

  1. Back up your SQL server database. 
  2. Navigate to your database and open the SQL Server Management Studio (SSMS). 
  3. Execute the following query.

    You can use your own criteria to specify records to be deleted (using the 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) were deleted.

    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 /* START OF COUNTER */
    DECLARE @cntmax INT
    SET @cntmax = 10 /* NUMBER OF BATCHES */
    WHILE @cnt < @cntmax
    BEGIN
      SET @cnt = @cnt + 1
      DELETE TOP(@batch) FROM YOUR_TABLE WHERE... /* ACTUAL DELETE QUERY - ADJUST */
    END
  4. When the query is completed, close SQL Server Management Studio.

    The targeted database table rows are deleted. 

 

Last modified
22:03, 30 Mar 2017

Tags

Classifications

Public