3

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

closed as too broad by hot2use, Erik Darling, mustaccio, RDFozz, Colin 't Hart Jul 11 '18 at 21:00

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Unless you activated/create an extended event and/or create a script to track blocking, then locking is not recorded in the database. Please edit your question and add as much details as possible. Please also consider reading the following article: How do I ask a good question?. Currently your question doesn't contain enough relevant information to be answerable and will possibly be closed as too broad. – hot2use Jul 11 '18 at 8:38
  • 1
    Hi hot2use, thanks for replying, I found out that it's not possible to query the info about past locked event :) – YesImNoob Jul 11 '18 at 9:13
  • I have used this article to set up monitoring deadlocks using extended events. blogs.msdn.microsoft.com/sqlserverfaq/2013/04/26/… – Jacob H Jul 11 '18 at 13:32
4

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.)

1

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": "MyImportantServer@mycompany.com",
                "To": "dba@mycompany.com",
                "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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.