/*Starting loop to lock the table for sometime*/
USE
YourDatabase_Name
YourDatabase_Name
GO
DECLARE @ID INT
SET @ID = 1
WHILE @ID < 1000
BEGIN
SELECT
TOP 1 * FROM SALESDATA
TOP 1 * FROM SALESDATA
SET @ID = @ID + 1
END
USE
YourDatabase_Name
YourDatabase_Name
GO
/*script to get user information who has locked the table by
some process*/
some process*/
SELECT
DISTINCT
OBJECT_NAME(SL.RSC_OBJID) AS Table_Name
OBJECT_NAME(SL.RSC_OBJID) AS Table_Name
,SL.REQ_SPID As SPID
,SP.LOGINAME As
UserName
UserName
FROM MASTER.DBO.SYSLOCKINFO SL (NOLOCK)
INNER
JOIN MASTER.DBO.SYSPROCESSES SP (NOLOCK)
JOIN MASTER.DBO.SYSPROCESSES SP (NOLOCK)
ON
SL.REQ_SPID=SP.SPID
SL.REQ_SPID=SP.SPID
WHERE OBJECT_NAME(SL.RSC_OBJID) IS NOT NULL
Here we can see the table name, processed and user name who has locked this
table.