0

I let a while loop run over the weekend with a CURRENT_TIMESTAMP condition on it, problem is I accidentally set the condition to 2020-08-06 instead of 2020-06-08 (oops). Is there any way to manually exit the loop and view the results so far? I don't think it's possible for me to change the system time in order for the loop to exist as the SQL server isn't run locally.

1 Answer 1

1

Success will depend a lot on where the results are stored. If they're written to a normal table then you can simply query that table. If there's a transaction open which would block new queries use the NOLOCK hint. If you're using snapshot isolation I don't know how you can get at the open transaction's versioned data.

If all the results are in temporary tables you'll have a harder time. Those tables are scoped to the session so accessing them from a different session will be tricky. You may be able to look in TempDB's metadata to find the table's full name and query that explicitly. I don't know if that will work, I've never tried. (I tried; it doesn't work.)

If the code is running through SSMS you can stop execution without ending the session. This will leave the temp tables in tact and available to the session. Cancel using the menu Query -> Cancel Executing Query (mapped to the red square in the toolbar). If there's an open transaction that transaction will remain open. You will have to manually commit it or roll it back.

Cancellation is achieved by sending an "attention" message to SQL Server. Other clients may or may not support this. From a scripted environment (e.g. Powershell, Python), if the query was submitted asynchronously it may be possible to call the "Cancel" method (or equivalent) on the original connection or query object.

With sufficiently high access rights you could look at the on-disk structures. Paul White has written up a method here. Depending on how much has been written it may take as long to transcribe the dumps from DBCC PAGE as it would to re-run the query.

If results are in variables you could connect a debugger to the SQL Server process, pause its execution and go hunting. Good luck with that. If there are other users on the system, or background system activities, it may be difficult to distinguish your desired values from all the others present. Certainly, it is not documented or supported in any way. Do NOT try this on a shared production instance.

Finally, if you're using SSMS have a look in the messages tab. You never know, whoever wrote the script may have placed PRINT statements to track progress.

Ending the loop is easy. Find the SPID from the SSMS tab or DMVs (sys.dm_exec_sessions et al) then issue KILL session_id. Unfortunately it will end the script entirely, loosing local variables and temp tables, and disconnect the targeted session. Any open transaction will have to roll back, which will take roughly as long as the transaction has been open, give or take.

2
  • Thank you, I was storing row after row in temp tables before they were to be put into real tables, so I imagine I'm quite out of luck here then.
    – Oscar
    Jun 8, 2020 at 11:50
  • 1
    You can't query the temp table from another session (unless it's a global temp table, of course), however just stopping the query (without killing the session) will leave the temp table contents visible to the original session Jun 8, 2020 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.