question

Håkan Winther avatar image
Håkan Winther asked

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?
sql-server-2008ssmsbug
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I looked at the version number and it is service pack2.
0 Likes 0 ·
Hmmm.... That's weird. I'd report it to MS. It's not something I've seen or would expect to see.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.