Hi! I suggest you to run SQL Server Profiler to gather full informaion about what's going
on with the database and a detailed information about locks (Please refer to this link for details:
http://msdn.microsoft.com/en-us/library/ms173789(v=sql.105).aspx[
^]).
Then try to use Database Engine Tuning Advisor to solve these issues.
upd.
In sys.sysprocesses we can see processes information, but we need a process or processes which are getting blocked
select * from sys.sysprocesses where spid >= 50 and blocked <> 0
also you may need to use this query:
SELECT loginame, cpu, memusage, physical_io, spa.*
FROM master..sysprocesses spa
WHERE EXISTS( SELECT spb.*
FROM master..sysprocesses spb
WHERE spb.blocked > 0 and
spb.blocked = spa.spid )
AND NOT EXISTS ( SELECT spc.*
FROM master..sysprocesses spc
WHERE spc.blocked > 0 AND
spc.spid = spa.spid )
ORDER BY spa.spid
This query will give you information about how many shared locks you have
SELECT l.resource_type, l.request_mode, l.request_status, l.request_session_id
FROM sys.dm_tran_locks AS l
WHERE resource_database_id = DB_ID();
If in won't help, try to search locks with solution from this article:
http://www.techrepublic.com/blog/datacenter/find-blocking-processes-using-recursion-in-sql-server-2005/275[
^]