query to find long running query

#21

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:

Quote:

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.

#22

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:

Quote:

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.

#23

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:

Quote:

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.

#24

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:

Quote:

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.

#25

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:

Quote:

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.

#26

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:

Quote:

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.

#27

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:

Quote:

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.

#28

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:

Quote:

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.

#29

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:

Quote:

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.

#30

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

Quote:

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.