Wednesday, September 21, 2011

Sql amazing queries

Find no of connection on database



SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid < 0
GROUP BY dbid, loginame

You can kill all connection are you using currently by running below query


DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'myDatabaseName'
--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)
DEALLOCATE my_cursor

No comments:

Post a Comment