Sql Server Materials

  Get alert long open transaction on Temp DB. USE [msdb] GO /****** Object:  Job [ADM - Longer TempDB OpenTran]    Script Date: 12/15/2025 10:17:01 AM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[On Demand]]    Script Date: 12/15/2025 10:17:01 AM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[On Demand]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[On Demand]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Longer TempDB OpenTran',  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=2,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N'No description available.',  @category_name=N'...

If we want  analyze the blocking information, we can use sp_whoisactive for on going blocking. if we want  analyze the pas happed blocking information. we can relay  on third party toools (monitoring tools.). if we  don't have any monitoring tool one of the method logging the sp_whoisactive.  Below  is script for logging the sp_whoisactive.  Here  we have created two table  and creating one job. In that job there  are  4 steps, first  two step for loading data into above tables. remaining two steps for deleting the data older than 72 hours. if you want you change this.  Here place the DB ANME with your DBname  USE [DB ANME] GO /****** Object:  Table [dbo].[whoisactive_log]    Script Date: 12/3/2025 1:31:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[whoisactive_log]( [whoisactive_id] [bigint] IDENTITY(1,1) NOT NULL, [dd_hh_mm_ss_mss] [nvarchar](50) NULL, [sessio...