query to find long running query

#51

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#52

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#53

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#54

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#55

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#56

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#57

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#58

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

#59

For 2000,

Can I do something along the lines of looking at select spid from
sysprocesses where status = 'running' and getdate()-lastbatch >= 20 mins

I dont have QA handy now so the columns names may be incorrect.. but just at
a high level.. will that work ?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

Jack
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

-----------------------------------------------
SELECT session_id, user_id, text
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
WHERE plan_handle IS NOT NULL;

"Jack" <Jackdaniels (AT) hotmail (DOT) com> wrote in message
news:OdvVXZ7tIHA.2064 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
If a process is running and not waiting for any resource, will waittime
increase as such ?

I would like to know if a query is just running right now and been
running for the last 20 mins and it may not be waiting for anything but
just running.

"manu" <manu (AT) discussions (DOT) microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94 (AT) microsoft (DOT) com...
Try this code and schedule it as a job:--

SET NOCOUNT ON
Drop TABLE #job_execution_state
CREATE TABLE #job_execution_state (spid int NOT NULL,
cmd sysname
COLLATE
database_default NULL,
hostname sysname
COLLATE
database_default NULL,
waittime INT NOT
NULL,
dbid INT NOT
NULL,
uid INT NOT
NULL,
Status sysname COLLATE
database_default NULL,
loginame sysname COLLATE
database_default NULL)
Insert into #job_execution_state
select spid,cmd,hostname,waittime,dbid,uid,status,loginam e from
master..sysprocesses where waittime>1200000--Specify time in
milliseconds here
and spid>50

if exists (Select * from #job_execution_state)

begin
EXEC xp_cmdshell 'Net Send Machinename One or more processes are running
for
more than 20 minutes'
end

Manu Jaidka

"Jack" wrote:

Is there no query using sysprocesses or DMVs in SQL 2000/2005 that i
can
write ? I want to raise an alert if queries are running for past 20
mins.

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59 (AT) microsoft (DOT) com...
With SQL2000, you'd be better off obtaining that info with a SQL
trace.

Linchi

"Jack" wrote:

I want to write a query that will tell me if there are queries that
are
still running and are past 20 mins since it came in ? How can I do
so ?
I am using SQL 2000. If i can have one for 2005, that would be fine
too.

Thank you.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

query to find long running query

microsoft.public.sqlserver.server microsoft.public.sqlserver.server

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM

Re: query to find long running query - 05-18-2008 , 11:33 AM