Thursday, 17 September 2015

How to kill all sessions for single Database in SQL server



Please find below query:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'Name _of_Database'
--SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

-- SELECT @SQL
EXEC(@SQL)

No comments:

Post a Comment

How to find out recent differential backup history in SQL Server

-- Get Backup History for required database SELECT TOP 100 s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000...