SQL SERVER – 2005 – Retrieve Processes Using Specified Database

March 15, 2008

Blog Reader Jim Sz posted quick but very interesting script. If user want to know how many processes are there in any particular database it can be retrieved querying sys.processes database.

USE master
GO
DECLARE @dbid INT
SELECT @dbid = dbid
FROM sys.sysdatabases
WHERE name = 'AdventureWorks'
IF EXISTS (SELECT spid
FROM sys.sysprocesses
WHERE dbid = @dbid)
BEGIN
SELECT 'These processes are using current database' AS Note,
spid, last_batch,
status, hostname, loginame
FROM sys.sysprocesses
WHERE dbid = @dbid
END
GO

Reference : Pinal Dave (https://blog.sqlauthority.com), Jim Sz

SQL Scripts, SQL System Table

SQL SERVER – 2005 – What is CLR?

SQL SERVER – 2005 – Retrieve Any User Defined Object Details Using sys objects Database

SQL SERVER – Enabling Older Legacy Cardinality Estimation

February 9, 2019

SQL SERVER – Taking Multiple Backup of Database in Single Command – Mirrored Database Backup

October 23, 2010

SQL SERVER – 2008 – Interview Questions and Answers – Part 4

September 15, 2008

5 Comments. Leave new

Jerry Hung
March 17, 2008 7:20 pm
I thought this would be cleaner and easier
SELECT ‘These processes are using database ‘ AS Note
,[Database]=DB_NAME(dbid), spid, last_batch,
status, hostname, loginame
FROM sys.sysprocesses
WHERE dbid = DB_ID(‘master’) — replace DB name here
Reply
- Jerry Hung II
  March 20, 2012 4:14 pm
  I am sure this is the way it has to be…
  Reply
Arturo A.Asenga
March 19, 2008 7:23 pm
Please sending me the datail for the SQL becouse i’m an IT
officer and my interest is to be database manager
sincerely,
Arturo A.Asenga
Reply
Realm174
November 22, 2009 10:27 pm
I used the above snippet to figure out why I was unable to drop my table.. looks like the ‘sa’ account is using it in background….
(status = background and loginame = sa)
I’m using SQL Server Express 2008, in Windows Authentication mode, and I’m the only person using the database at the moment.
So I’m not sure where ‘sa’ is coming from…
Reply