SQL SERVER DBA, Linux and Azure




user1 running a command on table1 which is still executing at the same time other user try to read/update the data from/to same table then user1 query will block the user2 Query.





Step1: Find the Blocking

There are number of ways to find out the details of the system processes IDs (spids) involved in blocking.

1.     sp_who2 System Stored Procedure

2.     sys.dm_exec_requests DMV

3.     sys.sysprocesses

4.     Sys.dm_os_waiting_tasks

5.     SQL Server Management Studio Activity Monitor

6.     SQL Server Management Studio Reports

7.     SQL Server Profiler


1.sp_who2 System Stored Procedure

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated SPID, status,login,Hostname,BlkBy,DBName,Command,CPU time, etc. The information returned can be filtered to return only the active processes by using the ‘active' parameter.

Below is some sample code and a screen shot with showing process 54 being blocked by process 53.

1.USE Master





2.USE Master


Sp_Who2 active




2.  sys.dm_exec_requests DMV

The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

Use master


select  * from sys.dm_exec_requests

where  blocking_session_id<>0

3. sys.sysprocesses


The sys.sysprocesses provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

Use master


Select * from sys.sysprocesses where blocked<>0


4.sys.dm_os_waiting_tasks DMV

The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources.

USE Master


SELECT session_id, wait_duration_ms, wait_type, blocking_session_id

FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id <> 0


5.SQL Server Management Studio Activity Monitor

It is easy procedure to do find the blocking from SSMS.

Open SSMS->Connect instance->Right click on instance-> select 'Activity Monitor'->Processes



6.SQL Server Management Studio Reports

We can monitor the blocking on standard reports.

Open SSMS->Connect instance->Right click on instance->  Reports -> Standard Reports-> Activity - All Blocking Transactions.


7.SQL Server Profiler

To capture blocking related data on a continuous basis, one option is to run SQL Server Profiler and save the data to a table or file for analysis purposes. In order to configure Profiler to capture blocking related data, execute Profiler, configure the general properties. In addition, be sure to configure the 'blocked process threshold' before you start Profiler using this code:

sp_configure 'show advanced options', 1




sp_configure 'blocked process threshold', 20




Menu Bar->Tools->SQL Profiler->Connect the Instance->Select use template as TSQL-Lock->click on column filter->Select Database->Give the database name->ok->Run


Note: Don’t do this process in business hours. It causes a performance issue. Without change we can’t run the profiler.




Find the command which is running under SPID by using DBCCDBCC INPUTBUFFER(53)


Kill the transaction by using below command




1.If select then kill the SPID ( Select command is not modify the data)

2. There is any other command part of the blocking then we can take the user/application team approval then we will kill it


Bandaru Ajeyudu

SQL and Azure DBA Trainer

Microsoft & Azure SQL DBA certified.




T-SQL query to get tables size in GB with no of rows in SQL server

Hi All,

Please find the below T-SQL script to get the tables size in GB with number of rows in SQL Server Database.

Use DB_Name;  --Change your database here



 s.name + '.' + t.Name AS [Table Name],

 part.rows AS [Total Rows In Table - Modified],

 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) 

 AS [Table's Total Space In GB]


 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id

 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id 

                    AND idx.Index_id = part.Index_id

 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id

 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id

 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id

WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 

GROUP BY t.Name, s.name, part.rows

ORDER BY [Table's Total Space In GB] DESC

Retrieve SSRS report schedule info through SQL

 --Retrieve SSRS report schedule info through SQL

Grabs information about what reports in SSRS are scheduled, when are they due to be sent out, 

report parameters and any errors in the last run etc.

Please note that the XPath on the [Parameters] column varies based on what parameters are setup in your own report. 

Please adjust them for your own purpose. Also the 'Prompt' value for a parameter is not stored, so you might have to 

join back to the relevant table for a friendly name.

Also there is additional info in the [ExtensionSetting].





'Next Run Date' = CASE next_run_date

WHEN 0 THEN null


substring(convert(varchar(15),next_run_date),1,4) + '/' +

substring(convert(varchar(15),next_run_date),5,2) + '/' +



'Next Run Time' = isnull(CASE len(next_run_time)

WHEN 3 THEN cast('00:0'

+ Left(right(next_run_time,3),1)

+':' + right(next_run_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 6 THEN cast(Left(right(next_run_time,6),2)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))


Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]

        ---Example report parameters: StartDateMacro, EndDateMacro & Currency.

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]








FROM [ReportServer$LIVE].[dbo].[Subscriptions] S

INNER JOIN ReportServer$LIVE.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID

INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name

INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id