How to Delete huge data from a SQL Server Table - SQL Server / TSQL Tutorial

In this video you will learn how to delete large number of records from a SQL Server Table. By watch this video you will learn following topics

1--How to Delete large number of records from SQL Server Table in small chunks
2--What is the difference between Full Recovery Mode and Simple Recovery Mode
3--Does Transactional Log file grow if we set the database to Simple Recovery and Delete huge number or records?
4--How to get the record count while Transactions is deleting the records by using NoLock Option
5--How to Shrink Log file and release unused space to Operating System
6- How to write script to delete records in small chunks from SQL Server Table

Script used in the video 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


How to Delete Huge Data from SQL Server Table in Small Portions

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.