Sometimes you are keen to know about what is
currently running on the SQL SERVER. I faced a similar problem recently and the below Mentioned query
helped me a lot. Actually my production database log was increasing so fast and I
was not aware of the reason as to why it was increasing in such a big amount? So I ran the
below query and it provided me with the list of statements with their process ids and the
source of running.
And with the help of this SQL statement I found the culprit statement also which was
increasing the log.
So hope fully it will help you too.
SELECT
[Spid] = session_Id
,
ecid
,
[Database] = DB_NAME(sp.dbid)
,
[User] = nt_username
,
[Status] = er.status
,
[Wait] = wait_type
,
[Individual Query] =
SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE
WHEN er.statement_end_offset
= -1
THEN
LEN(CONVERT(NVARCHAR(MAX),
qt.text))
* 2
ELSE
er.statement_end_offset
END -
er.statement_start_offset)/2)
,[Parent
Query] = qt.text
,
Program =
program_name
,
Hostname
,
nt_domain
,
start_time
FROM
sys.dm_exec_requests
er
INNER
JOIN sys.sysprocesses
sp ON er.session_id
= sp.spid
CROSS
APPLY sys.dm_exec_sql_text(er.sql_handle)as
qt
WHERE
session_Id > 50 —
Ignore system spids.
AND
session_Id NOT IN
(@@SPID)
— Ignore this current
statement.
ORDER
BY 1, 2