Solved

T-sql query delete on batch

Posted on 2013-01-18
3
428 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard 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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

630 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