SQL Server locks

  • 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

  • is there any one who can help me please??????

  • 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

  • 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?

  • 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

  • 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.

  • 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.

  • 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