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 12»»

login_name shows up as blank in sys.dm_exec_sessions but not in sp_who2 Expand / Collapse
Author
Message
Posted Monday, August 29, 2011 1:03 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
When I probe sys.dm_exec_sessions (joining with other DMVs to get active-session info) I get the login_name column to be blank for a certain session.
Yet, for the same spid, the Login shows up properly (not blank) when I execute "sp_who2 active".
It also shows up properly in sys.sysprocesses.

What could be the reason for the faulty output of sys.dm_exec_sessions ?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1167118
Posted Monday, August 29, 2011 2:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:52 PM
Points: 31, Visits: 272
I suspect that it is your join that is causing this. Can you supply the query that you are using?
Post #1167177
Posted Monday, August 29, 2011 2:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
BrentMc (8/29/2011)
I suspect that it is your join that is causing this. Can you supply the query that you are using?


Sure, here it is:

--"Performance Tuning with SQL Server Dynamic Management Views", L. Davidson and T. Ford
SELECT
[des].session_id
, [des].[status]
, [des].login_name
, [des].[host_name]
, der.blocking_session_id
, DB_NAME(der.database_id) AS database_name
, der.command
, [des].cpu_time
, [des].reads
, [des].writes
, [dec].last_write
, [des].[program_name]
, der.wait_type
, der.wait_time
, der.last_wait_type
, der.wait_resource
, CASE [des].transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
, OBJECT_NAME( dest.objectid, der.database_id ) AS [object_name]
, dest.[text] AS [executing batch]
, SUBSTRING(
dest.[text], der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH (dest.[text])
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2
) AS [executing statement]
--, deqp.query_plan
FROM
sys.dm_exec_sessions [des]
LEFT JOIN
sys.dm_exec_requests der
ON
[des].session_id = der.session_id
LEFT JOIN
sys.dm_exec_connections [dec]
ON
[des].session_id = [dec].session_id
OUTER APPLY
sys.dm_exec_sql_text(der.sql_handle) dest
--OUTER APPLY
-- sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE
[des].session_id <> @@SPID
AND [des].[status] <> 'sleeping'
ORDER BY
[des].session_id;



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1167179
Posted Tuesday, August 30, 2011 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 10,342, Visits: 13,352
Hmm, interesting. Since I can't duplicate this I have no idea why it would be blank. The source for sysprocesses and sys.dm_exec_sessions are different so the difference must be in the source.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1167673
Posted Tuesday, August 30, 2011 2:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
Jack Corbett (8/30/2011)
Hmm, interesting. Since I can't duplicate this I have no idea why it would be blank. The source for sysprocesses and sys.dm_exec_sessions are different so the difference must be in the source.


Thanks for responding, I'll see if I can find out something more about this.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1167811
Posted Wednesday, August 31, 2011 7:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 10,342, Visits: 13,352
I'll definitely be interested in hearing what you find out.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1168064
Posted Wednesday, August 31, 2011 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 10,342, Visits: 13,352
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1168111
Posted Wednesday, August 31, 2011 8:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
Jack Corbett (8/31/2011)
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?


I get it for something called "Entity Framework", but I don't know much else about it. I would need to ask the devs about that.
We don't use MS Access on that server.
I also just saw a linked server query running on the server in question, linking to another server; login name was blank for that session as well.

Unfortunately that's all I have at the moment.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1168121
Posted Wednesday, August 31, 2011 8:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
What do you get from the original_login_name column? And what does "blank" mean? NULL? Or an empty string?

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #1168122
Posted Wednesday, August 31, 2011 8:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
Adam Machanic (8/31/2011)
What do you get from the original_login_name column? And what does "blank" mean? NULL? Or an empty string?


I get empty string.
I will add the original_login_name column in the output.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1168123
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse