Improve company productivity with a Business Account.Sign Up

x
?
Solved

T-sql query delete on batch

Posted on 2013-01-18
3
Medium Priority
?
435 Views
Last Modified: 2013-01-28
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
Comment
Question by:motioneye
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38792202
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
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 1000 total points
ID: 38792242
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
 
LVL 4

Accepted Solution

by:
krtyknmsql earned 1000 total points
ID: 38792542
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question