SSMS session active and "running" but no requests

I have experienced a strange behavior with management studio (sql2008) when running a large script (generated from Red-gate compare) with some DDL statements separated by GO. The script is running a long time and when I check sp_who2 nothing changes in CPU or IO for my session.

When running :

 select * from sys.dm_exec_requests

there is no request for my session and:

 select * from sys.dm_exec_sessions

is showing AWAITING COMMAND and active transaction = 1, but DBCC INPUTBUFFER(myspid) is showing the Alter table statement.

I cancel the query but nothing happens, and then I use the KILL myspid to release the resources and rollback the transaction. When I check the sessions and requests to see the rollback the sessions is gone (the rollback was probably very quick)

BUT my client in management studio still thinks the session is running and when I close the window I get a question about "Do you want to cancel the query".

It seems like the communication between the client and the server is all gone.

I am not running on my computer and I guess it is the RTM version of SSMS.

Is this a known issue? Have you experienced something similar?

more ▼

asked Sep 07, 2012 at 08:55 AM in Default

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

That's not something I've run into. I have seen scripts generated from SQL Data Compare be so big that you can't run them through SSMS. I'd suggest patching the GUI to the latest SP if not the latest CU and see if that fixes the problem. That's very unusual to see something in INPUTBUFFER but not dm_exec_requests. That raises my hackles a little. It's suggestive of some issue beyond SSMS.

more ▼

answered Sep 07, 2012 at 10:39 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

I looked at the version number and it is service pack2.

Sep 07, 2012 at 12:57 PM Håkan Winther

Hmmm.... That's weird. I'd report it to MS. It's not something I've seen or would expect to see.

Sep 07, 2012 at 01:31 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 07, 2012 at 08:55 AM

Seen: 1097 times

Last Updated: Sep 07, 2012 at 01:31 PM

Copyright 2018 Redgate Software. Privacy Policy