Skip to main content
Synthetic Realities
Shopping cart
Empty
For enquiries:
synthetic.realities@outlook.com
0409 048 621
  • home
  • About us
  • Blogs
  • Contact us

Categories

  • Business Processes
  • Business Tools
  • DBA Tools
  • SQL Scripts
  • Software

Clearly Viewing Database Activity with SP_WHO2

One of the most commonly used stored procedures within SQL server is SP_WHO2, as it shows all the sessions that are currently established in the database. These are denoted as SPID‘s, or Server process Id’s. The following SQL query is useful for formatting the output, to filter out system database activity and ordering the output per database.


DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)
INSERT INTO @Table EXEC sp_who2
SELECT * FROM @Table
WHERE
DBName not like 'master' and
DBName not like 'msdb' and
DBName not like 'model' and
DBName not like 'tempdb'
ORDER BY DBName, Status

  • Anonymous's blog

More information about text formats

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

Order support

  • Customer Service

Product support

  • Purchases and GST

Legal

  • Terms and Conditions
  • Returns & Refunds
  • Business Contact Details
  • Privacy