This is the script which I always had in my archive. Following script find out which are the queries running currently on your server.
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLYÂ sys.dm_exec_sql_text(sql_handle) AS sqltext
While running above query if you find any query which is running for long time it can be killed using following command.
KILL [session_id]
Reference : Pinal Dave (https://blog.sqlauthority.com)
60 Comments. Leave new
We can get this even by profiler too, right?
But is there anyway i get the queries executed only on a database rather than server instance itself?
This will help if when we are trying to understand a big application that talks to a database.
I think you can add filter (where clause) of database as follows into this query
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where DB_NAME(req.database_id) = ‘master’
What about adding the user to the query?
Add one more join to query as follow
INNER JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
and column in select list as s.login_name
sorry to ask my question here.
Just want to know how to retrieve data faster from sql server 2005 in VB.net 2005? Any idea? thanks.
When I try running this script I get the Message “sql_handle” is not a recognized table hints option. I am running 2005 but compatability mode is set to 80. Is there a way round this?
This works only at particular instance of time.
I want to record all the sql running/finished, its cpu time, etc
Hi
when i ran this query.it shows me the same query in the output.I think result expected would be a different query.
Does anybody know a query for SQL Server 2000?
Thank you!
Hi
When i run the above query, the same i got as the current running query.
Is there are no queries running currently other than the above query?
If some other queries were also running, then how can it be captured?
Thanks in advance
is there any way of seeing the parameters being passed with the exec ?
“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
currently running query error
thanks a lot.. this query helped me solve an issue :)
Hi Pinal,
IS it possible to retrive the details of currently running ssis package information by using query that is stored in msdb ,but not scheduled in sql agent job.
Thanks in Advance
Prasad
sp_usrinputbuffer
@tandy,
what is exactly ‘sp_usrinputbuffer’ for ??
Hi Hendra!
It´s a procedure to identify the exact point of execution of a statement.
You can find the complete code of this procedure on:
Good luck!
Regards,
Tandy
I use this on occassion however a added “WHERE session_id @@SPID” so that I do not self
This article is very helpful to me, thanks to pinaldave
thanks a lot it saved most of the time for today
Thanks for this article, but i have another query, for example i have stored procedure named A running having function named B, if i execute procedure A, using this query, at some stage this will show function B is executing rather procedure A, please give me any solution for finding procedure A while procedure executing function B.
above query works fine in sql server 2005, but how about 2000. Please help me.
Hi Pinaldave,
i have one dought, how findout current(Today) running multipule sql agent job list?
regards,
Harish
— Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT
— Initialize Variables
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) — Last 7 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
— Final Logic
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 — Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
Hi Pinaldave,
i have one dought, how findout current(Today) running multipule job list?
regards,
Harish