November 2009 Entries
Who is hogging the database process? This question can be hard to answer if you don’t have the right tools to view the Activity monitor. I got recently this problem.
SQL is an amazing language and MS SQL server can give you answers to about anything threw SQL queries. The query that answered my questions was this:
SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
this query will show you a table over the databases, the number of active connections and the logins that are using them. After that you can run another query to kill all connections to a specific database like so:
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
There are some other tricks at http://www.kodyaz.com/articles/kill-all-processes-of-a-database.aspx.