How to find out what is locking my tables?

Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.

For getting straight to "who is blocked/blocking" I combined/abbreviated sp_who and sp_lock into a single query which gives a nice overview of who has what object locked to what level.

--Create Procedure WhoLock--ASset nocount onif object_id('tempdb..#locksummary') is not null Drop table #locksummaryif object_id('tempdb..#lock') is not null Drop table #lockcreate table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))Insert into #lock exec sp_lockif object_id('tempdb..#who') is not null Drop table #whocreate table #who (     spid int, ecid int, status char(30),            loginame char(128), hostname char(128),            blk char(5), dbname char(128), cmd char(16)            --            , request_id INT --Needed for SQL 2008 onwards            --         )Insert into #who exec sp_whoPrint '-----------------------------------------'Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'Print '-----------------------------------------' + Char(10)Select     left(loginame, 28) as loginame,     left(db_name(dbid),128) as DB,    left(object_name(objID),30) as object,    max(mode) as [ToLevel],    Count(*) as [How Many],    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],    l.spid, hostnameinto #LockSummaryfrom #lock l join #who w on l.spid= w.spidwhere dbID != db_id('tempdb') and l.status='GRANT'group by dbID, objID, l.spid, hostname, loginameSelect * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, objectPrint '--------'Print 'Who is blocking:'Print '--------' + char(10)SELECT p.spid,convert(char(12), d.name) db_name, program_name, p.loginame, convert(char(12), hostname) hostname, cmd, p.status, p.blocked, login_time, last_batch, p.spidFROM      master..sysprocesses pJOIN      master..sysdatabases d ON p.dbid =  d.dbidWHERE     EXISTS (  SELECT 1          FROM      master..sysprocesses p2          WHERE     p2.blocked = p.spid )Print '--------'Print 'Details:'Print '--------' + char(10)Select     left(loginame, 30) as loginame,  l.spid,    left(db_name(dbid),15) as DB,    left(object_name(objID),40) as object,    mode ,    blk,    l.statusfrom #lock l join #who w on l.spid= w.spidwhere dbID != db_id('tempdb') and blk <>0Order by mode desc, blk, loginame, dbID, objID, l.status

(For what the lock level abbreviations mean, see e.g. https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx)

Copied from: sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock...

NB the [Xclusive lock for command] column can be misleading -- it shows the current command for that spid; but the X lock could have been triggered by an earlier command in the transaction.

exec sp_lock

This query should give you existing locks.

exec sp_who SPID -- will give you some info

Having spids, you could check activity monitor(processes tab) to find out what processes are locking the tables ("details" for more info and "kill process" to kill it).