I just tried to make a simple schema change to a table in a SQL Server database (by using the Design tool in SMSS). Whenever I tried to save the change, it kept timing out. I wondered whether this was due to existing connnections which were 'locking' the table.
I decided to kill connections as an experiment. I queried master..sysprocesses to get the current spids for that database, and killed them one by one until I was able to save my schema change. (Not very scientific, but I'm far from an expert with SQL Server). Sure enough, when I had killed all the spids (bar the one which was me using SMSS), I was able to save the schema change.
I would like to ask about the relationship between ADO.NET SqlConnections and spids. For example, if the client app calls Open() on a SqlConnection object, should I see another spid in master..sysprocesses? What about if I call Close() on that SqlConnection? Should the spid disappear?
I'm sure it's not quite that simple, since I understand there is a notion of connection pooling, but can someone shed any light on how this relationship works?
Thank you
David