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 :
there is no request for my session and:
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?
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.
answered Sep 07 '12 at 10:39 AM
Grant Fritchey ♦♦