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.
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 Jamesyes, 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
|
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
|
barnabeck
Posting Yak Master
236 Posts |
Posted - 2013-07-22 : 18:10:41
|
@LoztInSpace ... no comment :))Thank you guys... I finally could kill the table simply with the drop table command... |
|
|
|
|
|
|
|