SQL SERVER – 2005 – Retrieve Processes Using Specified Database

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 (http://blog.SQLAuthority.com), Jim Sz

5 thoughts on “SQL SERVER – 2005 – Retrieve Processes Using Specified Database

  1. 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

    Like

  2. 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

    Like

  3. 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…

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s