Setting a trace on Delete statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Setting a trace on Delete statement

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-03-10 : 10:02:49

Hi - I have the following bit of code that I would like to put a trace on to see whereabouts or how far through the execute it has gone but I'm not sure how, any help would be great:

DECLARE
 @count INT,
 @ContinueDelete INT,
 @LimitDate1 DATETIME,
 @LimitDate2 DATETIME,
 @LimitDate3 DATETIME,
 @LimitDate4 DATETIME,
 @LimitDate5 DATETIME,
 @LimitDate6 DATETIME,
 @LimitDate7 DATETIME

--AS
SET ARITHABORT ON

SET @count = 10000
SET @LimitDate1 = GETDATE() - 7
SET @LimitDate2 = GETDATE() - 31 --30
SET @LimitDate3 = GETDATE() - 57 --60
SET @LimitDate4 = GETDATE() - 180
SET @LimitDate5 = GETDATE() - 332
SET @LimitDate6 = GETDATE() - 730
SET @LimitDate7 = '2009-01-01 22:00:00'  --2009-01-01 00:00:00

    BEGIN

		--1 Week Of Data
		DELETE FROM mp_DELFOR	WHERE DeliveryDate     < @LimitDate1
		DELETE FROM mp_ASNTesco WHERE DeliveryDateTime < @LimitDate1
		--1 Month Of Data
		DELETE FROM mp_ASNHistory	WHERE DeliveryDatTime    < @LimitDate2
		DELETE FROM mp_EPOD			WHERE ActualDeliveryDate < @LimitDate2
		--2 Months Of Data
--		DELETE FROM mp_ScannedBarcodes			WHERE RecordDate	   < (GETDATE() - 60)
		DELETE FROM mp_3rd_DespatchConfirmation WHERE ActivityDateTime < @LimitDate3
		--6 Months Of Data
		DELETE FROM mp_Libra_Orders   WHERE ActivityDateTime < @LimitDate4
		DELETE FROM mp_LogTextFiles   WHERE RecordDate       < @LimitDate4
		DELETE FROM mp_Libra_POOrders WHERE ActivityDateTime < '2010-10-07 17:00:26.000'
		--1 Year Of Data
--		DELETE FROM mp_whsStockWatchMTS2BKUP WHERE CurrDatePCC   < (GETDATE() - 365)
--		DELETE FROM mp_whsStockWatch		 WHERE CurrDatePCC   < (GETDATE() - 365)
		DELETE FROM mp_LogXML				 WHERE RecordDate < '2010-12-10 23:30:58.330'
		DELETE FROM mp_POCommunicated		 WHERE RecordDate < '2010-06-07 15:55:14.000'
		--2 Years Of Data
		DELETE FROM mp_DeliveryNoteRunDetails WHERE RunStartDate < '2009-08-10 21:09:29.283'
		DELETE FROM mp_FFIProducedRecords	  WHERE File_Creation    < '2009-10-31 00:00:00.000'
		DELETE FROM mp_CustomerReceiptDetails WHERE Key_Delivery_Note_No_And_Date IN 
					(SELECT Key_Delivery_Note_No_And_Date
					 FROM mp_CustomerReceiptHeader 
					 WHERE del_date < @LimitDate6)
		DELETE FROM mp_CustomerReceiptHeader  WHERE del_date < @LimitDate6
		--Everything from 01/01/2009
--		DELETE FROM mp_OrigOrderLineItems WHERE ReqdDelivDate < @LimitDate7
		DELETE FROM mp_OrigOrderSumry	  WHERE DateCreated   < @LimitDate7
		--100 most recent records
		DELETE DT
		FROM
			(SELECT ROW_NUMBER() OVER(ORDER BY RecordDate DESC) AS row_number, * 
			 FROM dbo.mp_sicSainsburysInvoiceableRS) AS DT
		WHERE row_number > 100
 
SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
    BEGIN       
	DELETE TOP ( @count )
	FROM    mp_ScannedBarcodes
	WHERE   RecordDate < '2011-01-09 23:58:37'

	WAITFOR DELAY '00:00:10' ;

	IF EXISTS ( SELECT  1
                 	FROM    mp_ScannedBarcodes
			WHERE   RecordDate < @LimitDate3 ) 
		BEGIN
		SET @ContinueDelete = 1
		END
	ELSE 
		BEGIN
		SET @ContinueDelete = 0
		END
	END

SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
    BEGIN 
	DELETE TOP ( @count )
	FROM    mp_whsStockWatch
	WHERE   CurrDatePCC < @LimitDate5

	WAITFOR DELAY '00:00:10' ;

	IF EXISTS ( SELECT  1
		        FROM    mp_whsStockWatch
			WHERE   CurrDatePCC < @LimitDate5 ) 
	        BEGIN
	        SET @ContinueDelete = 1
	        END
	ELSE 
		BEGIN
		SET @ContinueDelete = 0
		END
	END

SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
    BEGIN 
	DELETE TOP ( @count )
	FROM    mp_whsStockWatchMTS2BKUP
	WHERE   CurrDatePCC < '2011-02-17 10:02:00.000'

	WAITFOR DELAY '00:00:10' ;

	IF EXISTS ( SELECT  1
			FROM    mp_whsStockWatchMTS2BKUP
                 	WHERE   CurrDatePCC < '2011-02-17 10:02:00.000' ) 
		BEGIN
		SET @ContinueDelete = 1
		END
	ELSE 
		BEGIN
	        SET @ContinueDelete = 0
		END
	END

SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
    BEGIN 
	DELETE TOP ( @count )
	FROM    mp_OrigOrderLineItems
	WHERE   ReqdDelivDate < @LimitDate7

	WAITFOR DELAY '00:00:10' ;

	IF EXISTS ( SELECT  1
		        FROM    mp_OrigOrderLineItems
			WHERE   ReqdDelivDate < @LimitDate7 ) 
		BEGIN
		SET @ContinueDelete = 1
		END
	ELSE 
		BEGIN
		SET @ContinueDelete = 0
		END
	END

SET NOCOUNT OFF ;

END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-03-14 : 06:11:20

solved this by doing the following:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE
 @count INT,
 @ContinueDelete INT,
 @LimitDate1 DATETIME,
 @LimitDate2 DATETIME,
 @LimitDate3 DATETIME,
 @LimitDate4 DATETIME,
 @DeletedRows INT,
 @TotRows INT,
 @Counts1 INT,
 @ID INT


--AS
SET ARITHABORT ON

SET @count = 10000
SET @LimitDate1 = GETDATE() - 60
SET @LimitDate2 = GETDATE() - 300
SET @LimitDate3	= getdate() - 13
SET @LimitDate4 = '2009-12-01 00:00:00'
SET @TotRows = 0
SET @Counts1 = 0
SET @ID = 0

SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
	BEGIN       
		DELETE TOP ( @count )
				FROM    mp_ScannedBarcodes
				WHERE   RecordDate < @LimitDate1

			      SET @DeletedRows = @@ROWCOUNT
			      SET @TotRows = @TotRows + @DeletedRows
			      SET @Counts1 = @Counts1 + 1
		              SET @ID = @ID + 1

				WAITFOR DELAY '00:00:10' ;

				IF EXISTS ( SELECT  1
							FROM    mp_ScannedBarcodes
							WHERE   RecordDate < @LimitDate1 ) 
					BEGIN
						SET @ContinueDelete = 1
					END
				ELSE 
					BEGIN
						SET @ContinueDelete = 0
					END

					--PRINT 'Starting: INSERT INTO Delete_CRAERP'
					INSERT INTO Delete_CRAERP (DeleteHistoryID, Description, TableName, RecordCount, TotCount) VALUES (@ID, 'Delete', 'mp_ScannedBarcodes', @DeletedRows, @TotRows)

	END

SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
	BEGIN 
		DELETE TOP ( @count )
				FROM    mp_whsStockWatch
				WHERE   CurrDatePCC < @LimitDate2

			      SET @DeletedRows = @@ROWCOUNT
			      SET @TotRows = @TotRows + @DeletedRows
			      SET @Counts1 = @Counts1 + 1
			      SET @ID = @ID + 1

				WAITFOR DELAY '00:00:10' ;

				IF EXISTS ( SELECT  1
							FROM    mp_whsStockWatch
							WHERE   CurrDatePCC < @LimitDate2 ) 
					BEGIN
						SET @ContinueDelete = 1
					END
				ELSE 
					BEGIN
						SET @ContinueDelete = 0
					END

					--PRINT 'Starting: INSERT INTO Delete_CRAERP'
					INSERT INTO Delete_CRAERP (DeleteHistoryID, Description, TableName, RecordCount, TotCount) VALUES (@ID, 'Delete', 'mp_whsStockWatch', @DeletedRows, @TotRows)

	END

SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
	BEGIN 
		DELETE TOP ( @count )
				FROM    mp_whsStockWatchMTS2BKUP
				WHERE   CurrDatePCC < @LimitDate3

			      SET @DeletedRows = @@ROWCOUNT
			      SET @TotRows = @TotRows + @DeletedRows
			      SET @Counts1 = @Counts1 + 1
		              SET @ID = @ID + 1

				WAITFOR DELAY '00:00:10' ;

				IF EXISTS ( SELECT  1
							FROM    mp_whsStockWatchMTS2BKUP
							WHERE   CurrDatePCC < @LimitDate3 ) 
					BEGIN
						SET @ContinueDelete = 1
					END
				ELSE 
					BEGIN
						SET @ContinueDelete = 0
					END

					--PRINT 'Starting: INSERT INTO Delete_CRAERP'
					INSERT INTO Delete_CRAERP (DeleteHistoryID, Description, TableName, RecordCount, TotCount) VALUES (@ID, 'Delete', 'mp_whsStockWatchMTS2BKUP', @DeletedRows, @TotRows)

	END

SET @ContinueDelete = 1
WHILE @ContinueDelete > 0 
	BEGIN 
		DELETE TOP ( @count )
				FROM    mp_OrigOrderLineItems
				WHERE   ReqdDelivDate < @LimitDate4

				  SET @DeletedRows = @@ROWCOUNT
				  SET @TotRows = @TotRows + @DeletedRows
				  SET @Counts1 = @Counts1 + 1
				  SET @ID = @ID + 1

				WAITFOR DELAY '00:00:10' ;

				IF EXISTS ( SELECT  1
							FROM    mp_OrigOrderLineItems
							WHERE   ReqdDelivDate < @LimitDate4 ) 
					BEGIN
						SET @ContinueDelete = 1
					END
				ELSE 
					BEGIN
						SET @ContinueDelete = 0
					END

					--PRINT 'Starting: INSERT INTO Delete_CRAERP'
					INSERT INTO Delete_CRAERP (DeleteHistoryID, Description, TableName, RecordCount, TotCount) VALUES (@ID, 'Delete', 'mp_OrigOrderLineItems', @DeletedRows, @TotRows)

	END

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources