Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 13 '11 at 20:48

This question came from our site for professional and enthusiast programmers.

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

2 Answers

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.

share|improve this answer
    
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... –  user1096553 Dec 15 '11 at 0:28
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.