Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_who2 Expand / Collapse
Author
Message
Posted Monday, August 31, 2009 1:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
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
Post #780220
Posted Monday, August 31, 2009 2:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 1,800, Visits: 1,546
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?
Post #780238
Posted Monday, August 31, 2009 2:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
I am getting blank.
Post #780268
Posted Monday, August 31, 2009 2:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
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?
Post #780272
Posted Monday, August 31, 2009 3:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:35 PM
Points: 100, Visits: 405
Tara,

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

Thanks,
Raj
Post #780290
Posted Monday, August 31, 2009 4:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 17,812, Visits: 15,737
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
Post #780335
Posted Monday, August 31, 2009 5:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 889, Visits: 1,453
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.
Post #780360
Posted Tuesday, September 1, 2009 5:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #780598
Posted Tuesday, September 1, 2009 7:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 1,800, Visits: 1,546
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.
Post #780674
Posted Tuesday, September 1, 2009 7:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 1,800, Visits: 1,546
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)
Post #780681
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse