I wrote VB.net code to unlock the database. I was tired of always doing this manually, so I wrote a command-line tool in VB.Net to kill database locks . Replace 'test' with your database name and replace my connection string with your own. Connection
strings are a different topic all together.
Module Module1
Const SelectId As String = "USE master; " & vbNewLine & "SELECT request_session_id FROM sys.dm_tran_locks WHERE DB_NAME(resource_database_id)='test' AND request_session_id != @@SPID"
Sub Main()
Dim ConnectionString As String = "Data Source=192.168.4.7,1433;Trusted_Connection=True;"
Dim Con As New Data.SqlClient.SqlConnection(ConnectionString)
Dim SqlCmd As New Data.SqlClient.SqlCommand(SelectId, Con)
Con.Open()
Dim Reader As Data.SqlClient.SqlDataReader = SqlCmd.ExecuteReader
If Reader.HasRows Then
Dim Q As New Queue(Of Integer)
Do While Reader.Read()
Dim ID As Integer = Reader.GetInt32(0)
Console.WriteLine("ID " & ID.ToString)
Q.Enqueue(ID)
Loop
Reader.Close()
Console.Write("Do you want to KILL these proccesses (y/n)? ")
Dim Response As ConsoleKeyInfo = Console.ReadKey
Console.WriteLine()
If Response.Key = ConsoleKey.Y Then
Do Until Q.Count = 0
SqlCmd = New Data.SqlClient.SqlCommand("KILL " & Q.Dequeue, Con)
SqlCmd.ExecuteNonQuery()
Loop
My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Asterisk)
End If
Con.Close()
Else
Console.WriteLine("No rows found.")
Reader.Close()
Con.Close()
End If
Console.WriteLine("Done. Press any key to exit...")
Console.ReadLine()
End Sub
End Module
Exclusive access could not be obtained because the database is in use
)s
