SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Lock on table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 02/25/2013 :  03:56:06  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/25/2013 :  04:15:00  Show Profile  Reply with Quote
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

India
8 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/25/2013 :  05:57:52  Show Profile  Reply with Quote
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

India
8 Posts

Posted - 02/25/2013 :  06:01:43  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/25/2013 :  06:14:11  Show Profile  Reply with Quote
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

India
8 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/26/2013 :  01:09:19  Show Profile  Reply with Quote
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

India
8 Posts

Posted - 02/26/2013 :  02:35:25  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/26/2013 :  03:05:18  Show Profile  Reply with Quote
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

India
8 Posts

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

hasondea
Starting Member

USA
3 Posts

Posted - 03/19/2013 :  04:35:35  Show Profile  Reply with Quote
I am updating and the same is getting blocked.


unspammed
Go to Top of Page

gfjfgt
Starting Member

3 Posts

Posted - 04/21/2013 :  23:13:50  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000