SPID

Question

SPID

shiv-356842

SSCarpal Tunnel

Points: 4310
More actions
August 3, 2009 at 9:13 am

#134556

Hi Everybody,
SPID where it will be stored in MS SQL SERVER
i hope it is stored in MAster database or msdb which table it will be stored could you plz help me asp
Regards
raj

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'm not sure what you are asking. You can see the SPID as session_id in sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

shiv-356842 SSCarpal Tunnel Points: 4310 More actions · Answer 2

Hi Jack,

Thank you for your reply

I am asking spid where it stored in MS SQL SERVER.

Thank you very much.....

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Those are the places I know of where you can retrieve information about the SPID. Well those and using the @@SPID function.

I'm still not sure what you mean by storing SPID? SPID's are session id's so when a session ends that SPID can be re-used. The system views, the ones I mentioned and some others, can give you all the information about what a specific SPID is doing.

If this is related to your other post, you can find lock information from sys.dm_tran_locks using request_session_id.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Chand00 SSCertifiable Points: 6466 More actions · Answer 4

Hi Shiv,

SPID is a unique id number given by sql server to uniquely identify user connections. It is not stored in any of the databases.

for better understanding try this scenario

1. connect to your sql server

2. run the following query

select @@spid

3. disconnect from sql server

4. connect to your sql server again

5. run the following query

select @@spid

Output from step 2 and step 5 will be different if you have many users who are connected or getting connected to databases. If you are connecting to a sql server where there is less user activity or to your pc there is a high chance that you may get the same processid.

[font="Verdana"]Thanks
Chandra Mohan[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 5

Microsoft have re-invented the SPID - it now refers to the session ID of the current user process.

Books Online
This is called the server process ID in earlier versions of SQL Server.

Session ID (or session_id) is contained in many dynamic management views like sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_tran_session_transactions...to name but a few. These are available in all databases.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

shiv (8/3/2009)
Hi Jack,
Thank you for your reply
I am asking spid where it stored in MS SQL SERVER.
Thank you very much.....

SPID is dynamic. Once a process is completed, it's SPID may be assigned to a new process.