SQL Server 2012 - How to view the locks' log? [closed]

Question

I'm using SQL Server 2012 Express Edition, may I know how can I view all the lock/block processes which were recorded in my database? The reason I want to view the previous locked/blocked processes is SQL Server only allows us to view the current locked process in the database.

I found that I have few timeout errors in my SQLException log file, so I would like to know is there a way to view or query the past records on the locks/blocks that caused the time out.

And I did not turn on my blocked process report for my database T.T

Answer 1

If you haven't already turned on the blocked process report, then it's not possible to jump back in time to view past blocked processes.

However, I find that where there's blocking, these two already-on things help:

First, SQL Server tracks which indexes are involved in blocking. It doesn't tell you which queries are involved, nor is it permanently reliable (the data disappears at odd times), but it's a start. You can query sys.dm_db_index_operational_stats to learn more, or use the open source sp_BlitzIndex to see it. sp_BlitzIndex identifies the indexes involved in blocking as "Aggressive Indexes" - that can be a little confusing, so here's a post on how to interpret those warnings.

Second, if there's blocking, there's sometimes also deadlocking. And good news! SQL Server tracks deadlocks by default in the system health session, and you can indeed query those with the open source sp_BlitzLock. It doesn't hurt to give that a shot - it may identify which queries are causing you problems.

(Disclaimer: I wrote some code in those open source procs, and I wrote the blog post referenced in here.)

Answer 2

Without setting up something upfront to record the blocking events, there is no way to see the history of blocking.

Please note that locking and blocking are two different things. Locking is a normal activity in a database and is not harmful or undesirable. Long lasting blocking is an issue instead.

That said, it's really easy to set up something to listen for blocking events. If you were using a different edition of SQL Server, I would recommend turning on Data Collection, but since you're on Express Edition you don't have the SQL Server Agent to schedule the collection process and things can get a bit tricky.

In this case, your best option is to create an Extended Events session to capture the Blocked Process events and write them to a file target.

USE master
GO

-- Enable editing advanced configuration options
EXEC sp_configure 'advanced', 1
RECONFIGURE 
GO

-- Set the blocked process threshold 
-- (15 seconds, or whatever makes sense in your case)
EXEC sp_configure 'blocked process threshold (s)', 15
RECONFIGURE 
GO


-- Create an extended events session to capture blocking events
CREATE EVENT SESSION [blocked_processes] ON SERVER 
ADD EVENT sqlserver.blocked_process_report (
    -- twice the threshold: allows capturing events only once
    WHERE [duration]<=30000000
)
ADD TARGET package0.event_file(SET filename=N'blocked_processes')
WITH (
    MAX_MEMORY = 2048 KB
    ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY = 30 SECONDS
    ,MAX_EVENT_SIZE = 0 KB
    ,MEMORY_PARTITION_MODE = NONE
    ,TRACK_CAUSALITY = OFF
    ,STARTUP_STATE = ON
);

-- Start the session
ALTER EVENT SESSION [blocked_processes] ON SERVER STATE = START;

Once captured the events, you can visualize them using SSMS or by using the API with Powershell or .NET.

Another thing you could do is set up some sort of alert when blocking events are captured. To do this, you can XESmartTarget, which is a small Open Source commandline application that listens for events and performs actions in response. It can be configured very easily with a .json configuration file like this:

{
    "Target": {
        "ServerName": "(local)\\SQLEXPRESS",
        "SessionName": "blocked_processes",
        "Responses": [
            {
                "__type": "TableAppenderResponse",
                "ServerName": "(local)\\SQLEXPRESS",
                "DatabaseName": "master",
                "TableName": "blocked_processes",
                "AutoCreateTargetTable": true,
                "UploadIntervalSeconds": 30,
                "Events": [
                    "blocked_process_report"
                ],
                "OutputColumns": [
                    "collection_time",
                    "blocked_process",
                    "duration"
                ]
            },
            {
                "__type": "EmailResponse",
                "SMTPServer": "localhost",
                "Sender": "[email protected]",
                "To": "[email protected]",
                "Subject": "Blocking occurred",
                "Body": "Some blocking is going on",
                "Attachment": "blocked_process",
                "AttachmentFileName": "BlockedProcessReport.xml",
                "Events": [
                    "blocked_process_report"
                ]
            }
        ]
    }
}

Save the file where you prefer (example: c:\temp\blocked_processes.json) and run XESmartTarget specifying the .json file as a parameter:

"c:\Program Files (x86)\XESmartTarget\XESmartTarget.exe" -F c:\temp\blocked_processes.json

You should start seeing an output like this:

XESmartTarget 1.0.8
Copyright ©  2018 - spaghettidba

Info - XESmartTarget.Program : Reading configuration from 'c:\temp\blocked_processes.json'
Info - XESmartTarget.Core.Responses.TableAppenderResponse : Initializing Response of Type 'XESmartTarget.Core.Responses.TableAppenderResponse'
Info - XESmartTarget.Core.Responses.EmailResponse : Initializing Response of Type 'XESmartTarget.Core.Responses.EmailResponse'
Info - XESmartTarget.Program : Starting Target
Info - XESmartTarget.Core.Target : Connecting to XE session 'blocked_processes' on server '(local)\SQLEXPRESS'
Info - XESmartTarget.Core.Target : Connected.
Info - XESmartTarget.Core.Responses.TableAppenderResponse : Creating target table (local)\SQLEXPRESS.master.blocked_processes
Info - XESmartTarget.Core.Responses.TableAppenderResponse : 1 rows written
Info - XESmartTarget.Core.Responses.TableAppenderResponse : 0 rows written
Info - XESmartTarget.Core.Responses.TableAppenderResponse : 0 rows written
Info - XESmartTarget.Core.Responses.TableAppenderResponse : 0 rows written
Info - XESmartTarget.Core.Responses.TableAppenderResponse : 0 rows written

The .json file instructs XESmartTarget to write the events to a table in the master database and send an email alert everytime blocking events occur. The tool can be scheduled with the Windows Scheduler to start with Windows.