locked
WHILE Loop with WAITFOR DELAY RRS feed

  • Question

  • This question is related to the one in this link http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c4fabf14-572a-4f24-84c8-7f1bba1558f3

    A few days ago I asked a question which is related to this one. I received wonderful responses. However, the business requirement has since had extra logic to be added on my query. I have tried a few options to address that but there are some issues with my solutions.

    The additional business requirement is that while we delete records which are more than 90days old from the Date data was loaded, the deletion process should not LOCK the tables during the whole process.

    The query below seems to be doing the deletion job but I have two problems:

    1. The loop seems to be running unendingly even after it finishes with the tables. When I include BREAK (which is commented out in my query below) the process runs deletes on only one table and then stops the job.
    2. How best can I include WAITFOR DELAY in my query?

    I would appreciate if someone would panel beat my query so that

    1. The loop will go through all the tables in the database and then stops after going through them all. There are more than 40 tables.
    2. As deletions happen there should be some WAITFOR DELAY pauses in the process to avoid locking.

    Below is the query that I’m currently using.

    DECLARE @DELETECOUNT INT = 10000     -- Nibbling deletes

    DECLARE @ArchiveDBTables TABLE (ID INT IDENTITY (1,1), TableName VARCHAR (250))

    DECLARE @TableName VARCHAR (250)

    INSERT INTO @ArchiveDBTables

                SELECT

                  

                      QUOTENAME(TABLE_SCHEMA) +

                      N'.' +

                      QUOTENAME(TABLE_NAME)

                FROM

                      INFORMATION_SCHEMA.TABLES

                WHERE

                      TABLE_TYPE = 'BASE TABLE' AND

                      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +

                            N'.' +

                            QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

           

    WHILE 1 = 1

    BEGIN

          SET @TableName = (SELECT TOP 1 TableName FROM @ArchiveDBTables)

          DECLARE @q VARCHAR(250) = 'DELETE FROM ' + @TableName + ' WHERE DataLoadDate < DATEADD(DD,-90,GETDATE ())'

          EXEC (@q)

         

          DELETE FROM @ArchiveDBTables WHERE TableName = @TableName

          --SELECT @DELETECOUNT = @@ROWCOUNT

          --WAITFOR DELAY '000:00:00.400'    

          --BREAK          

    END


    Mpumelelo

    Thursday, April 19, 2012 9:30 AM

