SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is sysprocesses and what can it do for you?


What is sysprocesses and what can it do for you?

Author
Message
edwardelliott
edwardelliott
Right there with Babe
Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)

Group: General Forum Members
Points: 773 Visits: 351
Comments posted to this topic are about the item What is sysprocesses and what can it do for you?
SuperDBA-207096
SuperDBA-207096
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3755 Visits: 711
Question - how does this relate to sp_who and sp_who2, and the activty monitor in SSMS?
Andrew Peterson
Andrew Peterson
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2248 Visits: 751
Thanks for a great review. For those of us who have been using sysprocesses as well as sp-who, sp_who2 for along time, it never hurts to have a review.

BTW.. sp_who & sp_who2 use info from sysprocesses.
sysprocesses goes back to the original Sybase design, so it has been around for a while.

The more you are prepared, the less you need it.
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6917 Visits: 1619
Good review of sysprocess table. Every DBA should know about this table. Though I know a couple of ones who are used to using sp_who2 but does have no idea where the data comes from.

2005 and 2008 has an extra column request_id.

SQL DBA.
edwardelliott
edwardelliott
Right there with Babe
Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)

Group: General Forum Members
Points: 773 Visits: 351
Hi Mark,

Thanks Andrew and Sanjay.

The sysprocesses table is wrapped by sp_who* and the activity monitor but they do not give you as much detail, if you look at the definition for sp_who it is:

CREATE PROCEDURE sys.sp_who 
select spid,
ecid,
status,
loginame=rtrim(loginame),
hostname,
blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh



so it is useful but I think when troubleshooting issues knowing how to use sysprocessses and what everything means is essential.

It is also good for scripting because for instance you can join against itself on blocked = spid to get information on what queries are being blocked and also what it is that is blocking them.




Ed
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6396 Visits: 5211
What a co-incidence...

On thursday, I was struggling to get the Detailed information for each column in sys.sysprocesses. And here we are....

Very useful article, especially for me.

Thanks

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Wayne West
Wayne West
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8312 Visits: 3702
A friend of mine wanted to conduct a license audit of her installation and used sysprocesses to get the info. On each server she created a DTS job that ran the following query:
select getdate() as StatsDate, ss.srvname as ServerName, 
rtrim(db.name) as DBName, rtrim(hostname) as HostName,
rtrim(program_name) as ProgramName, rtrim(nt_domain) as Domain,
rtrim(nt_username) as UserName, rtrim(net_address) as MACAddress,
rtrim(loginame) as LoginName
from master..sysprocesses sp
join master..sysdatabases db
on sp.dbid = db.dbid
cross join master..sysservers ss
where sp.loginame <> 'sa'
order by db.name, hostname, loginame


then wrote it to a text file. She ran it at the top of the hour, on some days every 5 or 15 minutes, over an extended period of time and collected the scripts on her machine via a scheduled task that ran at 5 minutes after the hour that did this:
copy C:\SQLUserStats\ConnectionStats.txt + \\server1\c$\SQLUserStats\sqluserstats.txt C:\SQLUserStats\ConnectionStats.txt
copy C:\SQLUserStats\ConnectionStats.txt + \\server2\c$\SQLUserStats\sqluserstats.txt C:\SQLUserStats\ConnectionStats.txt
etc.


Worked like a charm. Suck the text file into a table and you can slice and dice the data to your heart's contentment.

One thing it would not show: if connections came through a gateway or application server that could spoof a MAC address or user name, you could have several dozen users hidden that you won't readily see. You also wouldn't see users who logged in for less than an hour and didn't cross that top of the hour boundary, so there were days that it ran every 15 minutes or every 5 minutes. Still, it was deemed adequate by her management to give a reasonable number of user connections.

Regardless, sysprocesses saved a whole lot of work for her.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Yitzchok Lavi
Yitzchok Lavi
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 217
Wayne West (5/5/2009)
A friend of mine wanted to conduct a license audit of her installation and used sysprocesses to get the info...

I'm doing something similar. I'm checking sysprocesses every 5 minutes to look for users with certain patterns.
I ended up here because I have been seeing one user whose sessions sometimes show up without a value in loginame! I used Profiler to watch logins and logouts and I see the session, and there is a value for the login name. When run from SSMS the query has never shown this blank loginame, but I do track it when the query is run from Server Agent.
Does anyone have any idea how a session could legitimately show up in sysprocesses without a loginame? And if I don't see it in that column can I get it from somewhere else? (the case I know about is an SQL user)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search