Submit a ticketCall us

Solarwinds & Cisco Live! Barcelona
Join us from the 29th of January to the 2nd of February at Cisco Live 2018 in Barcelona, where we will continue to show how monitoring the network with SolarWinds will keep you ahead of the game. At our booth (WEP 1A), we will demonstrate how SolarWinds network solutions can help. As a bonus, we are also hosting a pre-event webinar - Blame the Network, Hybrid IT Edition with our SolarWinds Head Geek™, Patrick Hubbard on January 24th - GMT (UTC+0): 10:00 a.m. to 11:00 a.m. There's still time to RSVP.

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: 1,589 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