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
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
WHERE dbid = DB_ID(‘master’) — replace DB name here
I am sure this is the way it has to be…
Please sending me the datail for the SQL becouse i’m an IT
officer and my interest is to be database manager
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…