SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 urgent - how to kill a query executed through .net
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
187 Posts

Posted - 07/21/2013 :  17:04:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/21/2013 :  18:09:17  Show Profile  Reply with Quote
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.
Go to Top of Page

barnabeck
Posting Yak Master

Spain
187 Posts

Posted - 07/21/2013 :  18:33:01  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/21/2013 :  19:07:59  Show Profile  Reply with Quote
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.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/21/2013 :  19:17:40  Show Profile  Reply with Quote
You can try the following commands to get more information:



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;

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/21/2013 :  19:18:28  Show Profile  Reply with Quote
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 07/22/2013 :  09:31:22  Show Profile  Reply with Quote
hahaha - you didn't try to find the unique combinations of 100 unpaid invoices did you :) ?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186924
Go to Top of Page

barnabeck
Posting Yak Master

Spain
187 Posts

Posted - 07/22/2013 :  18:10:41  Show Profile  Reply with Quote
@LoztInSpace ... no comment :))

Thank you guys... I finally could kill the table simply with the drop table command...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000