SQL Server – sp_helpuser stickiness

Introduction

One of our Application engineers emailed me asking whether something changed as she could no longer execute sp_helpuser on our Production database.

She said she is able to do so on Dev & QA.

 

TroubleShooting

Affirmation

I connected to our Production SQL Instance and like Lionel Richie I am stuck on you, as well.

 

sp_WhoIsActive

Thankfully, we have Adam Machanic’s sp_whoIsActive on our SQL Instances.

Ran it and it came back with a concise list of active sessions.

 

Output

wp_whoIsActive_20161215-0307PM (brushed up)

 

Explanation

  1. My session which happens to be session 63 is blocked by session 56
    • BTW, I am trying to run the same command as the Application Manager
    • And, that command is sp_helpuser

 

sp_helpuser

As sp_whoisActive identified the exact blocked statement captured that statement from the sql_text column.

Code

Code – Original

Here is a copy of it.


SELECT
	   u.name,
	  CASE
		WHEN (r.principal_id IS NULL) THEN 'public'
		ELSE r.name
	  END,
	  l.name,
	  l.default_database_name,
	  u.default_schema_name,
	  u.principal_id,
	  u.sid

FROM sys.database_principals u

LEFT JOIN (
				sys.database_role_members m

				JOIN sys.database_principals r

					ON m.role_principal_id = r.principal_id

	)

	ON m.member_principal_id = u.principal_id

LEFT JOIN sys.server_principals l
	ON u.sid = l.sid

WHERE u.type <> 'R'

Code – Revised

Broke the code down a bit to see which object referenced is being blocked.

Commenting out the reference to sys.server_principals.


SELECT
	   u.name,
	  CASE
		WHEN (r.principal_id IS NULL) THEN 'public'
		ELSE r.name
	  END,
	  --l.name,
	  --l.default_database_name,
	  u.default_schema_name,
	  u.principal_id,
	  u.sid

FROM sys.database_principals u

LEFT JOIN (
				sys.database_role_members m

				JOIN sys.database_principals r

					ON m.role_principal_id = r.principal_id

	)

	ON m.member_principal_id = u.principal_id

/*
LEFT JOIN sys.server_principals l
	ON u.sid = l.sid

*/
WHERE u.type <> 'R'

The query came back promptly.

And, so knew that the the object that we had problem with is sys.server_principals.

This realization will play an important role as we narrow into a specific database and object names.

sp_lock

Issued sp_lock against the blocking session id.

syntax

 
exec sp_lock [session-id]
 

 

sample

 
exec sp_lock 56
 

 

output

sp_lock__20161215__0731PM

 

Explanation

  1. There are quite a bit of locks
  2. The entries marked with Mode=X
    • X are Exclusive Locks
    • DBID=1
      • Master Database
    • DBID=5
      • User Database

 

Personally, I will like to get clearer data rather than than the Object ID provided by sp_lock.

 

Mladen Prajdić, Microsoft MVP

Here is a very nice code written and shared by Microsoft MVP, Mladen Prajdić

Code

Here is the link to the code.

And, pasted below is the actual code.

 
/*
	Mladen Prajdić 
	I'm from Slovenia
	MPV
	SQL Server 2005: Get full information about transaction locks
	Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...

	With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
	http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

*/
use [master]
go


SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod,
		tblSP.program_name,
		tblSP.loginame,
		tblSP.last_batch,
		[durationSinceLastBatch]
			= datediff(minute, tblSP.last_batch, getdate())
		
FROM  sys.dm_tran_locks L

JOIN  sys.partitions P
 
	ON P.hobt_id = L.resource_associated_entity_id

JOIN sys.objects O
 
	ON O.object_id = P.object_id

JOIN sys.dm_exec_sessions ES
 
	ON ES.session_id = L.request_session_id

JOIN sys.dm_tran_session_transactions TST 
	ON ES.session_id = TST.session_id

JOIN sys.dm_tran_active_transactions AT 
	ON TST.transaction_id = AT.transaction_id

JOIN sys.dm_exec_connections CN 
	ON CN.session_id = ES.session_id

CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

LEFT OUTER JOIN sys.dm_exec_requests ER 
	ON ES.session_id = ER.session_id

INNER JOIN master.dbo.sysprocesses tblSP 
	ON  ES.session_id = tblSP.spid

WHERE   resource_database_id = db_id()

ORDER BY 
		L.request_session_id
		

The code above needs to be ran on each database that we need to investigate for locks

 

 

Output:

201612150250PM (brushed up)

 

 

Explanation:

  1. The impacted objects are:
    • master..sysxlgns
    • master..sysobjvalues

 

Follow Up

I hope the problem re-occurs in the future and we will hopefully have setup better monitoring and alerting.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s