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.

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.
>
>

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@discussions.microsoft.com> wrote in message
news:285BC385-3DF7-430C-B291-579C0EDCFE59@microsoft.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.
>>
>>

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@discussions.microsoft.com> wrote in message
> news:285BC385-3DF7-430C-B291-579C0EDCFE59@microsoft.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.
> >>
> >>
>
>

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@discussions.microsoft.com> wrote in message
news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94@microsoft.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@discussions.microsoft.com> wrote in message
>> news:285BC385-3DF7-430C-B291-579C0EDCFE59@microsoft.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.
>> >>
>> >>
>>
>>

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@hotmail.com> wrote in message
news:OdvVXZ7tIHA.2064@TK2MSFTNGP05.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@discussions.microsoft.com> wrote in message
> news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94@microsoft.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@discussions.microsoft.com> wrote in message
>>> news:285BC385-3DF7-430C-B291-579C0EDCFE59@microsoft.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.
>>> >>
>>> >>
>>>
>>>
>

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@iscar.co.il> wrote in message
news:%23q$lmZKuIHA.3716@TK2MSFTNGP05.phx.gbl...
> 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@hotmail.com> wrote in message
> news:OdvVXZ7tIHA.2064@TK2MSFTNGP05.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@discussions.microsoft.com> wrote in message
>> news:F1A3D27D-0EF5-4162-91D1-BA0F40887A94@microsoft.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@discussions.microsoft.com> wrote in message
>>>> news:285BC385-3DF7-430C-B291-579C0EDCFE59@microsoft.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.
>>>> >>
>>>> >>
>>>>
>>>>
>>
>
>