Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

T-sql query delete on batch

Posted on 2013-01-18
3
Medium Priority
?
431 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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