Submit a ticketCall us

WebinarFREE IT Monitoring Webcast

Don’t miss out on our webcast, Essential IT Monitoring with SolarWinds ipMonitor, where we will show you how to keep an eye on your IT environment from one centralized, affordable, and lightweight monitoring tool: SolarWinds® ipMonitor®.

Register now.

Home > Success Center > Network Performance Monitor (NPM) > NPM - Knowledgebase Articles > 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_ret on Mar 31, 2017

Views: 6,510 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

Tags

Classifications

Public