SQL transactions that drop connections.

Question

SQL transactions that drop connections.

rudyhinojosa2007

SSC Rookie

Points: 46
More actions
December 17, 2009 at 12:45 pm

#137681

I was curious if there is a definitive guide from a credible source that talks about what SQL functions and/or transactions that could drop a persistent connection to a production database?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Drop a different connection? So from one session terminate another?

KILL is what you're looking for. It's the only thing (other than a shutdown) that can terminate someone else's connection.

Edit: Taking a DB offline/single user/restricted user WITH ROLLBACK... will also disconnect users.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rudyhinojosa2007 SSC Rookie Points: 46 More actions · Answer 2

GilaMonster (12/17/2009)
Drop a different connection? So from one session terminate another?
KILL is what you're looking for. It's the only thing (other than a shutdown) that can terminate someone else's connection

I have Access 97 users connected through an odbc connection to SQL 2005. Out of the blue all of the users started getting #Deleted in the form fields. Resetting their PC's fixed the issue. I checked with the DBA and all he did was changed the database recovery mode from Simple to Full. Would that drop existing connections?

rudyhinojosa2007 SSC Rookie Points: 46 More actions · Answer 3

geez, I re-read my question and the context was all wrong. sorry. It sounded like a noob question like "how do i kill a process?" lol

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Nope. A switch of recovery model does not disconnect users.

Is it possible that he took the database offline? Set it to single user or restricted mode?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rudyhinojosa2007 SSC Rookie Points: 46 More actions · Answer 5

No. I reviewed the logs and nothing out of the ordinary. My only guess is that there was a subtle network glitch that interrupted the network connection. Thanks for responding quickly. 😉