Solved

T-sql query delete on batch

Posted on 2013-01-18
3
427 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 250 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 250 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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