| 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 MSSQL 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
ms.gangadhar
Starting Member
8 Posts |
Posted - 2013-02-25 : 06:18:51
|
| yes it has the clustered Index. and no other parallel operations. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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 |
|
|
|
hasondea
Starting Member
3 Posts |
Posted - 2013-03-19 : 04:35:35
|
| I am updating and the same is getting blocked.unspammed |
|
|
|
gfjfgt
Starting Member
3 Posts |
Posted - 2013-04-21 : 23:13:50
|
| unspammed |
|
|
|
|
Lock on table