0

I have a remote sql server connection and was in the middle of updating some value with begin and commit tran command. However, before it finished, the query got canceled and the table is locked. After several attempt to restart the database, the table is still locked and then we restarted the machine.

It is worse now that we cannot even connect to the server from management studio. There are few different errors that shows up when I tried to connect such as the connection timeout, server not known and the most common one that shows up is cannot generate SSPI context.

It can't be domain issue since I can connect just fine before on the same network with same user account. Does anyone know how to resolve this?

3
  • Offtopic, should be on serverfault.
    – Marc B
    Dec 13, 2011 at 20:32
  • What is showing up in the SQL ErrorLog?
    – mrdenny
    Dec 13, 2011 at 21:43
  • There is nothing in the ErrorLog suprisingly :( Dec 13, 2011 at 22:01

2 Answers 2

2

Why was the query cancelled? Was it a big query? What actions were taken after the query was cancelled? How long did you wait before restarting?

The reason I ask is that, when a query gets cancelled the database immediately starts rolling back the transaction. If the query had done a significant amount of changes before the cancellation, it might take a long time to rollback (explaining why the table would be locked). This is when things typically go from bad to worse, because developers get impatient and do things like restart the service/server. Then, when the database engine starts up, the database goes into recovery mode, rolling back all transactions that were open during the restart.

Can you remote into the machine? If so, check services to verify the database engine is running. If so, can you connect to localhost via management studio (remoted in). If not, start it. If you can't start it, try starting in single user mode.

1
  • Hi Brian, Thanks for the respond. I can remote to the machine and the service is running. However I cannot connect to the to the server even from the local machine. We decided to migrate the database to other server, but what's funny is that the server that is stuck can't even be deleted. Messed up... Dec 15, 2011 at 0:28
0

Keeping in mind what @brian said, the other thing you should do is make sure you have a good recent backup. And then take another one for good measure.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .