Not long a go I got a request to bring one of the databases from the production environment to the test environment. This database is
500GB; not huge but still too big to fit in our test environment. So it was necessary to delete the data we didn’t need for testing. There were multiple tables I needed to delete from, and I just wanted something to run that I could forget about and comeback to rather than having to run a delete for each table. So I made up the script below. You can change the size of the batch as I found that 1000000 gave the best performance (FYI: deleting fewer than 5000 rows per loop removes the need for a table lock… anything greater locks the table.)
-- 2 different delete statements: -- the rowcount method is deprecated in future versions. -- the alternative is to use "SELECT TOP" used in the CTE -- Both still work the same way; if the @@rowcount (returns the number of rows affected by the last statement) is 0 then there are no more rows to delete -- we loop through as there are billions of rows to delete, so if script fails for whatever reason we only lose last run SET NOCOUNT ON --variables which we are deleting by DECLARE @ProductId nvarchar (32) DECLARE @SalesId int --store the name of the table we are going to delete DECLARE @tblname NVARCHAR (128) --we require different delete statements for each schema --in our database. This works quite nicely as we don't have to get --clever with deciding which delete statement to run DECLARE @schema nvarchar (32) --counting through total number of clients in the tables --could use a cursor here but either way is ok DECLARE @count INT [email protected] variable to execute by DECLARE @sql nvarchar (MAX) -- checking if temp table exists form previous run and dropping if it does IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#tablesToDelete')) DROP TABLE #tablesToDelete; -- put list of tabels you want to delete from here CREATE TABLE #tablesToDelete ([TblName] nvarchar (128)) INSERT INTO #tablesToDelete VALUES('Sales.SalesNumbers') INSERT INTO #tablesToDelete VALUES('Products.Products') INSERT INTO #tablesToDelete VALUES('Bob.NoSchema') SELECT @count = COUNT (*) from #tablesToDelete while @count > 0 BEGIN SELECT TOP 1 @tblname = [TblName] from #tablesToDelete SET @schema = SUBSTRING (@tblName, 1, CAST (CHARINDEX ('.', @tblname) -1 AS INT)) SET @SalesId = 85 SET @ProductId = '2, 21, 9, 20' IF @schema = 'Sales' BEGIN set @sql = ' USE MontyDev DECLARE @continue INT DECLARE @rowcount INT SET @continue = 1 WHILE @continue = 1 BEGIN PRINT GETDATE() SET ROWCOUNT 1000000 delete from '[email protected]tblname+' where tagId < '+CAST(@SalesId AS nvarchar)+' SET @rowcount = @@rowcount PRINT GETDATE() IF @rowcount = 0 BEGIN SET @continue = 0 END END' EXEC (@sql) END ELSE IF @schema = 'Products' BEGIN set @sql = ' USE MontyDev DECLARE @continue INT DECLARE @rowcount INT SET @continue = 1 WHILE @continue = 1 BEGIN PRINT GETDATE() ;WITH CTE AS ( select top 1000000 * from '[email protected]tblname+' where clientId NOT in ( '+CAST(@ProductId AS nvarchar)+') and tagId < '+CAST(@SalesId AS nvarchar)+ ' order by [ProductId] ASC, [SalesId] ASC ) DELETE FROM CTE SET @rowcount = @@rowcount PRINT GETDATE() IF @rowcount = 0 BEGIN SET @continue = 0 END END ' PRINT @tblname EXEC (@SQL) END ELSE PRINT 'Delete query not defined for schema '[email protected]schema+'!' DELETE FROM #tablesToDelete where [TblName] = @tblname SET @count = @count - 1 END SET NOCOUNT OFF
Any questions/observations/criticisms always welcome via the comments or Twitter.