Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

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: 485 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