Yes I am using Service Broker.

Main problem is that service broker has stopped working and I think it may be because of this issue

after killing the SPIDS repeatedly it seems to have resolved now. wierd !!

thanks Gail

What are the values for the command column for the two system sessions that you've seen causing problems? Query sys.dm_exec_requests.

UPDATE

What are the wait types and wait resource for the sessions...

I think here is what you needed Gail

session_id status blocking_session_id wait_type wait_time wait_resource transaction_id

52 suspended 27 ...

wait type is NULL

that 'Open Tran' I am talking about is blocking the drop as is evident from SP_Who2 'Blkby' column

that doesnt tell me what command is running.

There is definately a blocking issue as even drop table and delete from is taking forever to delete one row from a table...

well its still there and yes the connection is closed.

it says

Status: background

Command : Update

CPU Time :91156

DISK IO 6371

SPID : changed to 16 now.

its locking database and service broker has...

What worked for me was windows update. Thanks

ok

Its actually an iterative call to a Stored Proc from .Net

and the proc runs super fast on SSMS. every call it fetches 2 to 3 rows and

returns data to front...

I explored Red Gate. My concern is more about how to maintain a proper sequence of scripts ...what goes when... if we have many drop columns and add columns its...

GSquared,

I was reviewing code written by another developer and came across scenarios

where is he has been checking AND of two columns

Application ID and Application Mask

===========

..WHERE ApplicationMask & Application_ID =...

Thanks, I will look into converting to Binary and compression.

Then in what scenarios would we need to decrypt data by HASHING it if the client application cant read it ?

I am looking to send BINARY data instead of XML in the Service Broker to reduce message payload.

I was thinking to HASH the string but then couldnt figure out how...

I found the table its sys.certificates.

When I send a message to the target queue, messages appears on sys,transmission_queue table with error message 'DNS lookup failed with error: '11001(No such host...