Comments
9 comments
-
Hi Iain,
Thanks for pointing this out and I will bring it to someone's attention, but it looks like a situation we may not be able to work around.
The "stop tests" button works by setting a flag telling SQL Test to cancel execution at the next test task. It can't cancel a query that is running whilst in-progress, unfortunately. The query you have given it to run exceeds the maximum execution time, which I believe is 10 minutes and can't be extended.
SQL Test's process, according to the who query, is SPID 56. Cancelling SPID 54 is killing a generic SQL process, so there is no control over what will happen when you try to kill the SPID. It seems to cause an "Internal Binder Error" in the SSMS process.
So unfortunately I don't have any advice to either a. prevent the problem, b. make the stop button cancel a running query execution (without taking out SSMS) or c. prevent SSMS from crashing in either case. Best advice I have is to avoid this type of long-running query. -
Thanks, Brian.
Are you referring to the output I posted?
Sounds like you might be referring to a different set of spids. The exact numbers will vary from time to time.Brian Donahue wrote:SQL Test's process, according to the who query, is SPID 56. Cancelling SPID 54 is killing a generic SQL process, so there is no control over what will happen when you try to kill the SPID. It seems to cause an "Internal Binder Error" in the SSMS process.
Not sure what you mean by "generic SQL process".
In my example, spid 54 must be the SQL Test process. Everything else is idle ("AWAITING COMMAND") while the test is running.
The program_name column contains nothing like "SQL Test".
SQL Test apparently uses the SSMS default program_name ("Microsoft SQL Server Management Studio") instead of identifying itself clearly.
The program_name of SPID 56 is SQL Prompt, not SQL Test.
I don't think SQL Prompt is causing any issue here. I should have disabled it to make the output clearer.
SSMS is generally quite robust, but it is not protected from crashes caused by add-ins.
To clarify, it looks like SQL Test is crashing and taking SSMS with it.
SQL Prompt is more robust. It doesn't crash SSMS if its spid is killed. It just stops working until you turn it off and on again.
Could you make SQL Test behave the same way? -
Brian Donohue wrote:It looks like a situation we may not be able to work around. [...] [SQL Test] can't cancel a query that is running whilst in-progress, unfortunately.
SQL Server does not impose that behavior. You can work around it by changing how SQL Test manages its sessions.
SSMS allows you to cancel a query that is in progress.
You can press the red stop button, or close the window. If you close the window, you have to answer "Yes" when it asks "Do you want to cancel the query?"
It's not a major issue, but I would be happier knowing that my careless coding can't crash my client. -
I have logged development bug SQT-372 to have someone look into the cancellation problem.
-
Thanks, Brian!
-
Hi Iain,
I'm pleased to announce that we have a beta of SQL Test out that should fix all of the issues listed in your post:
http://www.red-gate.com/MessageBoard/viewtopic.php?p=67784
Please let me know if you find any issues with this build.
Best regards,
David -
SQL Test is a lot more robust with this update.
It remains stable when the long test runs.
There are no messages about timeouts in the GUI.
The stop button does indeed stop a test in progress.
The time to stop the test seems to depend on how long the test has been running. That's just normal rollback behavior, so that's okay.
Because the program name is "Red Gate Software Ltd SQL Test 1.5.0.79", it's much easier to debug now.
To find all the SQL Test sessions, you can use a query like this:SELECT spid, program_name, cmd FROM sys.sysprocesses WHERE program_name LIKE '%SQL Test%';
Sorry for my late reply - been away from development projects for a while.
Thanks for tackling this, David! -
Hi Iain,
Thanks for responding! It's great to get feedback for our work, glad to hear it's all working ok!
Please keep us informed!
Best regards,
David -
Hi Lain,
Do you confirm that it remains stable with a long test runs ?
i received many messages about timeouts in the GUI...and the stop button does not work in test progress...
Can you tell me witch version are you using ??
Best regards,
A.Amine,
Senior Data Base Administrator
https://www.الرقية-الشرعية.com
Add comment
Please sign in to leave a comment.
1. SQL Test can't stop a test in progress.
2. A runaway test eventually SSMS crashes.
Can you make it safe to stop a a test in progress, and provide a way to do it in the SQL Test interface?
Here's an example to illustrate the problem.
Say you're trying to roll your own version of PostgreSQL's generate_series function.
SELECT * FROM generate_series(2,4); generate_series ----------------- 2 3 4 (3 rows)From that example I created a simple test:
CREATE PROCEDURE [PGDocTests].[test can generate simple series] AS BEGIN --Act CREATE TABLE #actual ( num INT ); INSERT INTO #actual SELECT * FROM generate_series(2, 4); --Assert SELECT TOP (0) * INTO #expected FROM #actual; INSERT INTO #expected VALUES (2), (3), (4); EXECUTE tSQLt.AssertEqualsTable @Actual = N'#actual', @Expected = N'#expected'; END;To pass the test, you might naively try something like this:
Joining sys.all_columns to itself without a filter generates over five million rows in a new database.
This should fail because the test expects just three output rows.
Before my machine can materialize the output, SQL Test triggers an internal timeout and then crashes.
The Red Gate error reporting tool showed this message just before SSMS crashed:
Before the crash, I tried to stop the test using the interface, but the test kept going.
SSMS also crashes if you try to stop the test more forcefully.
I let the test run again for a minute then queried the user-level processes to find the test:
Session 54 must be the test runner. It is running parallel SELECT INTO processes, while everything else is idle.
Let's kill it!
SSMS crashes again!