SQL Server – Maintenance Commands – DBs Info, Processes, Longest Running Process & Underlying Query

Posted: May 13, 2012 in SQLServer
Tags: , ,

— List all processes running on SQL Server
sp_who2

— List all processes running on SQL Server for a specific DB
SELECT [Database]=DB_NAME(dbid), spid, ‘Kill ‘ + cast(spid as varchar(10)), last_batch, status, hostname, loginame
FROM sys.sysprocesses
WHERE dbid = DB_ID(‘SS_QA’)

— Get all the databases info
EXEC sp_databases
EXEC sp_helpdb
EXEC sp_msForEachDB ‘PRINT ”?”’

— Find longest running SPIDs

select
p.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), ‘1900-01-01’),
121), 12) as ‘batch_duration’
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in (‘background’, ‘sleeping’)
and P.cmd not in (‘AWAITING COMMAND’
,’MIRROR HANDLER’
,’LAZY WRITER’
,’CHECKPOINT SLEEP’
,’RA MANAGER’)
order by batch_duration desc

— SQL running for a given spid

declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)

set @spid = 70 –XXX — Fill this in

select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from master.dbo.sysprocesses
where spid = @spid
order by ecid

SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end – @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)

Advertisements

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