February 28, 2017 at 11:08 am
Hey everyone, one of my co-workers gave me this loop to work with on a large delete job. But DB Visualizer is throwing me all kinds of errors and I cant figure it out. Help?
I've got three errors that say "Must declare the scalar variable @r"
One says "near TRANSACTION"
One says COMMIT TRANSACTION has no corresponding BEGIN TRANSACTION
SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (10000)
FROM dbo.table
WHERE Batch > 11111;
SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
CHECKPOINT;
END
February 28, 2017 at 11:31 am
joe.dot - Tuesday, February 28, 2017 11:08 AMHey everyone, one of my co-workers gave me this loop to work with on a large delete job. But DB Visualizer is throwing me all kinds of errors and I cant figure it out. Help?
I've got three errors that say "Must declare the scalar variable @r"
One says "near TRANSACTION"
One says COMMIT TRANSACTION has no corresponding BEGIN TRANSACTIONSET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;DELETE TOP (10000)
FROM dbo.table
WHERE Batch > 11111;SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
CHECKPOINT;
END
There's no error in that code.
February 28, 2017 at 11:34 am
That's what I keep thinking. My co-worker said it should work just fine. I think a lot of it was copied off a forum or website.
Note: DBVisualizer throws these errors I mentioned, but SSMS does not. SSMS just sits there and says "executing" for a long time and then times out. I'm completely lost here.
February 28, 2017 at 12:00 pm
joe.dot - Tuesday, February 28, 2017 11:34 AMThat's what I keep thinking. My co-worker said it should work just fine. I think a lot of it was copied off a forum or website.Note: DBVisualizer throws these errors I mentioned, but SSMS does not. SSMS just sits there and says "executing" for a long time and then times out. I'm completely lost here.
How many rows are being deleted?
If there are millions, it will take a long time. I'd suggest setting it up as a SQL Agent job and running it there, to avoid any timeouts. You may also want to think about adding a WAITFOR DELAY in there to reduce the locking on the table.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2017 at 12:01 pm
joe.dot - Tuesday, February 28, 2017 11:34 AMThat's what I keep thinking. My co-worker said it should work just fine. I think a lot of it was copied off a forum or website.Note: DBVisualizer throws these errors I mentioned, but SSMS does not. SSMS just sits there and says "executing" for a long time and then times out. I'm completely lost here.
That could be for a number of reasons, unfortunately we don't know enough about your target dataset, the setup etc.
If you can build ranges according to an incremental key or date ranges, try deleting for smaller sets maybe. WHERE Batch > 11111; could be a huge set by itself, we don't know.
Other factors will include locking, blocking.
Set your results from grid to text and see if it returns any deleted row counts, or add a line to show current @r.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 28, 2017 at 12:22 pm
joe.dot - Tuesday, February 28, 2017 11:34 AMThat's what I keep thinking. My co-worker said it should work just fine. I think a lot of it was copied off a forum or website.Note: DBVisualizer throws these errors I mentioned, but SSMS does not. SSMS just sits there and says "executing" for a long time and then times out. I'm completely lost here.
I've never seen SSMS to timeout when executing a query, I've seen it run for over a day.
If you're deleting a large number of rows, you might want to check the Query options. Right click any where on the query window, select Query Options and check that Execution time-out is set to 0.
You might also want to remove the BEGIN TRANSACTION/COMMIT TRANSACTION as they have no real use there.
Also check for any open transactions that might have been left in previous runs.
February 28, 2017 at 12:38 pm
Luis Cazares - Tuesday, February 28, 2017 12:22 PMI've never seen SSMS to timeout when executing a query, I've seen it run for over a day.
If you're deleting a large number of rows, you might want to check the Query options. Right click any where on the query window, select Query Options and check that Execution time-out is set to 0.You might also want to remove the BEGIN TRANSACTION/COMMIT TRANSACTION as they have no real use there.
Also check for any open transactions that might have been left in previous runs.
Not SSMS in itself, but the network may have something which terminates client connections after a long period of inactivity. Yep, that's as specific as I'm going to get with that one.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2017 at 12:40 pm
Luis Cazares - Tuesday, February 28, 2017 12:22 PMI've never seen SSMS to timeout when executing a query, I've seen it run for over a day.
If you're deleting a large number of rows, you might want to check the Query options. Right click any where on the query window, select Query Options and check that Execution time-out is set to 0.You might also want to remove the BEGIN TRANSACTION/COMMIT TRANSACTION as they have no real use there.
Also check for any open transactions that might have been left in previous runs.
So why does Aaron Bertrand use transactions in this article?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2017 at 1:07 pm
Phil Parkin - Tuesday, February 28, 2017 12:40 PMLuis Cazares - Tuesday, February 28, 2017 12:22 PMI've never seen SSMS to timeout when executing a query, I've seen it run for over a day.
If you're deleting a large number of rows, you might want to check the Query options. Right click any where on the query window, select Query Options and check that Execution time-out is set to 0.You might also want to remove the BEGIN TRANSACTION/COMMIT TRANSACTION as they have no real use there.
Also check for any open transactions that might have been left in previous runs.
So why does Aaron Bertrand use transactions in this article?
Just as common practice as explained here
However, unless other DML operations are used, having explicit transactions might cause leaving open transactions and causing "eternal" blocking.
He also mentions that error handling must be in place.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply