Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1318
  • Last Modified:

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

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
srionline2k6
Asked:
srionline2k6
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
SharathData EngineerCommented:
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
 
srionline2k6Author Commented:
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
 
SharathData EngineerCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LowfatspreadCommented:
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
 
srionline2k6Author Commented:
@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
 
JoeNuvoCommented:
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
 
JoeNuvoCommented:
Remark
edit the index name again (PK_Error -> PKError) , since I go back to copy the code from my previous answer.
0
 
SharathData EngineerCommented:
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
 
LowfatspreadCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now