Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Lock on table

Author  Topic 

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-02-25 : 03:56:06
Hi All,

We have a SSIS package having serializable transactions level.While updating its taking lot of time and gettign locked but i was unable to find out the which another process is blocking how to find this out ?

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 04:15:00
sp_who2 will give you current processes in server.

sp_who2 'active'
will list active processes. Look for BlkBy column and you'll get details of process being blocked ie ones having a valid value for BlkBy and the value there corresponds to ID of process blocking it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ms.gangadhar
Starting Member

8 Posts

Posted - 2013-02-25 : 05:46:35
I am getting blkby column value as -2 which is system process.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 05:57:52
hmm...system process blocking? what exactly does your process do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ms.gangadhar
Starting Member

8 Posts

Posted - 2013-02-25 : 06:01:43
its an SSIS package inside the sequence container lot of tasks are exist at last we have simple update task which updates status to completed which is getting hanged with system process as blocked.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 06:14:11
does the table have clustered index? is there any other parallel operation happening on table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ms.gangadhar
Starting Member

8 Posts

Posted - 2013-02-25 : 06:18:51
yes it has the clustered Index. and no other parallel operations.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 01:09:19
what does description for system process say which blocks your process?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ms.gangadhar
Starting Member

8 Posts

Posted - 2013-02-26 : 02:35:25
SPID 2 is blocking the update statement when i run SP_who. But When I go and see there in the sys.dm_tran_locks table it says some foreign key is holding a lock.This foreign key references to the primary key of the table which I am updating and the same is getting blocked.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 03:05:18
is the other table accessed inside same ssis itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ms.gangadhar
Starting Member

8 Posts

Posted - 2013-02-26 : 03:30:22
Yes it is accessed but there is no exclusive lock on that table
Go to Top of Page

hasondea
Starting Member

3 Posts

Posted - 2013-03-19 : 04:35:35
I am updating and the same is getting blocked.


unspammed
Go to Top of Page

gfjfgt
Starting Member

3 Posts

Posted - 2013-04-21 : 23:13:50
unspammed
Go to Top of Page
   

- Advertisement -