sp_BlitzLock


Job details

Name: sp_BlitzLock
Platform: Sqlserver
Category: Performance extension (Brent Ozar)
Description: Runs the sp_BlitzLock procedure (Brent Ozar) which checks for and analyzes deadlocks from the system health session or a custom extended event path
Long description: Runs the sp_BlitzLock procedure (Brent Ozar) which checks for and analyzes deadlocks from the system health session or a custom extended event path
Version: 1.4
Default schedule: 5 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2008′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]/blitz_obj_id/obj_name[id=‘sp_BlitzLock’]/instance

Parameters

Name Default value Description
location of dbo.sp_BlitzLock procedure master Name of the database where the dbo.sp_BlitzLock procedure is located.
@Top 20000 Use if you want to limit the number of deadlocks to return. This is ordered by event date ascending. This is input parameter to the sp_BlitzLock procedure.
return status when deadlocks found 1 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when new deadlocks found.
@DatabaseName If you want to filter to a specific database. This is input parameter to the sp_BlitzLock procedure.
@StartDate 19000101 The date you want to start searching on. This is input parameter to the sp_BlitzLock procedure. During installation this value is set to current date – 7 days. After execution the value is set to current date.
@EndDate 99991231 The date you want to stop searching on. This is input parameter to the sp_BlitzLock procedure.
@ObjectName If you want to filter to a specific object. The object name has to be fully qualified Database.Schema.Table. This is input parameter to the sp_BlitzLock procedure.
@StoredProcName If you want to search for a single stored procedure. The procedure name has to be fully qualified Database.Schema.Sproc. This is input parameter to the sp_BlitzLock procedure.
@AppName If you want to filter to a specific application. This is input parameter to the sp_BlitzLock procedure.
@HostName If you want to filter to a specific host. This is input parameter to the sp_BlitzLock procedure.
@LoginName If you want to filter to a specific login. This is input parameter to the sp_BlitzLock procedure.
@OutputDatabaseName If you want to output information to a specific database. This is input parameter to the sp_BlitzLock procedure.
@OutputSchemaName dbo Specify a schema name to output information to a specific Schema. This is input parameter to the sp_BlitzLock procedure.
@OutputTableName BlitzLock Specify table name to output information to a specific table. This is input parameter to the sp_BlitzLock procedure.
delete old output YES If set to “YES“ (default) old statistics from previous executions of sp_BlitzLockp procedure will be deleted (including statistics from table BlitzLockFindings).
max history records 500 Maximum number of records in the history table tab_histr_sp_blitzLock

Job Summary

Details of the Monitoring Job

Implementation Code Breakdown

Upgrade and Maintenance

This job is vital for system administrators and database professionals wanting to keep a proactive watch on potential deadlock issues, offering insights and automated management directly within the dbWatch environment. The outputs are structured to provide clear investigative paths, thus reducing the resolution time of deadlock-related disruptions.