How to Delete Large data from a SQL Server Table

How to Delete Large data from a SQL Server Table

Learn how to delete a huge number of records from a SQL Server table in this blog

Script used in the this blog that can be used to delete data from table in small portions

--Get the Data File and Log file for a Database in MB

 

SELECT file_id, name, type_desc, physical_name, (size*8)/1024 SizeinMB, max_size

 FROM sys.database_files ;

 

--Delete the Records in Small Chunks from SQL Server Table

 

DECLARE @DeleteRowCnt INT

 

SET @DeleteRowCnt = 1

 

DECLARE @DeleteBatchSize INT

 

SET @DeleteBatchSize=100000

 

WHILE (@DeleteRowCnt > 0)

 

  BEGIN

 

     DELETE TOP (@DeleteBatchSize) [dbo].[Customer] 

 

     WHERE RegionCD='NA'

 

  SET @DeleteRowCnt = @@ROWCOUNT;

END