• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

T-sql query delete on batch

I have following T-sql which executed daily. I wonder how I want to get some status of this query whether it is running or stuck somewhere. I means at least it told me how many rows or at what timestamp have the query performed.

WHILE 1 = 1 BEGIN
     BEGIN TRANSACTION
      DELETE TOP(1000)
      FROM MyviTable  where timestamp < getdate() -70

      SET @i = @@ROWCOUNT
      commit transaction
      IF @i = 0 BREAK
      --PRINT '#
0
motioneye
Asked:
motioneye
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you would need to implement what in oracle is known as "autonomeous transaction":
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

so you could "log" the steps from within the loop to outside the loop, and follow that one
0
 
RehanYousafCommented:
You can try something like this as well

DECLARE @i INT
DECLARE @deleted INT
DECLARE @left INT

DECLARE @DeleteData TABLE (
	id INT
)

WHILE 1 = 1 
BEGIN 
	BEGIN TRANSACTION
		DELETE TOP(100) 
		FROM dbo.TableA  
		OUTPUT deleted.id
		INTO @DeleteData
		WHERE id >= 10


		SET @i = @@ROWCOUNT
	COMMIT TRANSACTION
	
	IF @i = 0 BREAK
	
	SELECT @deleted = COUNT(id) FROM @DeleteData
	SELECT @left = COUNT(*) FROM dbo.TableA  WHERE id >= 10
	
    PRINT 'Deleted = ' + CONVERT(VARCHAR, @deleted) + ' / ' + CONVERT(VARCHAR, @left) 
END

Open in new window


if you provide me with your table schema ... i can update the query for you
0
 
krtyknmsqlCommented:
This will give you total number of records deleted.

DECLARE @i INT
DECLARE @d INT

SET @d = 0

WHILE 1 = 1 
BEGIN 
	BEGIN TRANSACTION
	  DELETE TOP(1000) 
      FROM MyviTable  WHERE timestamp < getdate() -70

      SET @i = @@ROWCOUNT
	  SET @d = @d + @i
	COMMIT TRANSACTION
	
	IF @i = 0 BREAK
	
    PRINT CAST(@d AS VARCHAR(50)) + ' Total records deleted'
END

Open in new window

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now