urgent - how to kill a query executed through .net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

urgent - how to kill a query executed through .net

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-07-21 : 17:04:02

I just launched a query through a webapplication which after a short while caused a logical timeout error. The connection to the server was interrupted, but my query is still being executed on the server.

How can I stop that query?

If I launch that query now in SQL Management Studio I get that that tempdb is already in the database. The query hadn't come yet to the end, as the last comment regards the dropping of that table.

The query performs 4.611.686.018.427.387.903 loops....

Martin

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-21 : 18:09:17

quote:
Originally posted by barnabeck

I just launched a query through a webapplication which after a short while caused a logical timeout error. The connection to the server was interrupted, but my query is still being executed on the server.

How can I stop that query?

If I launch that query now in SQL Management Studio I get that that tempdb is already in the database. The query hadn't come yet to the end, as the last comment regards the dropping of that table.

The query performs 4.611.686.018.427.387.903 loops....

Martin

That error message does not make sense to me - unless you have a table named "tempdb".

In any case, run the sp_who2 command from an SSMS window. You will see all the spids that are running. If you can identify the job based on any of the columns returned, you can kill it using kill spid. (for example, kill 57, if 57 is the spid).

If the job has progressed some, it may take a while to get killed because SQL Server will have to rollback any changes it has already done.

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-07-21 : 18:33:01

Thank you James
yes, the query builds up a temporary table, temp101, that is supposed to be dropped after the last run.
If I do sp_who2, I can't see any spid related to the user I use in the connectionstring of my web-application, which makes me think that the query is finished anyway. But why can't I run that same query manually in Management Studio. Seems that the query was interrupted before coming to an end and therefore I have to delete that temporary table temp101 manually. But how to identify it? The temporary tables in System Databases -> do have all sort of hexadecimal coded names and I don't know how to relate to them?

Martin

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-21 : 19:07:59

I don't think it is a (simple) temporary table. It is either a global temporary table (in which case the name should start with ##), or a user table in which case it should not start with # or ##). Regular temporary tables are scoped to each sessions, so the fact that you used that in one session should not affect another session even if that new session were to use the exact same name.

What I suspect is that it is not a temp table, it is a user table (based on your description that the name is temp101). If that is the case, then you should drop that table (from a SSMS query window). Otherwise, it will stay around for ever.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-21 : 19:17:40

You can try the following commands to get more information:

[CODE]

SELECT * FROM INFORMATION_SCHEMA.TABLES where table_name like '%temp%';

SELECT * FROM sys.objects where object_id = OBJECT_ID('temp101', 'u');

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

[/CODE]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-21 : 19:18:28