Share via

How to get Running Process in Sql Server along with Name

  • Article

Question

Wednesday, December 4, 2013 3:03 PM

Hello

I have following query

SELECT COUNT(*) FROM
(SELECT
    DB_NAME(dbid) as DBName, 
    hostprocess,
 loginame as LoginName
 --program_name
FROM
    sys.sysprocesses
WHERE 
    dbid > 0 and
 DB_NAME(dbid) = 'Northwind' and
 program_name != 'Microsoft SQL Server Management Studio - Query'
GROUP BY 
    dbid,loginame,hostprocess) AS A

With the help of this I can get Current Running Process List for particular database.

But Here I can not get Process Name. I want to know process name as we can see in task manager. So is there any way to get it?

can anybody halp me for it?

Regards

Vipul Langalia

All replies (7)

Thursday, December 5, 2013 11:53 AM âś…Answered

Hello

I know about profiler. But I want to know from the sql side like at server side I should know that which comes from visual studio n which comes from application..

So for that I found one way.

we can set Application Name variable of connection string. what ever we set there it will reflect in Programe_Name in sysProcesses data. SO I can know which comes from my application & I can Ignore other connections.

like ConnectionString  +";Application Name=\MyAPP\";

Thanks & Regards

Vipul langalia

Wednesday, December 4, 2013 3:45 PM

Hello Vipul, 

 If I understood your question correctly, you need the program name(application) that is using the process inside SQL server (like you see in the activity monitor). You cannot see the process list like in task manager, because that is at windows level and you will not get details on which application is accessing your database.  the ones you see in task manager are just services, sql server is service running on windows OS and that is what you see in Task manager. To see what applications are connected to DB, you find that info in sql server.  your query is almost right,  this should work 

SELECT
    DB_NAME(dbid) as DBName, 
    hostprocess,
 loginame as LoginName,
 program_name
FROM
    sys.sysprocesses
WHERE 
    dbid > 0 and
 DB_NAME(dbid) = 'Northwind' and
 program_name != 'Microsoft SQL Server Management Studio - Query'

there is also, sp_who2 that can give similar info but comprehensive.

If you are more interested, google/bing for sp_whoisactive.

Hope it Helps!!

Wednesday, December 4, 2013 4:04 PM

Hello Vipul,

You can also have a look at below two DMVs.Please see list of queries mentioned in both articles it will be helpful to you

sys.dm_exec_session

sys.dm_exec_connection

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Thursday, December 5, 2013 4:58 AM

Hello All

Still I am not getting Process Name. Actualy I want process Name for following purpose.

I can connect to database in sql server using my application, Or using Visual studio by connecting using Server Explorer. So in both the case it is showing as ".Net SqlClient Data Provider". So I can not differenciate the connection source.

For that I want process Name..

In short I want to get Process Name. So can you please help me in detail regarding this?

Thanks & Regards

Vipul Langalia

Thursday, December 5, 2013 5:10 AM

Hello All

Still I am not getting Process Name. Actualy I want process Name for following purpose.

I can connect to database in sql server using my application, Or using Visual studio by connecting using Server Explorer. So in both the case it is showing as ".Net SqlClient Data Provider". So I can not differenciate the connection source.

For that I want process Name..

In short I want to get Process Name. So can you please help me in detail regarding this?

Thanks & Regards

Vipul Langalia

Hello,

AFAIK, SQL server will provide you generic name not different name for different process.Did you checked out two DMVs I provided

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Thursday, December 5, 2013 5:48 AM

Hello

Yes I check it But I can not differenciate that which process is from Visual studio n which process is from applicaiton.

Like visual studio can also connect to sql server's particualar database. So it will show it as seperate process.

Any application which can also connect to sql server so it will show it as seperate process.

So I am analysing your sugested DVMs But had hard luck with that.. Is there any other solution for it?

Thanks & Regards

Vipul

Thursday, December 5, 2013 7:15 AM

Hello

Yes I check it But I can not differenciate that which process is from Visual studio n which process is from applicaiton.

Like visual studio can also connect to sql server's particualar database. So it will show it as seperate process.

Any application which can also connect to sql server so it will show it as seperate process.

So I am analysing your sugested DVMs But had hard luck with that.. Is there any other solution for it?

Thanks & Regards

Vipul

You can use profiler to capture events from visual studio and other connections see this link

http://technet.microsoft.com/en-us/library/ms181091.aspx

and

http://msdn.microsoft.com/en-us/library/ff650699.aspx

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers