Monitoring SQL Server Blocked Sessions with SQLCMD

By:   |   Comments (1)   |   Related: > Locking and Blocking


The release of SQL Server 2017 introduced support for Linux and one of the big differences in this release is that you need to administer almost everything from the console. This is a habit changer for most of us that have spent most of our careers using the Windows platforms with the graphical user interfaces.

One of the most basic tasks for a SQL Server Database Administrator is to check for blocked sessions and we must be prepared to deal with this task without using a graphical interface. In this tip I will show to you how you can use sqlcmd to achieve this task regardless of what platform or version of SQL Server you are running.


Working as a SQL Server Database Administrator sometimes resembles a rollercoaster. Everything is going fine and suddenly a problem emerges out of nowhere; thatís what makes our work so challenging and fun. When there is a blocked session on SQL Server your phone starts to ring and on the other side of the phone there is a user freaking out. The only information you can get from the user is ďitís not workingĒ, or ďthe application is slowĒ. Of course you wonít be able to solve the problem with only that information; you have to do your own research to find out whatís happening.

When the problem is on a server on which you can use SQL Server Management Studio, your problem is almost (and I emphasize almost) solved. Thatís because you can browse the SQL Server Locking and Blocking Tips Category and you will have the information as well as lots of sample code to deal with blocked sessions. With foresight and being prepared for these situations, you may already have a folder in the Template Browser holding all the scripts needed for troubleshooting blocking issues.

SSMS Template Browser - Description: You can create your own templates.

On the other hand, if you are unable to use SQL Server Management Studio, you will realize that you are in a little trouble. Letís take for instance the script from the tip Identify the cause of SQL Server blocking:

WITH [Blocking]
          AS ( SELECT   w.[session_id] ,
                         s.[original_login_name] ,
                        s.[login_name] ,
                        w.[wait_duration_ms] ,
                        w.[wait_type] ,
                        r.[status] ,
                        r.[wait_resource] ,
                        w.[resource_description] ,
                        s.[program_name] ,
                        w.[blocking_session_id] ,
                        s.[host_name] ,
                        r.[command] ,
                        r.[percent_complete] ,
                        r.[cpu_time] ,
                        r.[total_elapsed_time] ,
                        r.[reads] ,
                        r.[writes] ,
                        r.[logical_reads] ,
                        r.[row_count] ,
                        q.[text] ,
                        q.[dbid] ,
                        p.[query_plan] ,
               FROM     [sys].[dm_os_waiting_tasks] w
                        INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
                        INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
                        CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
                        CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
               WHERE    w.[session_id] > 50
                        AND w.[wait_type] NOT IN ( 'DBMIRROR_DBM_EVENT',
                                                   'ASYNC_NETWORK_IO' )
    SELECT  b.[session_id] AS [WaitingSessionID] ,
            b.[blocking_session_id] AS [BlockingSessionID] ,
            b.[login_name] AS [WaitingUserSessionLogin] ,
            b.[original_login_name] AS [WaitingUserConnectionLogin] ,
            b.[wait_duration_ms] AS [WaitDuration] ,
            b.[wait_type] AS [WaitType] ,
            b.[host_name] AS [WaitingHost],
            UPPER(b.[status]) AS [WaitingProcessStatus] ,
            b.[wait_resource] AS [WaitResource] ,
            b.[resource_description] AS [WaitResourceDescription] ,
            b.[program_name] AS [WaitingSessionProgramName] ,
            s1.[login_name] AS [BlockingUserSessionLogin] ,
            s1.[original_login_name] AS [BlockingSessionConnectionLogin] ,
            s1.[program_name] AS [BlockingSessionProgramName] ,
            s1.[host_name] AS [BlockingHost],
            UPPER(s1.[status]) AS [BlockingSessionStatus] ,
            t.[request_mode] AS [WaitRequestMode] ,
            t.[resource_type] AS [WaitResourceType] ,
            t.[resource_database_id] AS [WaitResourceDatabaseID] ,
            DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName] 
    FROM    [Blocking] b
            INNER JOIN [sys].[dm_exec_sessions] s1 ON b.[blocking_session_id] = s1.[session_id]
            INNER JOIN [sys].[dm_tran_locks] t ON t.[request_session_id] = b.[session_id]
    WHERE   t.[request_status] = 'WAIT'

