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


sp_who2


sp_who2

Author
Message
Tara-1044200
Tara-1044200
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1831 Visits: 3077
I am trying to check user activity for a particualr user from activity monitor, I see application as " Micorsoft SQL Server " so that means it is not a MSMS query and the database is seen as " master" .

this user is using too much CPU but i cudnt find what databases he is querying, how can i find that.
I tried sql profiler filetring on this user but i dont see any records. How can i track this guy.

thanks
Mayank Khatri
Mayank Khatri
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: 2232 Visits: 1730
If you know the SPID of that particular user then use DBCC INPUTBUFFER or fn_get_sql as follows:-
------------------------------------------------
DECLARE @HANDLE BINARY(20)

SELECT @HANDLE = sql_handle from sys.sysprocesses where spid =

SELECT text FROM ::fn_get_sql(@handle)
-------------------------------------------------


to get the text of the query, and then by that text u can know the database. How many user databases are there on that server?
Tara-1044200
Tara-1044200
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1831 Visits: 3077
I am getting blank.
Tara-1044200
Tara-1044200
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1831 Visits: 3077
I think if the application is MSMS Query then i can get some text result for the above query but for this user ther application used says " Microsoft SQL Server" what does it mean?
rajdba
rajdba
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 430
Tara,

Is the SPID getting changed?
Is the SPID value is less than 50?
What is the Version of your SQL Server?

Thanks,
Raj
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40549 Visits: 18565
Does that particular user have any other processes running?


I have seen in the past where a user was connected via SSMS and the Application showed as Microsoft SQL Server. The user ended up with a couple of spids, 1 in master and 1 in the user database. When the user disconnected and logged off - the user database spid went away but the spid in master remained.

What is the last batch time stamp of that user? How long ago did the user login to the db? Can you go and ask the user, or is it a service account or generic account?

I like to use the following query in conjunction with sp_who2 to find a little more detail on the running queries.

select percent_complete, estimated_completion_time, cpu_time/60000 as CPUMinutes, total_elapsed_time/60000 as TotalMinutes,session_id
,start_time,command, st.*,sql_handle,plan_handle,database_id,wait_time,last_wait_type
,wait_resource,transaction_id,reads,writes,logical_reads
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) AS st



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4538 Visits: 3397
Thanks Mayank, JAson for all your response..

I have noticed one more thinj..

(1) While executing the query as:: DBCC inputbuffer(77)--SPID,

I'm getting one result.

---------------------------------
Output::

Exec ImportComponents '459087',''


(2) DECLARE @HANDLE BINARY(20)

SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = 77

SELECT text FROM ::fn_get_sql(@handle)

Executing the above query, getting other result.



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

Output::

UPDATE Material WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917' , LastModifiedBy = 459087 , ChangeID = 2151042 FROM Material WHERE MaterialID = 1466288 UPDATE MaterialComponentDetail WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917', LastModifiedBy = 459087 , ReplacedByPartNumber = 'X15-05816-02' FROM MaterialComponentDetail WHERE MaterialID = 1466288


Can you please suggest why is this so??

Regards
Sourav

Thanks.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55089 Visits: 32797
Better than sp_who2, take a look at the DMV sys.dm_exec_requests. That will show you currently running queries. You can combine it with other DMV/DMF to arrive at the query being run, the execution plan, statistics, locks, etc. It's so much more elegant than using sp_who2.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Mayank Khatri
Mayank Khatri
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: 2232 Visits: 1730
Tara (8/31/2009)
I am getting blank.


If you are getting blank using fn_get_sql then probably your query has zero cost plan, hence not stored in cache. If you turn on flag 2861(By default its off) then SQL text with zero cost plan will be stored in ur memory and hence fn_get_sql will print something. But after you are done, I think u should turn off this flag.
Mayank Khatri
Mayank Khatri
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: 2232 Visits: 1730
Sourav (8/31/2009)
Thanks Mayank, JAson for all your response..

I have noticed one more thinj..

(1) While executing the query as:: DBCC inputbuffer(77)--SPID,

I'm getting one result.

---------------------------------
Output::

Exec ImportComponents '459087',''


(2) DECLARE @HANDLE BINARY(20)

SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = 77

SELECT text FROM ::fn_get_sql(@handle)

Executing the above query, getting other result.



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

Output::

UPDATE Material WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917' , LastModifiedBy = 459087 , ChangeID = 2151042 FROM Material WHERE MaterialID = 1466288 UPDATE MaterialComponentDetail WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917', LastModifiedBy = 459087 , ReplacedByPartNumber = 'X15-05816-02' FROM MaterialComponentDetail WHERE MaterialID = 1466288


Can you please suggest why is this so??

Regards
Sourav


Either your ImportComponents '459087' stored procedure is equivalent of the UPDATE script shown by running fn_get_sql or your SPID was changed when you ran the first INPUTBUFFER and the second fn_get_sql(@handle)
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