How to check process on sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to check process on sql server

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-27 : 12:27:35

How to check which process is running and active and how to confirm that particular process has been completed ? Is it possible in SQL using Query analyzer or Enterprise manager.?

Please help

SQL IN Minds

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:32:42

You mean which spid is doing what? try

sp_who2 active

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-27 : 12:41:31

NO I mean process, suppose a user start a script on database and it is supposed to take 3-4 hrs to complete, so how check it in sql server

SQL IN Minds

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:44:51

You can see what's happening at the moment. Not sure why you want to find out what processes have finished? Can you explain the bigger picture of what you are trying to do?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-27 : 12:46:35

Actually one user supposed to run a script on the database and it's supposed to run for 3-4 hrs.I have to confirm that the process has been started as well when it finish I have to take backup of that db.

SQL IN Minds

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:50:47

Is it a job or a script running through query analyzer? Its easier to check the status of a job than to check if a script has completed.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-27 : 12:56:54

it's query runnning thorugh front end application

SQL IN Minds

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 13:24:25

hmm.. not sure if there is an accurate way.. unless you modify the client app to write somewhere the status and you can just query the status table. This also gives you the choice to maintain history of the process.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-27 : 14:11:08

Hi I can see the processes
I am using the following query
USE MASTER
SELECT * FROM SYSPROCESSES WHERE waittime > 1000
GO

and
SELECT DB_NAME(Copy_Dbid_here_To_find_the_Database_Name )
SELECT DB_NAME(1)
SELECT * FROM SYSPROCESSES WHERE open_tran <> 0
GO
DBCC INPUTBUFFER(Replace_Copied_SPID_here)
GO

This solve problem

SQL IN Minds

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 14:13:22

Yes you can query sysprocesses but its not always accurate right? You can find out the spid and use DBCC INPUTBUFFER manually, but I recall you mentioned you have to do this via job in your original post.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-28 : 00:53:28

Use 'sp_who2 active' to find out spid of the process, then monitor it with 'sp_who2 spid'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources