Jul 21, 2021

MSSQL issue fix: Cannot drop database because it is currently in use

While executing the MSSQL query
drop database TestDb
If you get this error "Cannot drop database because it is currently in use", you can force delete that MSSQL database by terminating all the processes which are using it

DECLARE @DBName varchar(50) = 'TestDb'
WHILE EXISTS(select NULL from sys.databases where name = @DBName )
BEGIN
    DECLARE @SQL varchar(max)
    SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DBName) AND SPId <> @@SPId
    EXEC(@SQL)
    EXEC('DROP DATABASE ' + @DBName)
END

No comments:

Post a Comment

Be the first to comment on this post.