Answers

  • The following blog post is on similar topic:

    http://www.sqlusa.com/bestpractices2005/administration/deleteinbatches/

    I suggest you use sp_executeSQL instead of EXEC for dynamic SQL execution.

    The statement below looks real unusual, I hope it works:

        IF Count(@TableName) < Isnull(1, 1) 
            BREAK 


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Mpumelelo S Friday, April 27, 2012 11:16 AM
    • Edited by Kalman Toth Monday, October 8, 2012 9:17 AM
    Tuesday, April 24, 2012 11:05 PM
  • I'm now done with this. This is now a stored procedure which looks like this.

    ALTER PROCEDURE upArchiveDBHousekeeping

           @CutOffDate DATETIME, --old data with DataLoadDate less than this date will be deleted

           @BatchDelete int --number of rows to be deleted per loop

    AS

    BEGIN

    /*Create a table variable to hold a list of the table names in ARCHIVE database*/

    DECLARE @ArchiveDBTables TABLE (ID INT IDENTITY (1,1), TableName VARCHAR (250))

    DECLARE @TableName VARCHAR (250)

    DECLARE @SQLStatement NVARCHAR(400)

    /*Populate @ArchiveDBTables*/

    INSERT INTO @ArchiveDBTables

                SELECT

                      QUOTENAME(TABLE_SCHEMA) +

                      '.' +

                      QUOTENAME(TABLE_NAME)

                FROM

                      INFORMATION_SCHEMA.TABLES

                WHERE

                      TABLE_TYPE = 'BASE TABLE' AND

                      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +

                            '.' +

                            QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

           

    /*Delete in batches from each table with a 4secs pause between each batch to avoid extended table locking*/

    WHILE 1 = 1

    BEGIN

          WAITFOR DELAY '000:00:00.400'

          SET @TableName = (SELECT TOP 1 TableName FROM @ArchiveDBTables)

          SET @SQLStatement = 'DELETE TOP (' + CAST(@BatchDelete AS VARCHAR (10)) + ') FROM ' + @TableName + ' WHERE DataLoadDate <''' + CONVERT(VARCHAR(23),@CutOffDate,120)+''''

          EXEC sp_executeSQL @SQLStatement

          IF @@ROWCOUNT < 500

          DELETE FROM @ArchiveDBTables WHERE TableName = @TableName

          IF COUNT(@TableName) < ISNULL(1,1) BREAK

    END

    END

    GO


    Mpumelelo

    • Marked as answer by Mpumelelo S Friday, April 27, 2012 11:18 AM
    Friday, April 27, 2012 11:16 AM
  • I have done further modification to my stored procedure. Hope this will be useful to other forum users and to me in the future. Please note that this modified procedure uses a permanent table called dbo.Housekeeping_Reference instead of a table variable (@ArchiveDBTables) which the other procedure uses.

    The structure of dbo.Housekeeping_Reference table is summarised by the Create Table statement below. This addresses the business requirement at my workplace.

    CREATE TABLE dbo.Housekeeping_Reference(

          [TableSchemaTableName] [varchar](500) NULL,

          [DeleteCounter] [int] DEFAULT ((10000)) NULL,

          [CutOffDays] [int] DEFAULT ((90)) NULL,

          [CutOff]  AS (getdate()-[CUTOFFDAYS]),

          [Status] [smallint] DEFAULT ((1)) NULL

    )

    GO

    As you can see, table dbo.Housekeeping_Reference has a column called [Status] which determines if looping has happened to a given row. Please see the procedure below for the logic.

    ALTER PROCEDURE dbo.upArchiveDBHousekeeping 
    AS
    BEGIN
    /*Check if there are any new tables, if so add them to the reference table*/
    ;WITH AvailableTables (TableSchemaTableName) AS
    (SELECT
        QUOTENAME(TABLE_SCHEMA) +
        '.' +
        QUOTENAME(TABLE_NAME)
     FROM
        INFORMATION_SCHEMA.TABLES
     )
      MERGE INTO [dbo].[Housekeeping_Reference] H  
    	USING ( SELECT TableSchemaTableName
                FROM AvailableTables) T 
    	ON (H.TableSchemaTableName = T.TableSchemaTableName)
      WHEN NOT MATCHED THEN	INSERT (TableSchemaTableName) VALUES(TableSchemaTableName);
    /*Flatten the status of all rows to zero*/
    UPDATE dbo.Housekeeping_Reference SET [Status] = 0
              
    DECLARE @DeleteValueTableSchemaTableName VARCHAR (500)
    DECLARE @DeleteValueSQLStatement NVARCHAR(4000)
    DECLARE @TableSchemaTableName VARCHAR (500)
    DECLARE @CutOffDate DATETIME
    DECLARE @BatchDelete INT
    DECLARE @SQLStatement NVARCHAR(4000)
    /*Assign delete batches values for each table*/
    WHILE 1 = 1
    BEGIN
          SET @DeleteValueTableSchemaTableName = (SELECT TOP 1 TableSchemaTableName FROM dbo.Housekeeping_Reference WHERE [Status] = 0) --AND TableSchemaTableName <> '[dbo].[Housekeeping_Reference]')
          SET @DeleteValueSQLStatement = 'UPDATE dbo.Housekeeping_Reference SET DeleteCounter = CASE WHEN TableSchemaTableName = ''' + @DeleteValueTableSchemaTableName +''' 
              AND (SELECT COUNT(*) FROM ' + @DeleteValueTableSchemaTableName + ' ) BETWEEN 1000000 AND 5000000 THEN 100000 
              WHEN  (TableSchemaTableName = ''' + @DeleteValueTableSchemaTableName +''' 
              AND (SELECT COUNT(*) FROM ' + @DeleteValueTableSchemaTableName + ' ) > 5000000) THEN 1000000 ELSE 100000 END
              WHERE TableSchemaTableName = ''' + @DeleteValueTableSchemaTableName + ''''
          EXEC sp_executeSQL @DeleteValueSQLStatement
          UPDATE dbo.Housekeeping_Reference SET [Status] = 1 WHERE TableSchemaTableName = @DeleteValueTableSchemaTableName
          IF (SELECT COUNT([Status]) FROM dbo.Housekeeping_Reference WHERE [Status] = 0) = 0 BREAK
    END
    /*Delete in batches from each table with a 4secs pause between each batch to avoid extended table locking*/
    WHILE 1 = 1
    BEGIN
          WAITFOR DELAY '00:00:00.400'
          SET @TableSchemaTableName = (SELECT TOP 1 TableSchemaTableName FROM dbo.Housekeeping_Reference WHERE [Status] = 1 AND TableSchemaTableName <> '[dbo].[Housekeeping_Reference]')
          SET @CutOffDate = (SELECT CutOff FROM dbo.Housekeeping_Reference WHERE TableSchemaTableName = @TableSchemaTableName)
          SET @BatchDelete = (SELECT DeleteCounter FROM dbo.Housekeeping_Reference WHERE TableSchemaTableName = @TableSchemaTableName)
          SET @SQLStatement = 'DELETE TOP (' + CAST(@BatchDelete AS VARCHAR (10)) + ') FROM ' + @TableSchemaTableName + ' WHERE DataLoadDate <''' + CONVERT(VARCHAR(23),@CutOffDate,120)+''''
          EXEC sp_executeSQL @SQLStatement
          IF @@ROWCOUNT < 500
          UPDATE dbo.Housekeeping_Reference SET [Status] = 0 WHERE TableSchemaTableName = @TableSchemaTableName 
          IF (SELECT COUNT([Status]) FROM dbo.Housekeeping_Reference WHERE [Status] = 1) <= 1 BREAK
    END
    END
    GO


    • Marked as answer by Mpumelelo S Tuesday, May 1, 2012 10:04 AM
    • Edited by Mpumelelo S Tuesday, July 31, 2012 11:15 AM
    Tuesday, May 1, 2012 10:04 AM

All replies

  • Hi 

    What was wrong with my reply? How large data you are going to delete... Perhaps the better option is to do that separately  and notwithin a loop

    http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, April 19, 2012 9:46 AM
    Answerer
  • Hi Uri

    Nothing was wrong with your reply. The only problem is that the approach that you gave me cannot use WAITFOR DELAY, as I understand it. Your query worked (and works) perfectly well to be honest but I was told that it would be nicer if the deletions are done in small chunks – nibbling deletions – so as to avoid locking the tables. I work for a very large organisation and our data normally is in tens, if not hundreds of millions.

    Do you have anymore suggestions for me. A sample query will do as I have burnt my brains out in trying to make this work.

    Thanks.


    Mpumelelo


    • Edited by Mpumelelo S Thursday, April 19, 2012 10:01 AM
    Thursday, April 19, 2012 9:59 AM
  • I don't think, that a WaitForDelay helps you to delete the data in small chunks. I think, you need somthing like this:

    		SELECT @SQL = N'WHILE 1=1 BEGIN 
    				DELETE  TOP (30000) ' + @TableName + ' FROM ' + @TableName + ' 
    				t INNER JOIN tmp_Datum d ON t.DAY = d.DAY and 
    				t.MONTH = d.MONTH and t.YEAR = d.YEAR WHERE d.KillNow = 1
    				AND (d.TableToDelete = '''+ @TableName + ''') 
    				If @@rowcount < 5000 break END'
    But I'm not shure that this will prevend from locking the whole table.
    Thursday, April 19, 2012 11:29 AM
  • what edition of SQL Server are you using ?

    if you're using Enterprise Edition of SQL Server 2005 or higher you should consider to use partitioning as you can than drop data in seconds without filling the transaction log.

    see this Microsoft Whitepaper about Partitioning Partitioned Table and Index Strategies Using SQL Server 2008

    (downloadable Word document at the end of the page)

    Thursday, April 19, 2012 11:56 AM
  • Christa – thank you for your suggestion. I’m still trying to study your statement so that I can understand it.

    Daniel – that sounds like a good idea. I will bring it forth to my seniors and see if they will be happy with it for this particular task under discussion.


    Mpumelelo

    Thursday, April 19, 2012 12:07 PM
  • I think I have managed to fix my query to give me want I want. Please find below a modified version which appears to be working well. There is no more unending looping and I think the WAITFOR DELAY is working too. Any suggestions of improvement will be welcome.

    DECLARE @ArchiveDBTables TABLE (ID INT IDENTITY (1,1), TableName VARCHAR (250))

    DECLARE @TableName VARCHAR (250)

    INSERT INTO @ArchiveDBTables

                SELECT

                      QUOTENAME(TABLE_SCHEMA) +

                      N'.' +

                      QUOTENAME(TABLE_NAME)

                FROM

                      INFORMATION_SCHEMA.TABLES

                WHERE

                      TABLE_TYPE = 'BASE TABLE' AND

                      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +

                            N'.' +

                            QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

           

    WHILE 1 = 1

    BEGIN

          WAITFOR DELAY '000:00:00.400'

          SET @TableName = (SELECT TOP 1 TableName FROM @ArchiveDBTables)

          DECLARE @q VARCHAR(250) = 'DELETE TOP (10000)FROM ' + @TableName + ' WHERE DataLoadDate < DATEADD(DD,-90,GETDATE ())'

          EXEC (@q)

          IF @@ROWCOUNT < 500

          DELETE FROM @ArchiveDBTables WHERE TableName = @TableName

          IF COUNT(@TableName) < ISNULL(1,1) BREAK

    END


    Mpumelelo


    • Edited by Mpumelelo S Thursday, April 19, 2012 3:57 PM
    Thursday, April 19, 2012 3:57 PM
  • The following blog post is on similar topic:

    http://www.sqlusa.com/bestpractices2005/administration/deleteinbatches/

    I suggest you use sp_executeSQL instead of EXEC for dynamic SQL execution.

    The statement below looks real unusual, I hope it works:

        IF Count(@TableName) < Isnull(1, 1) 
            BREAK 


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Mpumelelo S Friday, April 27, 2012 11:16 AM
    • Edited by Kalman Toth Monday, October 8, 2012 9:17 AM
    Tuesday, April 24, 2012 11:05 PM
  • Hi SQLUSA

    I feel bad about my seemingly confused approach to this. I have realised that I need to understand more about sp_executesql and EXEC () as they are new concepts to me in my experience. I’m still growing as a developer and I realise that there is almost always something new to learn each time I’m beset by challenging scenarios. I hope to come up with a robust code to address my situation. The one given in my earlier post appears to be working. But as you have suggested I will try to amend it to sp_executesql . Thank you for all the pointers that I get from this and other forums.

    Many thanks,

    Mpumelelo

    • Edited by Mpumelelo S Thursday, April 26, 2012 11:40 AM
    Thursday, April 26, 2012 11:38 AM
  • To those reading this thread who might be new to dynamic SQL like myself I have found the links below very helpful:

    http://www.sommarskog.se/dynamic_sql.html

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Mpumelelo

    Thursday, April 26, 2012 1:08 PM
  • I'm now done with this. This is now a stored procedure which looks like this.

    ALTER PROCEDURE upArchiveDBHousekeeping

           @CutOffDate DATETIME, --old data with DataLoadDate less than this date will be deleted

           @BatchDelete int --number of rows to be deleted per loop

    AS

    BEGIN

    /*Create a table variable to hold a list of the table names in ARCHIVE database*/

    DECLARE @ArchiveDBTables TABLE (ID INT IDENTITY (1,1), TableName VARCHAR (250))

    DECLARE @TableName VARCHAR (250)

    DECLARE @SQLStatement NVARCHAR(400)

    /*Populate @ArchiveDBTables*/

    INSERT INTO @ArchiveDBTables

                SELECT

                      QUOTENAME(TABLE_SCHEMA) +

                      '.' +

                      QUOTENAME(TABLE_NAME)

                FROM

                      INFORMATION_SCHEMA.TABLES

                WHERE

                      TABLE_TYPE = 'BASE TABLE' AND

                      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +

                            '.' +

                            QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

           

    /*Delete in batches from each table with a 4secs pause between each batch to avoid extended table locking*/

    WHILE 1 = 1

    BEGIN

          WAITFOR DELAY '000:00:00.400'

          SET @TableName = (SELECT TOP 1 TableName FROM @ArchiveDBTables)

          SET @SQLStatement = 'DELETE TOP (' + CAST(@BatchDelete AS VARCHAR (10)) + ') FROM ' + @TableName + ' WHERE DataLoadDate <''' + CONVERT(VARCHAR(23),@CutOffDate,120)+''''

          EXEC sp_executeSQL @SQLStatement

          IF @@ROWCOUNT < 500

          DELETE FROM @ArchiveDBTables WHERE TableName = @TableName

          IF COUNT(@TableName) < ISNULL(1,1) BREAK

    END

    END

    GO


    Mpumelelo

    • Marked as answer by Mpumelelo S Friday, April 27, 2012 11:18 AM
    Friday, April 27, 2012 11:16 AM
  • I have done further modification to my stored procedure. Hope this will be useful to other forum users and to me in the future. Please note that this modified procedure uses a permanent table called dbo.Housekeeping_Reference instead of a table variable (@ArchiveDBTables) which the other procedure uses.

    The structure of dbo.Housekeeping_Reference table is summarised by the Create Table statement below. This addresses the business requirement at my workplace.

    CREATE TABLE dbo.Housekeeping_Reference(

          [TableSchemaTableName] [varchar](500) NULL,

          [DeleteCounter] [int] DEFAULT ((10000)) NULL,

          [CutOffDays] [int] DEFAULT ((90)) NULL,

          [CutOff]  AS (getdate()-[CUTOFFDAYS]),

          [Status] [smallint] DEFAULT ((1)) NULL

    )

    GO

    As you can see, table dbo.Housekeeping_Reference has a column called [Status] which determines if looping has happened to a given row. Please see the procedure below for the logic.

    ALTER PROCEDURE dbo.upArchiveDBHousekeeping 
    AS
    BEGIN
    /*Check if there are any new tables, if so add them to the reference table*/
    ;WITH AvailableTables (TableSchemaTableName) AS
    (SELECT
        QUOTENAME(TABLE_SCHEMA) +
        '.' +
        QUOTENAME(TABLE_NAME)
     FROM
        INFORMATION_SCHEMA.TABLES
     )
      MERGE INTO [dbo].[Housekeeping_Reference] H  
    	USING ( SELECT TableSchemaTableName
                FROM AvailableTables) T 
    	ON (H.TableSchemaTableName = T.TableSchemaTableName)
      WHEN NOT MATCHED THEN	INSERT (TableSchemaTableName) VALUES(TableSchemaTableName);
    /*Flatten the status of all rows to zero*/
    UPDATE dbo.Housekeeping_Reference SET [Status] = 0
              
    DECLARE @DeleteValueTableSchemaTableName VARCHAR (500)
    DECLARE @DeleteValueSQLStatement NVARCHAR(4000)
    DECLARE @TableSchemaTableName VARCHAR (500)
    DECLARE @CutOffDate DATETIME
    DECLARE @BatchDelete INT
    DECLARE @SQLStatement NVARCHAR(4000)
    /*Assign delete batches values for each table*/
    WHILE 1 = 1
    BEGIN
          SET @DeleteValueTableSchemaTableName = (SELECT TOP 1 TableSchemaTableName FROM dbo.Housekeeping_Reference WHERE [Status] = 0) --AND TableSchemaTableName <> '[dbo].[Housekeeping_Reference]')
          SET @DeleteValueSQLStatement = 'UPDATE dbo.Housekeeping_Reference SET DeleteCounter = CASE WHEN TableSchemaTableName = ''' + @DeleteValueTableSchemaTableName +''' 
              AND (SELECT COUNT(*) FROM ' + @DeleteValueTableSchemaTableName + ' ) BETWEEN 1000000 AND 5000000 THEN 100000 
              WHEN  (TableSchemaTableName = ''' + @DeleteValueTableSchemaTableName +''' 
              AND (SELECT COUNT(*) FROM ' + @DeleteValueTableSchemaTableName + ' ) > 5000000) THEN 1000000 ELSE 100000 END
              WHERE TableSchemaTableName = ''' + @DeleteValueTableSchemaTableName + ''''
          EXEC sp_executeSQL @DeleteValueSQLStatement
          UPDATE dbo.Housekeeping_Reference SET [Status] = 1 WHERE TableSchemaTableName = @DeleteValueTableSchemaTableName
          IF (SELECT COUNT([Status]) FROM dbo.Housekeeping_Reference WHERE [Status] = 0) = 0 BREAK
    END
    /*Delete in batches from each table with a 4secs pause between each batch to avoid extended table locking*/
    WHILE 1 = 1
    BEGIN
          WAITFOR DELAY '00:00:00.400'
          SET @TableSchemaTableName = (SELECT TOP 1 TableSchemaTableName FROM dbo.Housekeeping_Reference WHERE [Status] = 1 AND TableSchemaTableName <> '[dbo].[Housekeeping_Reference]')
          SET @CutOffDate = (SELECT CutOff FROM dbo.Housekeeping_Reference WHERE TableSchemaTableName = @TableSchemaTableName)
          SET @BatchDelete = (SELECT DeleteCounter FROM dbo.Housekeeping_Reference WHERE TableSchemaTableName = @TableSchemaTableName)
          SET @SQLStatement = 'DELETE TOP (' + CAST(@BatchDelete AS VARCHAR (10)) + ') FROM ' + @TableSchemaTableName + ' WHERE DataLoadDate <''' + CONVERT(VARCHAR(23),@CutOffDate,120)+''''
          EXEC sp_executeSQL @SQLStatement
          IF @@ROWCOUNT < 500
          UPDATE dbo.Housekeeping_Reference SET [Status] = 0 WHERE TableSchemaTableName = @TableSchemaTableName 
          IF (SELECT COUNT([Status]) FROM dbo.Housekeeping_Reference WHERE [Status] = 1) <= 1 BREAK
    END
    END
    GO


    • Marked as answer by Mpumelelo S Tuesday, May 1, 2012 10:04 AM
    • Edited by Mpumelelo S Tuesday, July 31, 2012 11:15 AM
    Tuesday, May 1, 2012 10:04 AM