I created a table with the output of sp_lock and I added a new column object_name. Is there a way to populate ALL the object_name rows based on the objid and dbid ? I can do this row by row using : use db_name(from sysdatabases) and select object_name(objid) but I would like to have a script to update all the rows. Thanks
just run an update my_table set objectname = object_name(id) also, you could write your own sp_lock sproc and add object_name(id) directly there
Hi Bambola, Your sugestion will not work. In the output of sp_lock I have different dbid and object id. DBID OBJID 5 131242345 5 456567787 6 111111224 6 235457658 7 243454758 Object_name(id) will give me the table names from master..sysobjects(I am running sp_lock from master). I still need a way to switch between databases.
It will run if you could join sysobjects & sysdatabases together to get the table name. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
This script is kind of a pain and I need to do a lot of work on it, but it works. <img src='/community/emoticons/emotion-1.gif' alt='' /> You will need to hardcode in the name of your databases. On the good side, once you have it setup, it works.<br /><br />-- sp_who 226<br />-- dbcc inputbuffer(311) <br />-- sp_lock<br />-- kill 409<br />-- select * from sysprocesses where status <> 'sleeping' order by cpu desc<br />-- select * from sysprocesses where hostname = 'KCW2KSAPP8'<br /><br />-- select * from sysprocesses with(nolock) where waitresource like '2%' order by cpu<br />-- select * from sysprocesses with(nolock) where waitresource like '2%' order by physical_io<br />-- select * from sysprocesses with(nolock) where waitresource like '2%' order by memusage<br />-- <br />-- select * from tempdb..sysobjects where id = 1<br />-- dbcc outputbuffer(209)<br />-- select * from sysprocesses where spid = 209<br /><br />--dbcc inputbuffer(101)<br />--select * from sysprocesses where blocked <> 0<br /><br /><br /><br /><br />PRINT 'locked objects on ops'<br />PRINT ' '<br />SELECT <br />sd.name AS DatabaseName,<br />CASE<br />WHEN DB_NAME(sd.dbid) = 'PE' THEN (SELECT name FROM PE.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'chat_suite' THEN (SELECT name FROM chat_suite.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'complaint' THEN (SELECT name FROM complaint.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'lending_tree_leads' THEN (SELECT name FROM lending_tree_leads.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'los' THEN (SELECT name FROM los.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'master' THEN (SELECT name FROM master.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'metrics' THEN (SELECT name FROM metrics.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'model' THEN (SELECT name FROM model.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'msdb' THEN (SELECT name FROM msdb.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'nfi' THEN (SELECT name FROM nfi.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'nhm' THEN (SELECT name FROM nhm.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'oneloan_external' THEN (SELECT name FROM oneloan_external.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'ops' THEN (SELECT name FROM ops.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'ops_archive' THEN (SELECT name FROM ops_archive.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'ops_credit' THEN (SELECT name FROM ops_credit.dbo.sysobjects WHERE id = sl.rsc_objid)<br />--WHEN DB_NAME(sd.dbid) = 'ops_credit_archive' THEN (SELECT name FROM ops_credit_archive.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'ops_nsis' THEN (SELECT name FROM ops_nsis.dbo.sysobjects WHERE id = sl.rsc_objid)<br />-- WHEN DB_NAME(sd.dbid) = 'ops_nsis_archive' THEN (SELECT name FROM ops_nsis_archive.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'payment' THEN (SELECT name FROM payment.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'servicing' THEN (SELECT name FROM servicing.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'system_admin' THEN (SELECT name FROM system_admin.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'tempdb' THEN (SELECT name FROM tempdb.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'training' THEN (SELECT name FROM training.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'web_home' THEN (SELECT name FROM web_home.dbo.sysobjects WHERE id = sl.rsc_objid) <br />WHEN DB_NAME(sd.dbid) = 'web_nhm' THEN (SELECT name FROM web_nhm.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'web_retail' THEN (SELECT name FROM web_retail.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> END AS ObjectName,<br />sl.req_spid AS LockSPID,<br />sp.blocked AS BlockedSPID,<br />sl.rsc_text AS LockText,<br />CASE<br />WHEN sl.rsc_type = 1 THEN 'Resource Not Used'<br />WHEN sl.rsc_type = 2 THEN 'Database'<br />WHEN sl.rsc_type = 3 THEN 'File'<br />WHEN sl.rsc_type = 4 THEN 'Index'<br />WHEN sl.rsc_type = 5 THEN 'Table'<br />WHEN sl.rsc_type = 6 THEN 'Page'<br />WHEN sl.rsc_type = 7 THEN 'Key'<br />WHEN sl.rsc_type = 8 THEN 'Extent'<br />WHEN sl.rsc_type = 9 THEN 'RID'<br />WHEN sl.rsc_type = 10 THEN 'Application'<br />END AS LockType,<br />CASE<br />WHEN sl.req_status = 1 THEN 'GRANTED'<br />WHEN sl.req_status = 2 THEN 'CONVERTING'<br />WHEN sl.req_status = 3 THEN 'WAITING'<br />END AS LockStatus,<br />CASE<br />WHEN sl.req_mode = 0 THEN 'No Access Granted'<br />WHEN sl.req_mode = 1 THEN 'Sch-S: Schema Stability - No Drops'<br />WHEN sl.req_mode = 2 THEN 'Sch-M: Schema Modification - No Other References Allowed'<br />WHEN sl.req_mode = 3 THEN 'S: Shared - Shared Access'<br />WHEN sl.req_mode = 4 THEN 'U: Update - May Eventually Update'<br />WHEN sl.req_mode = 5 THEN 'X: Exclusive - Exclusive Access To Resource'<br />WHEN sl.req_mode = 6 THEN 'IS: Intent Shared - Intent To Acquire S Locks On Subordinate Resource'<br />WHEN sl.req_mode = 7 THEN 'IU: Intent Shared - Intent To Acquire U Locks On Subordinate Resource'<br />WHEN sl.req_mode = 8 THEN 'IX: Intent Shared - Intent To Acquire X Locks On Subordinate Resource'<br />WHEN sl.req_mode = 9 THEN 'SIU: Shared Intent Update - Shared Access With Intent Of Acquiring Update Locks On Subordinate Resources'<br />WHEN sl.req_mode = 10 THEN 'SIX: Shared Intent Exclusive - Shared Access With Intent Of Acquiring Exclusive Locks On Subordinate Resources'<br />WHEN sl.req_mode = 11 THEN 'UIX: Update Intent Exclusive - Update Lock Held With Intent Of Acquiring Exclusive Locks On Subordinate Resources'<br />WHEN sl.req_mode = 12 THEN 'BU: Bulk Operation Lock'<br />WHEN sl.req_mode = 13 THEN 'RangeS_S: Shared Key-Range and Shared Resource Lock - Shared Locks For Serializable Range Scan.'<br />WHEN sl.req_mode = 14 THEN 'RangeS_U: Shared Key-Range and Update Resource Lock - Update Locks For Serializable Update Scan.'<br />WHEN sl.req_mode = 15 THEN 'RangeI_N: Insert Key-Range and Null Resource Lock - Shared Locks To Test Range For Insertion Of New Key In Index.'<br />WHEN sl.req_mode = 16 THEN 'RangeI_S: Key-Range Conversion Lock - Created by an overlap of RangeI_N and S Locks.'<br />WHEN sl.req_mode = 17 THEN 'RangeI_U: Key-Range Conversion Lock - Created by an overlap of RangeI_N and U Locks.'<br />WHEN sl.req_mode = 18 THEN 'RangeI_X: Key-Range Conversion Lock - Created by an overlap of RangeI_N and X Locks.'<br />WHEN sl.req_mode = 19 THEN 'RangeX_S: Key-Range Conversion Lock - Created by an overlap of RangeI_N and S Locks.'<br />WHEN sl.req_mode = 20 THEN 'RangeX_U: Key-Range Conversion Lock - Created by an overlap of RangeI_N and S Locks.'<br />WHEN sl.req_mode = 21 THEN 'RangeX_X: Exclusive Key-Range And Exclusive Resource Lock - Conversion Locks For Update A Key In A Range.'<br />END AS LockRequestMode,<br />CASE<br />WHEN sl.req_ownertype = 1 THEN 'Transaction'<br />WHEN sl.req_ownertype = 2 THEN 'Cursor'<br />WHEN sl.req_ownertype = 3 THEN 'Session'<br />WHEN sl.req_ownertype = 4 THEN 'ExSession'<br />END AS LockOwnerType,<br />CASE<br />WHEN (sp.blocked = 0 AND (SELECT DISTINCT blocked FROM master.dbo.sysprocesses WITH(NOLOCK) WHERE blocked = sl.req_spid) IS NOT NULL) THEN 3<br />WHEN (sp.blocked <> 0 AND (SELECT DISTINCT blocked FROM master.dbo.sysprocesses WITH(NOLOCK) WHERE blocked = sp.blocked) IS NOT NULL) THEN 2<br />ELSE 1<br />END AS LockOccurenceTypeOrder<br />FROM <br />master..syslockinfo sl WITH(NOLOCK) <br />INNER JOIN master..sysdatabases sd WITH(NOLOCK) ON sl.rsc_dbid = sd.dbid<br />INNER JOIN master..sysprocesses sp WITH(NOLOCK) ON sl.req_spid = sp.spid<br />WHERE<br />sl.rsc_objid IS NOT NULL<br />AND sl.rsc_objid <> ''<br />AND (sp.spid IN (SELECT DISTINCT blocked FROM master.dbo.sysprocesses WITH(NOLOCK))<br />OR blocked <> 0)<br />ORDER BY <br />LockOccurenceTypeOrder<br /><br />--Show all blcked processes<br />select * from master.dbo.sysprocesses WITH(NOLOCK) where blocked <> 0<br /><br />PRINT 'connections by host'<br />PRINT ' '<br />SELECT <br />hostname, <br />COUNT(*) AS HostConnections,<br />(SELECT COUNT(*) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />hostname <br />ORDER BY <br />2 DESC<br /><br />PRINT 'connections by database'<br />PRINT ' '<br />SELECT <br />sd.name, <br />COUNT(sp.spid) AS HostConnections,<br />(SELECT COUNT(spid) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses sp with(nolock) <br />INNER JOIN master.dbo.sysdatabases sd with(nolock) ON sp.dbid = sd.dbid<br />GROUP BY <br />sd.name <br />ORDER BY <br />2 DESC<br /><br />PRINT 'connections by loginame'<br />PRINT ' '<br />SELECT <br />loginame, <br />--hostname,<br />COUNT(*) AS HostConnection<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />loginame--,<br />--hostname<br />ORDER BY <br />2 DESC<br /><br />PRINT 'connections by loginame/hostname'<br />PRINT ' '<br />SELECT <br />loginame, <br />hostname,<br />COUNT(*) AS HostConnection<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />loginame,<br />hostname<br />ORDER BY <br />1,2 DESC<br /><br />PRINT 'connections by host/program'<br />PRINT ' '<br />SELECT <br />hostname,<br />program_name, <br />COUNT(*) AS HostConnections,<br />(SELECT COUNT(*) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />program_name,<br />hostname<br />ORDER BY <br />1,2<br /><br />PRINT 'connections by Client PID'<br />PRINT ' '<br />SELECT<br />hostprocess,<br />COUNT(*) AS HostConnections,<br />(SELECT COUNT(*) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY<br />hostprocess<br />ORDER BY<br />2 DESC<br /><br />--DBCC CACHESTATS<br />--DBCC MEMORYSTATUS<br />--DBCC SHOWCONTIG <br />--DBCC DBREINDEX ('ops_nsis.dbo.loan',idx_loan__broker_id__created_by__is_prequal__cur_status__cancelled__loan_id)<br />--DBCC SHOWCONTIG(loan) WITH TABLERESULTS, ALL_INDEXES, ALL_LEVELS<br />--DBCC INDEXDEFRAG(ops_nsis,loan,idx_loan__broker_id__created_by__is_prequal__cur_status__cancelled__loan_id)<br /><br />-- select count(*) from ops_credit..archivable_credit_reports where isArchived = 0<br /><br />-- select <br />-- count(*), <br />-- CAST(CAST(DATEPART(MM, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,pull_date)AS VARCHAR(4)) AS DATETIME)<br />-- from <br />-- ops_credit.dbo.credit_report<br />-- group by<br />-- CAST(CAST(DATEPART(MM, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,pull_date)AS VARCHAR(4)) AS DATETIME)<br />-- order by<br />-- 2 desc<br />-- <br /><br />-- SELECT <br />-- COUNT(*),<br />-- is_prequal,<br />-- CAST(CAST(DATEPART(MM, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,create_date)AS VARCHAR(4)) AS DATETIME)<br />-- FROM <br />-- ops_nsis.dbo.loan<br />-- GROUP BY<br />-- is_prequal,<br />-- CAST(CAST(DATEPART(MM, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,create_date)AS VARCHAR(4)) AS DATETIME)<br />-- ORDER BY 3 desc<br />-- <br />-- <br />-- dbcc inputbuffer(86)<br />-- dbcc inputbuffer(75)<br />-- kill 75<br />-- dbcc outputbuffer(75)<br /><br /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
Derrickleggett, This is not an elegant solution : WHEN DB_NAME(sd.dbid) = 'PE' THEN (SELECT name FROM PE.dbo.sysobjects WHERE id = sl.rsc_objid) you still need to manually enter all database names on your server.
Suppose @tbl_sp_who is the table with the output of sp_who declare @tbl_sp_who table (dbid int, objid int ....) you can hard code it select o.name from @tbl_sp_who t inner join my_db.dbo.sysobjects o on t.dbid = @dbid and o.id = t.objid or create a small table with all database names, loop over it and dynamically create your update statement. '...inner join ' + db_name(id) + '.dbo.' + 'sysobjects...'
I said it wasn't elegant. Read the post. It works though; and when you've had it around for years it takes time to change. <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
Finally I created something like this : DECLARE test CURSOR FOR SELECT b.name,a.objid from #lock a inner join master..sysdatabases b on a.dbid=b.dbid OPEN test declare @dbname varchar(30) declare @id int FETCH NEXT FROM test into @dbname,@id WHILE @@FETCH_STATUS = 0 BEGIN select @execstr =ISNULL(@execstr + CHAR(13), '')+'USE '+CAST(@dbname AS VARCHAR)+' Update #lock SET objname= (Select name from sysobjects where id='+CAST(@id AS VARCHAR)+')' exec(@execstr) FETCH NEXT FROM test into @dbname,@id END Regards
