x

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

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1850
x127
x1

asked: Sep 07, 2012 at 08:55 AM

Seen: 873 times

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