How can I 'free' the table?

Question

Hi everybody,

I'm trying to figure out a problem with the stored procedure. I executed it several times and something got broken. I'm unable to do a single row insert into a particular table or do any query without WITH (NOLOCK) with the WHERE clause. How can I find out what is wrong with that table and get it working again?

I've disconnected all queries, but it didn't help.

Thanks in advance.

Answer 1

Hello Naomi,

Sounds like blockings on the table. You can check existings locks with the following script; if there is an open transaction causing locks you could "kill" the process = session_id

-- List all Locks of the Current Database
SELECT TL.resource_type AS ResType
      ,TL.resource_description AS ResDescr
      ,TL.request_mode AS ReqMode
      ,TL.request_type AS ReqType
      ,TL.request_status AS ReqStatus
      ,TL.request_owner_type AS ReqOwnerType
      ,TAT.[name] AS TransName
      ,TAT.transaction_begin_time AS TransBegin, GETDATE() as CurrDate
      ,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
      ,ES.session_id AS S_Id
      ,ES.login_name AS LoginName
      ,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
      ,PARIDX.name AS IndexName
      ,ES.host_name AS HostName
      ,ES.program_name AS ProgramName
FROM sys.dm_tran_locks AS TL
     INNER JOIN sys.dm_exec_sessions AS ES
         ON TL.request_session_id = ES.session_id
     LEFT JOIN sys.dm_tran_active_transactions AS TAT
         ON TL.request_owner_id = TAT.transaction_id
            AND TL.request_owner_type = 'TRANSACTION'
     LEFT JOIN sys.objects AS OBJ
         ON TL.resource_associated_entity_id = OBJ.object_id
            AND TL.resource_type = 'OBJECT'
     LEFT JOIN sys.partitions AS PAR
         ON TL.resource_associated_entity_id = PAR.hobt_id
            AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')
     LEFT JOIN sys.objects AS PAROBJ
         ON PAR.object_id = PAROBJ.object_id
     LEFT JOIN sys.indexes AS PARIDX
         ON PAR.object_id = PARIDX.object_id
            AND PAR.index_id = PARIDX.index_id
WHERE TL.resource_database_id  = DB_ID()
      AND ES.session_id <> @@Spid -- Exclude "my" session
      -- optional filter 
      AND TL.request_mode <> 'S' -- Exclude simple shared locks
ORDER BY TL.resource_type
        ,TL.request_mode
        ,TL.request_type
        ,TL.request_status
        ,ObjectName
        ,ES.login_name;

Answer 2

Hi @Anonymous
You can use the following stored procedure to query execution errors：

CREATE PROCEDURE Get_Objects_With_Errors   
AS  
    SET NOCOUNT ON;  
    DECLARE @objectName AS VARCHAR(255);  
    DECLARE @count int = 0;  
    -- Find all procedures and functions in dbo schema  
    DECLARE listRoutines CURSOR FOR  
        SELECT SPECIFIC_NAME  
        FROM INFORMATION_SCHEMA.ROUTINES  
        WHERE SPECIFIC_SCHEMA = 'dbo'   
        GROUP BY SPECIFIC_NAME;  
    OPEN listRoutines  
    FETCH NEXT FROM listRoutines into @objectName;  
    WHILE (@@FETCH_STATUS <> -1)  
    BEGIN  
        BEGIN TRY  
            EXEC sp_refreshsqlmodule @objectName;  
        END TRY  
        BEGIN CATCH  
            PRINT @objectName + ' has ERRORS : ' + ERROR_MESSAGE();  
            SET @count = @count + 1;  
        END CATCH  
        FETCH NEXT FROM listRoutines INTO @objectName;  
    END  
    CLOSE listRoutines;  
    DEALLOCATE listRoutines;  
    PRINT 'Total with errors : ' + CAST(@count AS nvarchar(10));  
SET NOCOUNT OFF;CREATE PROCEDURE Get_Objects_With_Errors   
AS  
    SET NOCOUNT ON;  
    DECLARE @objectName AS VARCHAR(255);  
    DECLARE @count int = 0;  
    -- Find all procedures and functions in dbo schema  
    DECLARE listRoutines CURSOR FOR  
        SELECT SPECIFIC_NAME  
        FROM INFORMATION_SCHEMA.ROUTINES  
        WHERE SPECIFIC_SCHEMA = 'dbo'   
        GROUP BY SPECIFIC_NAME;  
    OPEN listRoutines  
    FETCH NEXT FROM listRoutines into @objectName;  
    WHILE (@@FETCH_STATUS <> -1)  
    BEGIN  
        BEGIN TRY  
            EXEC sp_refreshsqlmodule @objectName;  
        END TRY  
        BEGIN CATCH  
            PRINT @objectName + ' has ERRORS : ' + ERROR_MESSAGE();  
            SET @count = @count + 1;  
        END CATCH  
        FETCH NEXT FROM listRoutines INTO @objectName;  
    END  
    CLOSE listRoutines;  
    DEALLOCATE listRoutines;  
    PRINT 'Total with errors : ' + CAST(@count AS nvarchar(10));  
SET NOCOUNT OFF;  

Here are two documents for your reference:
The first article is to execute TSQL statements through transactions. You need to debug to see which line of code is the problem.
The second article uses Try catch to capture the error detailed log.
1)https://www.mssqltips.com/sqlservertip/6550/capture-executions-of-stored-procedures-in-sql-server/
2)https://www.codeproject.com/articles/363681/easy-way-to-track-stored-procedure-errors-in-ms-sq