This is the kind of query we must keep close, so we have it when there are performance issues. This query returns a lot of information when you are troubleshooting a blocking issue. Even though this query is useful and very complete, there is an issue if we can only connect to the problematic SQL Server instance by using sqlcmd. Just take a look at the screen captures on the tip Identify the cause of SQL Server blocking. In order to take screen captures, the author had to split the result set into seven images just to make it fit the screen.

For the purposes of illustration the issue of running that script in sqlcmd I took the following screen capture.

Sqlcmd Execution - Description: This is how looks that query when is executed in sqlcmd.

As you can see on the previous image, what was a perfect query to troubleshoot blocking has become useless when we execute it inside sqlcmd. The fact is that when we are using the console we canít display too much information, we need to use queries that return as few columns as possible in order to aid with visualization.

Using sqlcmd to Find Blocked SQL Server Sessions

Now that we are aware of the problem letís try to find a solution.

First we must create a short query that returns the basic information, like the following:

SELECT session_id,        
        DB_NAME(database_id) [Database],
FROM    sys.dm_exec_requests
WHERE   blocking_session_id <> 0

This query returns far less columns than the previous query, but as you can see on the next image we still have the same problem with reading the results and understanding the query output.

Shorter Query - Description: Even this small query has visualization issues when executed in sqlcmd.

In order to solve it we can use the ĖW switch when invoking sqlcmd. This option removes trailing spaces from columns.

The -W switch - Description: By using the -W switch we get rid of the problem.

As you can see on the previous image, we now have legible query output. But if you look closely you can see that instead of writing the query in the sqlcmd console I just loaded the query from a text file with the :r sqlcmd command. Having the query in a file saves time. Furthermore, you can have more queries like this in script files and copy them to each server where you canít connect by using SQL Server Management Studio.

Now that we know we have a blocked session, we need to gather more information about both the blocked session and the blocking session, but we must do it one step at a time.

First letís check out what the blocking session is executing with the following query.

SELECT  A.Session_id,
        DB_NAME(B.database_id) [Database],
FROM    sys.dm_exec_requests A
        LEFT JOIN sys.dm_exec_requests B ON A.blocking_session_id = B.session_id
        OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) C
WHERE   A.blocking_session_id <> 0

The query above returns the T-SQL code that is running the blocking session, its wait type, the database on which its running and the id of the session that is being blocked.

On the following image you can see the output of this query.

Blocking Sessions - Description: With this little script we can get information about the blocking session.

Also, we may like to see the query that is being blocked and we can do so with the following script.

SELECT  A.Session_id,
        DB_NAME(A.database_id) [Database],
FROM    sys.dm_exec_requests A        
        CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
WHERE   A.blocking_session_id <> 0

As you can see on the image above, with the previous query we have a result set that fits the screen and gives us valuable information.

In other words, we have found a way to troubleshoot blocking with sqlcmd by using script files that return just the data we need. This method of having script files adapted to run on sqlcmd in a folder can be used for other types of issues like checking free database space or log file fragmentation just to enumerate a few. In fact you can emulate the functionality of SQL Server Management Studioís Template Browser with the difference that if you want to see the script names you need to execute the !!dir sqlcmd command to list the contents of a folder.

On the following screen capture you will see how it looks when all three scripts are run together.

Running all the scripts together - Description: All those scripts can fit in a single screen capture.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, September 8, 2022 - 2:42:56 AM - Paul Back To Top (90451)
HI, could anyone give me advice.

on a daily basis i lose connection to the SQL instance and can not connect until the server is restarted.
i do have continuous queries running all the time.

get free sql tips
agree to terms