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.
I connected to our Production SQL Instance and like Lionel Richie I am stuck on you, as well.
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.
- 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
As sp_whoisActive identified the exact blocked statement captured that statement from the sql_text column.
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.
Issued sp_lock against the blocking session id.
exec sp_lock [session-id]
exec sp_lock 56
- There are quite a bit of locks
- The entries marked with Mode=X
- X are Exclusive Locks
- Master Database
- 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ć
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
- The impacted objects are:
I hope the problem re-occurs in the future and we will hopefully have setup better monitoring and alerting.