August 27, 2009 at 7:45 am
I have SQL Server 2005 and was told me that if any locks were there on a particular database. Now i user SP_who2 to see the output, but i do not know how to read the output.
i the status i see " runnable", "sleeping", "suspended", "background".
so how do i know that something is locking?
pleae advice
August 27, 2009 at 7:58 am
is there any one who can help me please??????
August 27, 2009 at 8:36 am
look at sys.sysprocesses
select * from sys.sysprocesses where blocked > 0
This will return the SPID of the offending transaction (the number in the blocked column is the SPID number that is blocking)
you can then use SP_who2 to see who this is and dbcc inputbuffer() to see what code is being run.
There are smarter more intuitive scripts out there using DMV's but the above will give you a general idea of what is causing a lock
August 27, 2009 at 8:54 am
thanks. and anything else i can do if i can find a lock or some more tricks to check lockings and how do i deal with it?
August 27, 2009 at 9:19 am
I found a script here and created my own stored procedure to identify only active processes, which made my life easier. The name was different, but I named it usp_what. It is a version of sp_who2 and it has been very helpfull so far. Here it is in case you want to test it (always test your new objects before moving them in a live system (dev, stage, production) I use my local to test all this scripts I get from different sources, being Central the best so far. OK, no more blah blah, here it is.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_what] --- 5/9/2006
-- @loginame sysname = NULL
as
set nocount on
declare
@retcode int, @loginame sysname
declare
@sidlow varbinary(85),@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh int
declare
@charMaxLenLoginName varchar(6),@charMaxLenDBName varchar(6),@charMaxLenCPUTime varchar(10),@charMaxLenDiskIO varchar(10),
@charMaxLenHostName varchar(10),@charMaxLenProgramName varchar(10),@charMaxLenLastBatch varchar(10),@charMaxLenCommand varchar(10)
--------
select @retcode = 0 -- 0=good ,1=bad.
--------------------------------------------------------------
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,
convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort',
substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
--------Screen out any rows
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
,'TASK MANAGER'
)
and blocked = 0 or spid <= 50
---set the column widths
UPDATE #tb1_sysprocesses set last_batch = DATEADD(year,-10,GETDATE())
where last_batch IS NULL or last_batch = '01/01/1901 00:00:00' or last_batch = 0 and spid <= 32767
--------Output the report.
EXECUTE(
'SET nocount off
SELECT SPID = convert(char(5),spid)
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,ActiveSeconds = DATEDIFF(ss,last_batch,getdate())
,DBName = substring(case when dbid = 0 then null when dbid 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,BatchStart = CONVERT(varchar(8),last_batch,14)
,Now = CONVERT(varchar(8),getdate(),14)
,LBDate = substring(last_batch_char,1,5)
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
-- ,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
order by CAST(SPID as int)
-- (Seems always auto sorted.) order by SPID
SET nocount on')
drop table #tb1_sysprocesses
--return @retcode
August 27, 2009 at 9:33 am
espanolanthony (8/27/2009)
thanks. and anything else i can do if i can find a lock or some more tricks to check lockings and how do i deal with it?
This script is really useful for sql2005+ databases
SELECT distinct L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
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
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
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
As for dealing with locking you need to start using profiler and work out the types of queries that are causing excessive locking on your database. the answer to locking problems are hugely varied, its not as simple as having a script or tool that will just fix all your locking issues. Locking is a safety mechanism used in databases to stop the same row being updated which an update is already in process. Locking only becomes an issue when you start getting deadlocks (profiler is great for delving into these) or excessive locking whereby your end users start to notice a difference in performance.
August 27, 2009 at 10:06 am
Thank you all, but as of now i only want to see if something is locking and i am sure that these scripts well surely help. Thanks again.
August 27, 2009 at 10:18 am
In the one I sent (usp_what) you will one column named blkBy. That column will tell you who is blocking the process. There are times when you need to figure out who is the main blocker as one process could be block by one and that one is being block by some other process.
Can you kill processes? Ye, but you need to very careful on what you kill. There are times when is better to let the process complete than just kill it.
Profiler will give you a better understanding of what process is making the other to be blocked. It could be bad code, or the code is right but is not using NOLOCK for example. Profiler is one the best help tools you can have within SQL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply