Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Deleting Large Number of Records

By Lynn Pettis, (first published: 2009/09/15)

Introduction

One of the frequent questions asked on forums is how to delete a large number of records from a table in SQL Server. The question can come from several directions. Sometimes it is the result of excess growth of the database's transaction log, or that the table must remain available to users during the delete process, or simple that the user is looking for an efficient means to delete records on a regular basis.

With this, I'd like to present one method that can be used to delete records from a table that will allow the table to remain available to users and will help keep the transaction log from growing excessively. I will show how this can be done in SQL Server 2000 and SQL Server 2005/2008.

The Setup

The first thing we need to do, however, is setup the table from which we will be deleting a large number of records. Most requests for help in this area come from individuals that are asking how to delete several million rows of data from a multi-million row table. For this code we will create a table with 20 million rows of data, and we will delete approximately 10%, or around 2 million, of those rows. The test data that will be generated below will have transaction dates (TDate) from January 1, 2000 through December 31, 2009. We will purge all data from the Year 2000.

    USE [SandBox] -- This is a my test bed database
	GO
	-- Create the test table
	create tabledbo.LAPTest (
	     AccountID int,
	     Amount money,
	     TDate datetime
	)
	create clustered index IX_LAPTest_TDate on dbo.LAPTest (
TDate asc
) GO --===== Build the table 100000rows at a time to "mix things up" DECLARE @CounterINT SET @Counter = 0 WHILE @Counter < 20000000 BEGIN --===== Add 100000rows to the test table INSERT INTO dbo.LAPTest( AccountID, Amount, Date) SELECT TOP 100000 AccountID = ABS(CHECKSUM(NEWID()))%50000+1, Amount = CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY), Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0ASDATETIME) FROM master.dbo.syscolumns t1 CROSS JOINmaster.dbo.syscolumns t2 --===== Increment the counter SET @Counter = @Counter + 100000 END GO--===== Build the table 100000rows at atime to "mix things up"

Now that we have our test table and data, we are ready to start work on the code that will delete all records for the Year 2000.

SQL Server 2000

The first code snippet I will write will be for deleting the records from table in SQL Server 2000. Although this code will also work in SQL Server 2005, it will not work in future versions of SQL Server. For that reason, I will rewrite the code I develop for SQL Server 2000 to use the new capabilities available in SQL Server 2005.

I have made every effort to make this code generic, and it should work with minimal changes. As it may be used in databases that are set to use either the SIMPLE, BULK LOGGED, or FULL recovery models; I have included conditional logic to perform a transaction log backup if the database is not using the SIMPLE recovery model.

 declare @recoverymodel varchar(12), 
         @dbname sysname, 
         @backupdevice sysname, 
         @batchsize int, 
         @loopcnt int, 
         @year smallint, 
         @backupfile nvarchar(512) 
 select 
     @recoverymodel = cast(DATABASEPROPERTYEX ( db_name() , 'Recovery') as varchar(12)), 
     @dbname = db_name(), 
     @backupdevice = 'D:\Backups\', 
     @batchsize = 10000, 
     @loopcnt = 0, 
     @year = 2000 
   
 set rowcount @batchsize -- used for SQL Server 2000 to limit rows affected 
   
 while @batchsize <> 0 
 begin 
     delete from 
         dbo.LAPTest 
     where 
         TDate >= dateadd(yyyy, (@year - 1900), 0) and 
         TDate <  dateadd(yyyy, (@year - 1900) + 1, 0) 
     set @batchsize = @@rowcount 
     if @batchsize > 0 
        and @recoverymodel <> 'SIMPLE' 
         begin 
             set @loopcnt = @loopcnt + 1 
             set @backupfile = @backupdevice + @dbname + N'_' + cast(@loopcnt as nvarchar(8)) + N'.trn' 
             backup log @dbname 
             to disk = @backupfile 
             with description = N'T-Log Backup during purge' 
         end 
 end 
   
 set rowcount 0 -- reset rows affected after processing, SQL Server 2000 

SQL Server 2005

Now we will take the code above and modify it to work using the new features introduced in SQL Server 2005. This will also allow this code to be used in SQL Server 2008 and future versions as well. As you look at the code you will very little difference between the two versions. What are missing from the SQL Server 2005 version are the set rowcount statements and the addition of the TOP clause in the delete statement.

 declare @recoverymodel varchar(12), 
         @dbname sysname, 
         @backupdevice sysname, 
         @batchsize int, 
         @loopcnt int, 
         @year smallint, 
         @backupfile nvarchar(512) 
 select 
     @recoverymodel = cast(DATABASEPROPERTYEX ( db_name() , 'Recovery') as varchar(12)), 
     @dbname = db_name(), 
     @backupdevice = 'D:\Backups\', 
     @batchsize = 10000, 
     @loopcnt = 0, 
     @year = 2000 
   
 while @batchsize <> 0 
 begin 
     delete top (@batchsize) from 
         dbo.LAPTest 
     where 
         TDate >= dateadd(yyyy, (@year - 1900), 0) and 
         TDate <  dateadd(yyyy, (@year - 1900) + 1, 0) 
     set @batchsize = @@rowcount 
     if @batchsize > 0 
        and @recoverymodel <> 'SIMPLE' 
         begin 
             set @loopcnt = @loopcnt + 1 
             set @backupfile = @backupdevice + @dbname + N'_' + cast(@loopcnt as nvarchar(8)) + N'.trn' 
             backup log @dbname 
             to disk = @backupfile 
             with description = N'T-Log Backup during purge' 
         end 
 end 

Variations

Now that you have the basic generic code to delete large volumes of data from a table, you may decide that you don't want 200+ transaction log backup files should you have to do a restore to a point in time. If your database is not using the SIMPLE recovery model, you could modify the conditional used in the IF statement to decide if the transaction log backup should be run. One change you could make is to only run the transaction log backup if (@loopcnt modulo (some value) = 0 and @batchsize > 0) OR (@batchsize = 0). In addition to this logic, you could also add a differential backup after the purge is run as well.

Conclusion

With these sample code snippets, I hope you can see how easily you can batch your deletes allowing the table to remain relatively open for continued use, and at the same time keep your transaction log files manageable when your database is using the BULK LOGGED or FULL recovery model.

Total article views: 49176 | Views in the last 30 days: 81
 
Related Articles
FORUM

SQL Server job to delete old backups

SQL Server job to delete old backups

FORUM

Deleting Older backups

Deleting Older backups

FORUM

deleting backup logs

deleting backup logs

FORUM

Deleting BackUp Files

Deleting BackUp Files in UAT Farm

FORUM

Delete Backup Files

delete files within a backup (.bak) file

Tags
delete    
sql server 2000    
sql server 2005    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones