Forcefully detach mdf file in SQL Express...

  • Article

[via https://www.mssqlonline.com/?p=14]

when you want to force a detach of sqlexpress mdf file, while there are still other processes currently using it (or normally when there were still connections remain in the connection pool), you can use the following sqlcmd script to force a process kill and then detach the database. the condition is that this script assumed that the application is using .Net SqlClient Data Provider. if you are using other provider, change the query string below to identify processes to kill.

Declare @spId Varchar(30)

DECLARE TmpCursor CURSOR FOR
Select 'Kill ' + convert(Varchar, spid) as spId
from master..SysProcesses
where program_name = '.Net SqlClient Data Provider'

OPEN TmpCursor

FETCH NEXT FROM TmpCursor
INTO @spId

WHILE @@FETCH_STATUS = 0

BEGIN

Exec (@spId)

FETCH NEXT FROM TmpCursor
INTO @spId

END

CLOSE TmpCursor
DEALLOCATE TmpCursor
go
exec sp_detach_db [###mdf file name in full path###]
go

this script basically iterates all the spid, form the kill statement, and run it in the while loop, and finally do the detach of the database.

beware that after forcefully  detaching the database, your current running application may behave abnormal since the connection / process assumed to be exist was lost.

FYI.

Technorati Tags: microsoft,sqlserver,express,detach,attach,mdf