Feeds:
Posts
Comments

Posts Tagged ‘sys.dm_exec_sessions’

SQL SERVER – How to kill all running processes

Posted in Solutions, SQL SERVER, tagged , , , , , on April 1, 2014| Leave a Comment »

Whenever we try to take an exclusive access to a database in order to perform any activity like backup restore, it usually fails if any process is still using the database. I received the given below error from my test database while I was restoring the database on it.

kill process.1.1

Opsssss, fortunately, we do have solution by killing the running process to take a lock and perform any activity using a management command namely KILL & a system view sys.sysprocesses. However, what encourages me to write this solution is that sys.sysprocesses will be removed from the future version of SQL Server. So what is the NEW solution ?

Given below is the script, that I developed using sys.dm_exec_sessions.

USE master
GO
DECLARE @SQL_String VARCHAR(MAX)
DECLARE @Database_id INT

--DO NOT forget to change database name in give below statement
SET @Database_id=DB_ID('AdventureWorks2012')
SET @SQL_String=''

SELECT @SQL_String=@SQL_String + 'KILL '
+ COALESCE(CONVERT(VARCHAR(5),[session_id])+ '; ','')
FROM sys.dm_exec_sessions
WHERE database_id =@Database_id

--PRINT @SQL_String
EXEC (@SQL_String)

The above statement kills all the process running on a specific database. But do not forget to CHANGE the database name.
Once the above statement executed successfully, you can perform any activity.

Read Full Post »

SQL SERVER – Retrieve Processes Using Specified Database

Posted in Solutions, SQL SERVER, tagged , , , , , , on February 28, 2013| Leave a Comment »

sys.sysprocesses is one of the helpful system views when it comes to query the processes of SQL Server. I have been using this system view for quite a while. But a few days ago I read on MSDN, “This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently uses this feature.”

So, I planned to upgrade my query.
Given below is my query that I used to retrieve processes using specified database since SQL Server 2000.

--This query will work fine on SQL Server 2000 and above.
Declare @database_name as varchar(max)
Set @database_name ='AdventureWorks2012'

Select [spid] as [session_id], [last_batch],[status]
,cmd as [Command], hostname as [host_name]
,[program_name], [loginame] as [Login_name]
from sys.sysprocesses
Where dbid = db_id(@database_name)

sysprocesses1.1

Given below is my new query that works on SQL Server 2012 and above, having the same result set as the above.

Declare @database_name as varchar(max)
Set @database_name ='AdventureWorks2012'

Select A.session_id,A.last_request_end_time as [last_batch],A.[status]
, B.[command],A.[host_name]
,A.[program_name],A.[login_name]
from sys.dm_exec_sessions A
Left Join sys.dm_exec_requests B
On A.[session_id]=B.[session_id]
Where A.[database_id]=db_id(@database_name)

sysprocesses1.2

Conclusion :
You can see that both the above queries result set is same but first query (sys.sysprocesses) will not be applicable for future versions of SQL.

Let me know your feedback regarding both queries.

Read Full Post »