Pause the query

Question

Post reply

Pause the query

ravisamigo

SSCertifiable

Points: 7440
More actions
December 20, 2010 at 8:12 am

#242541

Dear All,
Is there any option to pause the query and resume later if it takes more time to finish?
Please advise.
Thanks and Regards,
Ravichandra.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can't do it in the middle of a statement, however you can do it between statements in a batch with this code :

WAITFOR DELAY '00:00:03'

WAITFOR TIME '12:34:56'

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

But understand, you're holding open the transaction, locking resources, possibly blocking other users while that wait is in place. It could be a very dangerous thing to do.

What exactly is the problem here?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

ravisamigo (12/20/2010)
Dear All,
Is there any option to pause the query and resume later if it takes more time to finish?
Please advise.
Thanks and Regards,
Ravichandra.

IF somecondition is true WAITFOR TIME 'hh:mm:ss'

Of course, that assumes that you didn't write it all as one big honkin' query to begin with. You could do partial processing with a "control loop" as many folks do with large DELETE's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Agh! Gotta remember to read down. Remi already suggested what I just did.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)