Solved

my manager wants to know how many records got deleted in the below query where can i put the count of deleted records or row count in the  variable and print it in sql server

Posted on 2011-02-16
9
1,259 Views
Last Modified: 2013-11-16
HI I want to put the count of deleted records or row count in a variable and want us to show how many records got deleted at particular point.... say for each 50000 records. there are millions of records to be deleted and we want to know status of the records where exactly the status is... could you please point me where i can pass the variable and if you don't mind could you please modify the query according to the requirement.

print 50000 records got deleted

for another 50000 records

Print 100000 records got deleted ..... like that
Below is the query::::  
--------------------------------------------------------------------------------------------------------------------------
DECLARE @BatchSize int

SET @BatchSize = 50000

Declare @loop char(1)
SET @loop = 'Y'
WHILE @loop = 'Y'
BEGIN
      BEGIN TRANSACTION

      DELETE TOP (@BatchSize) dbo.Error
      FROM dbo.Error WITH (INDEX=PKError) -- input your index name here
      WHERE Id >= 3370376 AND Id <= 342609235
      AND OriginalMessage = 'Unknown UAC:0'

      IF @@ROWCOUNT = 0 SET @loop = 'N'

      COMMIT TRANSACTION

      WAITFOR DELAY '00:00:05'
END

Thanks,
Sridhar
0
Comment
Question by:srionline2k6
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 41 total points
ID: 34912197
Use another variable.
DECLARE @BatchSize int
DECLARE @RowCount int,@RowCountTotal int
SET @BatchSize = 50000

Declare @loop char(1)
SET @loop = 'Y'
WHILE @loop = 'Y'
BEGIN
      BEGIN TRANSACTION

      DELETE TOP (@BatchSize) dbo.Error
      FROM dbo.Error WITH (INDEX=PKError) -- input your index name here
      WHERE Id >= 3370376 AND Id <= 342609235
      AND OriginalMessage = 'Unknown UAC:0'
      SELECT @RowCount = @@ROWCOUNT,@RowCountTotal = isnull(@RowCountTotal,0)+@@ROWCOUNT
      IF @RowCount = 0 SET @loop = 'N'

      COMMIT TRANSACTION

      WAITFOR DELAY '00:00:05'
END
SELECT @RowCountTotal

Open in new window

0
 

Author Comment

by:srionline2k6
ID: 34912359
Thanks for your answer. But It is showing total no of records that are deleted not for every batch or transaction. I want for every transaction..

Thanks
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34912379
Then try this.
DECLARE @BatchSize int
DECLARE @RowCount int,@RowCountTotal int
SET @BatchSize = 50000

Declare @loop char(1)
SET @loop = 'Y'
WHILE @loop = 'Y'
BEGIN
      BEGIN TRANSACTION

      DELETE TOP (@BatchSize) dbo.Error
      FROM dbo.Error WITH (INDEX=PKError) -- input your index name here
      WHERE Id >= 3370376 AND Id <= 342609235
      AND OriginalMessage = 'Unknown UAC:0'
      SELECT @RowCount = @@ROWCOUNT--,@RowCountTotal = isnull(@RowCountTotal,0)+@@ROWCOUNT
      SELECT @RowCount
      IF @RowCount = 0 SET @loop = 'N'

      COMMIT TRANSACTION

      WAITFOR DELAY '00:00:05'
END

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34912431
something like this , read it from the job output..
DECLARE @BatchSize int

SET @BatchSize = 50000

Declare @loop char(1),@starttime datetime,@deleted bigint
         ,@ctime datetime,@loopno int,@etime datetime,@rows int
SElect @loop = 'Y',@starttime=GETDATE(),@deleted=0,@loopno=0
WHILE @loop = 'Y'
BEGIN
      BEGIN TRANSACTION
      select @ctime=GETDATE(),@loopno=@loopno+1
      DELETE TOP (@BatchSize) dbo.Error
      FROM dbo.Error WITH (INDEX=PKError) -- input your index name here
      WHERE Id >= 3370376 AND Id <= 342609235
      AND OriginalMessage = 'Unknown UAC:0'

      select @rows=@@ROWCOUNT,@etime=GETDATE()
      IF @@ROWs = 0 SET @loop = 'N'

      COMMIT TRANSACTION
      set @deleted=@deleted+@rows
      
      Print 'Cycle:('+Right('0000'+convert(varchar(4),@loopno),4)+'):'
            +'Start:'+convert(varchar,@ctime)+' End:'+convert(varchar,@etime)+' Deleted:'+convert(char(6),@rows)
            +' rows total=('+convert(varchar(10),@deleted)+'):'
            
      WAITFOR DELAY '00:00:05'
END
Print ' '
Print 'Start:'+convert(varchar,@starttime)+' End:'+convert(varchar,getdate)+' Deleted:('+convert(varchar(10),@deleted)+') rows:'
Print 'In 'convert(varchar(10),@loopno)+' cycles.'
Print ' '

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:srionline2k6
ID: 34912675
@sharath -- thanks for the script but its only showing 50000 rows got deleted my rek is to show lets say after 5 batches it should show 5 times 50000. you almost reached though.

@lowfat-- Hi thanks for your prompt response. I think you were the guy who solved my prob almost yesterday. the above query is giving the following error. could you please point why ? for me the query looks so complicated but hats off to you for puttiing up so much effort. kudos. But I want to know why i got the error?

Msg 128, Level 15, State 1, Line 34
The name "getdate" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 42 total points
ID: 34912691
Just in case you are not familiar on dealing with huge output.
I recommend you just create table or temp table, and keep output into it.

-- create global temp table, or create REAL table, this is up to your choice.
-- you may define more field, if you want to know something more, for ex. last deleted time
CREATE TABLE ##DeletedStatus (id int PRIMARY KEY,TotalDeleted int)
INSERT INTO ##DeletedStatus VALUES (1, 0)


DECLARE @BatchSize int
DECLARE @RowCount int -- define some more variable, if you expect to get more details
SET @BatchSize = 50000

declare @loop char(1)
SET @loop = 'Y'
WHILE @loop = 'Y'
BEGIN
	BEGIN TRANSACTION

	DELETE TOP (@BatchSize) dbo.Error
	FROM dbo.Error WITH (INDEX=PK_Error) -- input your index name here
	WHERE Id >= 3370376 AND Id <= 342609235
	AND OriginalMessage = 'Unknown UAC:0'

	SELECT @RowCount = @@ROWCOUNT -- and others thing, for ex. time, like in Lowfatspread's comment
	
	IF @RowCount = 0 SET @loop = 'N'

	UPDATE ##DeletedStatus
	SET TotalDeleted = TotalDeleted + @RowCount -- again, and others thing
	WHERE id = 1

	COMMIT TRANSACTION

	WAITFOR DELAY '00:00:05'
END

--in other window or sql session, run below query to see status
--SELECT * FROM ##DeletedStatus

Open in new window


to see status, just SELECT * FROM <temp table or table name> from other session/windows
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34912704
Remark
edit the index name again (PK_Error -> PKError) , since I go back to copy the code from my previous answer.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34912711
Run this. After your loop execution, you will get the no. of records for each batch.
DECLARE @BatchSize int
DECLARE @RowCount int
declare @RowCountTable table (ID int identity, Row_Count int)
SET @BatchSize = 50000

Declare @loop char(1)
SET @loop = 'Y'
WHILE @loop = 'Y'
BEGIN
      BEGIN TRANSACTION

      DELETE TOP (@BatchSize) dbo.Error
      FROM dbo.Error WITH (INDEX=PKError) -- input your index name here
      WHERE Id >= 3370376 AND Id <= 342609235
      AND OriginalMessage = 'Unknown UAC:0'
      SELECT @RowCount = @@ROWCOUNT
      INSERT @RowCountTable 
      SELECT @RowCount
      IF @RowCount = 0 SET @loop = 'N'

      COMMIT TRANSACTION

      WAITFOR DELAY '00:00:05'
END
SELECT * FROM @RowCountTable

Open in new window

0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 42 total points
ID: 34912973
this is all very simple stuff!


getdate requires empty brackets -- its a function


Print 'Start:'+convert(varchar,@starttime)+' End:'+convert(varchar,getdate())+' Deleted:('+convert(varchar(10),@deleted)+') rows:'
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now