Some time we need to know that who
is using database as of now or who all are connected to specific database.
We can use sysprocesses
to know the all the detail for specific
database.
sysprocesses contains information
about processes that are running on an instance of SQL Server. These processes
can be client processes or system processes. To access sysprocesses, you must
be in the master database context, or you must use the master.dbo.sysprocesses
three-part name.
If a user has VIEW
SERVER STATE permission on the server, the user will see all executing sessions
in the instance of SQL Server; otherwise, the user will see only the current
session.
Column
name
|
Data
type
|
Description
|
spid
|
smallint
|
SQL
Server session ID.
|
kpid
|
smallint
|
Windows
thread ID.
|
blocked
|
smallint
|
ID
of the session that is blocking the request. If this column is NULL, the
request is not blocked, or the session information of the blocking session is
not available (or cannot be identified).
-2
= The blocking resource is owned by an orphaned distributed transaction.
-3
= The blocking resource is owned by a deferred recovery transaction.
-4
= Session ID of the blocking latch owner could not be determined due to
internal latch state transitions.
|
waittype
|
binary(2)
|
Reserved.
|
waittime
|
bigint
|
Current
wait time in milliseconds.
0
= Process is not waiting.
|
lastwaittype
|
nchar(32)
|
A
string indicating the name of the last or current wait type.
|
waitresource
|
nchar(256)
|
Textual
representation of a lock resource.
|
dbid
|
smallint
|
ID
of the database currently being used by the process.
|
uid
|
smallint
|
ID
of the user that executed the command. Overflows or returns NULL if the
number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog.
|
cpu
|
int
|
Cumulative
CPU time for the process. The entry is updated for all processes, regardless
of whether the SET STATISTICS TIME option is ON or OFF.
|
physical_io
|
bigint
|
Cumulative
disk reads and writes for the process.
|
memusage
|
int
|
Number
of pages in the procedure cache that are currently allocated to this process.
A negative number indicates that the process is freeing memory allocated by
another process.
|
login_time
|
datetime
|
Time
at which a client process logged into the server. For system processes, the
time at which the SQL Server startup occurred is stored.
|
last_batch
|
datetime
|
Last
time a client process executed a remote stored procedure call or an EXECUTE
statement. For system processes, the time at which the SQL Server startup
occurred is stored.
|
ecid
|
smallint
|
Execution
context ID used to uniquely identify the subthreads operating on behalf of a
single process.
|
open_tran
|
smallint
|
Number
of open transactions for the process.
|
status
|
nchar(30)
|
Process
ID status. The possible values are:
dormant =
SQL Server is resetting the session.
running =
The session is running one or more batches. When Multiple Active Result Sets
(MARS) is enabled, a session can run multiple batches. For more information,
see Using Multiple Active Result Sets (MARS).
background =
The session is running a background task, such as deadlock detection.
rollback =
The session has a transaction rollback in process.
pending =
The session is waiting for a worker thread to become available.
runnable =
The task in the session is in the runnable queue of a scheduler while waiting
to get a time quantum.
spinloop =
The task in the session is waiting for a spinlock to become free.
suspended =
The session is waiting for an event, such as I/O, to complete.
|
sid
|
binary(86)
|
Globally
unique identifier (GUID) for the user.
|
hostname
|
nchar(128)
|
Name
of the workstation.
|
program_name
|
nchar(128)
|
Name
of the application program.
|
hostprocess
|
nchar(10)
|
Workstation
process ID number.
|
cmd
|
nchar(16)
|
Command
currently being executed.
|
nt_domain
|
nchar(128)
|
Windows
domain for the client, if using Windows Authentication, or a trusted
connection.
|
nt_username
|
nchar(128)
|
Windows
user name for the process, if using Windows Authentication, or a trusted
connection.
|
net_address
|
nchar(12)
|
Assigned
unique identifier for the network adapter on the workstation of each user.
When a user logs in, this identifier is inserted in the net_address column.
|
net_library
|
nchar(12)
|
Column
in which the client's network library is stored. Every client process comes
in on a network connection. Network connections have a network library
associated with them that enables them to make the connection. For more
information, seeNetwork Protocols and TDS Endpoints.
|
loginame
|
nchar(128)
|
Login
name.
|
context_info
|
binary(128)
|
Data
stored in a batch by using the SET CONTEXT_INFO statement.
|
sql_handle
|
binary(20)
|
Represents
the currently executing batch or object.
Note This
value is derived from the batch or memory address of the object. This value
is not calculated by using the SQL Server hash-based algorithm.
|
stmt_start
|
int
|
Starting
offset of the current SQL statement for the specified sql_handle.
|
stmt_end
|
int
|
Ending
offset of the current SQL statement for the specified sql_handle.
-1
= Current statement runs to the end of the results returned by the fn_get_sql
function for the specified sql_handle.
|
request_id
|
int
|
ID
of request. Used to identify requests running in a specific session.
|
SQL
Query:-
select spid, status, loginame,
hostname, blocked, db_name(dbid) as databasename, cmd
from master..sysprocesses
where db_name(dbid) like '%TSQL%'
and spid > 50
No comments:
Post a Comment