Thursday, December 30, 2021

Query Progress

Putting together various scripts developed by people in the community to check the progress of currently running queries. Some of them I have tweaked to make them more readable.

Script 1:  Time remaining for DB Backup & Restore

 
--https://www.sqlservercentral.com/blogs/how-to-get-estimated-completion-time-of-sql-server-database-backup-or-restore
SELECT dmr.session_id
	,dmr.command
	,CONVERT(NUMERIC(6, 2), dmr.percent_complete) AS [Percent Complete]
	,CONVERT(VARCHAR(20), DATEADD(ms, dmr.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
	,CONVERT(NUMERIC(10, 2), dmr.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
	,CONVERT(NUMERIC(10, 2), dmr.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
	,CONVERT(NUMERIC(10, 2), dmr.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
	,CONVERT(VARCHAR(1000), (
			SELECT SUBSTRING(TEXT, dmr.statement_start_offset / 2, CASE 
						WHEN dmr.statement_end_offset = - 1
							THEN 1000
						ELSE (dmr.statement_end_offset - dmr.statement_start_offset) / 2
						END)
			FROM sys.dm_exec_sql_text(sql_handle)
			)) [sqltxt]
FROM sys.dm_exec_requests dmr
WHERE command IN (
		'RESTORE DATABASE'
		,'BACKUP DATABASE'
		,'BACKUP LOG'
		,'RESTORE LOG'
		)

Script 2:  Time remaining for below commands :

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
ROLLBACK
TDE ENCRYPTION

SELECT session_id
	,percent_complete
	,start_time
	,STATUS
	,DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP) Estimated_finish_time
	,(total_elapsed_time / 1000) / 60 Total_Elapsed_Time_MINS
	,DB_NAME(Database_id) Database_Name
	,command
	,last_wait_type
FROM sys.dm_exec_requests
WHERE session_id > 50
	AND session_id <> @@spid
	--and DB_NAME(Database_id)='AWSDB'
	--and session_id=652

Script 3:  Progress of CREATE INDEX

--https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
DECLARE @SPID INT = 51;;

WITH agg
AS (
	SELECT SUM(qp.[row_count]) AS [RowsProcessed]
		,SUM(qp.[estimate_row_count]) AS [TotalRows]
		,MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS]
		,MAX(IIF(qp.[close_time] = 0
				AND qp.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [CurrentStep]
	FROM sys.dm_exec_query_profiles qp
	WHERE qp.[physical_operator_name] IN (
			N'Table Scan'
			,N'Clustered Index Scan'
			,N'Index Scan'
			,N'Sort'
			)
		AND qp.[session_id] = @SPID
	)
	,comp
AS (
	SELECT *
		,([TotalRows] - [RowsProcessed]) AS [RowsLeft]
		,([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
	FROM agg
	)
SELECT [CurrentStep]
	,[TotalRows]
	,[RowsProcessed]
	,[RowsLeft]
	,CONVERT(DECIMAL(5, 2), (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete]
	,[ElapsedSeconds]
	,(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft]
	,DATEADD(SECOND, (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]), GETDATE()) AS [EstimatedCompletionTime]
FROM comp;


Script 4:  Progress of SELECT INTO

 
--https://dba.stackexchange.com/questions/129090/progress-of-select-into-statement/129152#129152
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT --OBJECT_NAME(sp.[object_id]) AS [TableName], sdobd.*, '---', sp.*, '---', sau.*
	SUM(sdobd.[row_count]) AS [BufferPoolRows]
	,SUM(sp.[rows]) AS [AllocatedRows]
	,COUNT(*) AS [DataPages]
FROM sys.dm_os_buffer_descriptors sdobd
INNER JOIN sys.allocation_units sau ON sau.[allocation_unit_id] = sdobd.[allocation_unit_id]
INNER JOIN sys.partitions sp ON (
		sau.[type] = 1
		AND sau.[container_id] = sp.[partition_id]
		) -- IN_ROW_DATA
	OR (
		sau.[type] = 2
		AND sau.[container_id] = sp.[hobt_id]
		) -- LOB_DATA
	OR (
		sau.[type] = 3
		AND sau.[container_id] = sp.[partition_id]
		) -- ROW_OVERFLOW_DATA
WHERE sdobd.[database_id] = DB_ID()
	AND sdobd.[page_type] = N'DATA_PAGE'
	AND sp.[object_id] = (
		SELECT so.[object_id]
		FROM sys.objects so
		WHERE so.[name] = 'TestDump'

Done!! I hope this helps 😊

at No comments:

Monday, December 27, 2021

Query Cached Plan Statistics

We can use the below script to find the Query Cached Plan statistics for all DB's

SELECT UseCount = p.usecounts
	,PlanSize_KB = p.size_in_bytes / 1024
	,CPU_ms = qs.total_worker_time / 1000
	,Duration_ms = qs.total_elapsed_time / 1000
	,ObjectType = p.cacheobjtype + ' (' + p.objtype + ')'
	,DatabaseName = db_name(convert(INT, pa.value))
	,txt.ObjectID
	,qs.total_physical_reads
	,qs.total_logical_writes
	,qs.total_logical_reads
	,qs.last_execution_time
	,StatementText = SUBSTRING(txt.[text], qs.statement_start_offset / 2 + 1, CASE 
			WHEN qs.statement_end_offset = - 1
				THEN LEN(CONVERT(NVARCHAR(max), txt.[text]))
			ELSE qs.statement_end_offset / 2 - qs.statement_start_offset / 2 + 1
			END)
	,QueryPlan = qp.query_plan
FROM sys.dm_exec_query_stats AS qs
INNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = qs.plan_handle
OUTER APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) AS txt
OUTER APPLY sys.dm_exec_query_plan(p.plan_handle) AS qp
WHERE pa.attribute = 'dbid' --retrieve only the database id from sys.dm_exec_plan_attributes
ORDER BY qs.total_worker_time + qs.total_elapsed_time DESC;


Done!! I hope this helps 😊

at No comments:

Friday, December 17, 2021

Get SQL statement using the sql_handle , start offset and end offset

We can use the below script to find the exact SQL statement using the sql_handle , start offset and end offset. 

 
DECLARE @sql_handle VARBINARY(64) = 0x0300050097E18713F1A9570113A8000001000000000000000000000000000000000000000000000000000000 --Pass sql handle here
DECLARE @offsetStart INT = 150 --Pass start offset here
DECLARE @offsetEnd INT = 326 --Pass End offset here

SELECT SUBSTRING(TEXT, (@offsetStart / 2) + 1, (
			(
				CASE @offsetEnd
					WHEN - 1
						THEN DATALENGTH(TEXT)
					ELSE @offsetEnd
					END - @offsetStart
				) / 2
			) + 1) AS statement_text
FROM sys.dm_exec_sql_text(@sql_handle)
/*
-- Get SQL TEXT and QUERY PLAN
select * from sys.[dm_exec_sql_text](0x02000000ACAC3005372F5458DAAFAAB4D6A72F99D43FBC080000000000000000000000000000000000000000) -- sql_handle
select * from sys.[dm_exec_query_plan](0x06000700ACAC3005C0DDD2E50100000001000000000000000000000000000000000000000000000000000000) -- plan_handle
go

*/


Done!! I hope this helps 😊

at No comments:

Monday, October 25, 2021

Persist CPU Utilization from RingBuffer

CPU utilization of SQL Server can be obtained from. sys.dm_os_ring_buffers.Ring buffer contains the CPU utilization by all other processes. It is captured in one-minute increments for the past 256 minutes. 

In order to check the CPU utilization of SQL Server prior to that, we have to persist the ring buffer details into a permanent table. Steps below:-

Step 1:  Create a table in the DBA utility database

 
CREATE TABLE [dbo].[CpuUtilization] (
	[SqlCpuUtilization] [int] NOT NULL
	,[SystemIdleProcess] [int] NOT NULL
	,[OtherProcessCpuUtilization] [int] NOT NULL
	,[EventTime] [datetime] NOT NULL
	,CONSTRAINT [PK_dbo_CpuUtilization_EventTime] PRIMARY KEY CLUSTERED ([EventTime] ASC)
	)

Step 2:  Create a job to run the below script to capture ring buffer data & write it into the table created in Step 1

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @T TABLE (
	[SqlCpuUtilization] [int] NOT NULL
	,[SystemIdleProcess] [int] NOT NULL
	,[OtherProcessCpuUtilization] [int] NOT NULL
	,[EventTime] [datetime] NOT NULL
	);
DECLARE @ts_now BIGINT = (
		SELECT cpu_ticks / (cpu_ticks / ms_ticks)
		FROM sys.dm_os_sys_info WITH (NOLOCK)
		);

INSERT INTO @T
-- This version works with SQL Server 2014
SELECT TOP (256) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
	,SystemIdle AS [System Idle Process]
	,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
	,DATEADD(ms, - 1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
	SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
		,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
		,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
		,[timestamp]
	FROM (
		SELECT [timestamp]
			,CONVERT(XML, record) AS [record]
		FROM sys.dm_os_ring_buffers WITH (NOLOCK)
		WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			AND record LIKE N'%<SystemHealth>%'
		) AS x
	) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);

INSERT INTO [dbo].[CpuUtilization]
SELECT T.*
FROM @T AS T
LEFT OUTER JOIN dbo.CpuUtilization AS C ON T.EventTime = C.EventTime
WHERE C.EventTime IS NULL
OPTION (RECOMPILE);
	--select * from [dbo].[CpuUtilization]

Step 3:  Clean up old data (if required)

SET NOCOUNT ON;

DECLARE @RetentionDay DATETIME;

SET @RetentionDay = (
		SELECT CAST(DATEADD(DAY, - 30, GETDATE()) AS DATE)
		);

DELETE
FROM dbo.CpuUtilization
WHERE EventTime < @RetentionDay;


Done!! I hope this helps 😊

at No comments:

Wednesday, September 15, 2021

Active Sessions on SQL Server

We can use the below script to quickly identify the active sessions on the server and their details


SELECT s.session_id
	,r.STATUS
	,r.blocking_session_id 'blocked by'
	,r.wait_type
	,wait_resource
	,r.wait_time / (1000.0) 'Wait Time (in Sec)'
	,r.cpu_time
	,r.logical_reads
	,r.reads
	,r.writes
	,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
	,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
			(
				CASE r.statement_end_offset
					WHEN - 1
						THEN Datalength(st.TEXT)
					ELSE r.statement_end_offset
					END - r.statement_start_offset
				) / 2
			) + 1) AS statement_text
	,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
	,r.command
	,s.login_name
	,s.host_name
	,s.program_name
	,s.host_process_id
	,s.last_request_end_time
	,s.login_time
	,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
	,r.STATUS
	,r.blocking_session_id
	,s.session_id

Hope it helps !!


at No comments:

SQL Server Agent Alerts : Part 2

We can use the below script to create SQL Server Agent alerts to notify the operator in case of any Data Consistency Errors in Replication

Below are the common data consistency errors that can occur:

  • 2601 Cannot insert a duplicate key row in object
  • 20598 The row was not found at the Subscriber when applying the replicated command.
  • 2627 Violation of PRIMARY KEY constraint 'PK_'


USE [msdb];
GO

SET NOCOUNT ON;

-- Change @OperatorName as needed
DECLARE @OperatorName SYSNAME = N'DBAOperator';
-- Change @CategoryName as needed
DECLARE @CategoryName SYSNAME = N'SQL Server Agent Alerts';

-- Make sure you have an Agent Operator defined that matches the name you supplied
IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.sysoperators
		WHERE name = @OperatorName
		)
BEGIN
	RAISERROR (
			'There is no SQL Operator with a name of %s'
			,18
			,16
			,@OperatorName
			);

	RETURN;
END

-- Add Alert Category if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.syscategories
		WHERE category_class = 2 -- ALERT
			AND category_type = 3
			AND name = @CategoryName
		)
BEGIN
	EXEC dbo.sp_add_category @class = N'ALERT'
		,@type = N'NONE'
		,@name = @CategoryName;
END

-- Get the server name
DECLARE @ServerName SYSNAME = (
		SELECT @@SERVERNAME
		);
--AG
DECLARE @Error2601AlertName SYSNAME = @ServerName + N' Alert - Replication: Cannot insert duplicate key row in object';
DECLARE @Error20598AlertName SYSNAME = @ServerName + N' Alert - Replication: The row was not found at the Subscriber';
DECLARE @Error2627AlertName SYSNAME = @ServerName + N' Alert - Replication: Violation of PRIMARY KEY constraint';

IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error2601AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error2601AlertName
		,@message_id = 2601
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 600
		,@include_event_description_in = 5
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error2601AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error2601AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error20598AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error20598AlertName
		,@message_id = 20598
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 600
		,@include_event_description_in = 5
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error20598AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error20598AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error2627AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error2627AlertName
		,@message_id = 2627
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 0
		,@include_event_description_in = 5
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error2627AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error2627AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END
GO

Hope it helps !!


at No comments:

SQL Server Agent Alerts- Part 1

We can use the below script to create SQL Server Agent alerts to notify the operator defined.  The script is adapted from the post written by GLENN BERRY. The script creates alerts for high severity errors (from 19 to 25), alert for Error 832, and for Errors 855, 856 & few Alwayson Alerts (if enabled)


USE [msdb];
GO

-- Change @OperatorName as needed
DECLARE @OperatorName SYSNAME = N'DBAOperator';
-- Change @CategoryName as needed
DECLARE @CategoryName SYSNAME = N'SQL Server Agent Alerts';

IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.sysoperators
		WHERE name = @OperatorName
		)
BEGIN
	RAISERROR (
			'There is no SQL Operator with a name of %s'
			,18
			,16
			,@OperatorName
			);

	RETURN;
END

IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.syscategories
		WHERE category_class = 2 -- ALERT
			AND category_type = 3
			AND name = @CategoryName
		)
BEGIN
	EXEC dbo.sp_add_category @class = N'ALERT'
		,@type = N'NONE'
		,@name = @CategoryName;
END

-- Get the server name
DECLARE @ServerName SYSNAME = (
		SELECT @@SERVERNAME
		);
-- Alert Names start with the name of the server 
DECLARE @Sev19AlertName SYSNAME = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName SYSNAME = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName SYSNAME = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName SYSNAME = @ServerName + N' Alert - Sev 22 Error: Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName SYSNAME = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName SYSNAME = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName SYSNAME = @ServerName + N' Alert - Sev 25 Error: Fatal Error';
DECLARE @Error823AlertName SYSNAME = @ServerName + N' Alert - Error 823: The operating system returned an error';
DECLARE @Error824AlertName SYSNAME = @ServerName + N' Alert - Error 824: Logical consistency-based I/O error';
DECLARE @Error825AlertName SYSNAME = @ServerName + N' Alert - Error 825: Read-Retry Required';
DECLARE @Error832AlertName SYSNAME = @ServerName + N' Alert - Error 832: Constant page has changed';
DECLARE @Error855AlertName SYSNAME = @ServerName + N' Alert - Error 855: Uncorrectable hardware memory corruption detected';
DECLARE @Error856AlertName SYSNAME = @ServerName + N' Alert - Error 856: SQL Server has detected hardware memory corruption, but has recovered the page';

-- Sev 19 Error: Fatal Error in Resource
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev19AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName
		,@message_id = 0
		,@severity = 19
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev19AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 20 Error: Fatal Error in Current Process
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev20AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName
		,@message_id = 0
		,@severity = 20
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev20AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 21 Error: Fatal Error in Database Process
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev21AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName
		,@message_id = 0
		,@severity = 21
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev21AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 22 Error: Fatal Error Table Integrity Suspect
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev22AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName
		,@message_id = 0
		,@severity = 22
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev22AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 23 Error: Fatal Error Database Integrity Suspect
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev23AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName
		,@message_id = 0
		,@severity = 23
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev23AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 24 Error: Fatal Hardware Error
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev24AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName
		,@message_id = 0
		,@severity = 24
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev24AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 25 Error: Fatal Error
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev25AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName
		,@message_id = 0
		,@severity = 25
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev25AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 823 Alert added on 8/11/2014
-- Error 823: Operating System Error
-- How to troubleshoot a Msg 823 error in SQL Server	
-- http://support.microsoft.com/kb/2015755
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error823AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName
		,@message_id = 823
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error823AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 824 Alert added on 8/11/2014
-- Error 824: Logical consistency-based I/O error
-- How to troubleshoot Msg 824 in SQL Server
-- http://support.microsoft.com/kb/2015756
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error824AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName
		,@message_id = 824
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error824AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 825: Read-Retry Required
-- How to troubleshoot Msg 825 (read retry) in SQL Server
-- http://support.microsoft.com/kb/2015757
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error825AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName
		,@message_id = 825
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error825AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 832 Alert added on 10/30/2013
-- Error 832: Constant page has changed
-- http://www.sqlskills.com/blogs/paul/dont-confuse-error-823-and-error-832/
-- http://support.microsoft.com/kb/2015759
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error832AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error832AlertName
		,@message_id = 832
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error832AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error832AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Memory Error Correction alerts added on 10/30/2013
-- Mitigation of RAM Hardware Errors	 		
-- When SQL Server 2012 Enterprise Edition is installed on a Windows 2012 operating system with hardware that supports bad memory diagnostics, 
-- you will notice new error messages like 854, 855, and 856 instead of the 832 errors that LazyWriter usually generates.
-- Error 854 is just informing you that your instance supports memory error correction
-- Using SQL Server in Windows 8 and Windows Server 2012 environments
-- http://support.microsoft.com/kb/2681562
-- Check for SQL Server 2012 or greater and Enterprise Edition
-- You also need Windows Server 2012 or greater, plus hardware that supports memory error correction
IF LEFT(CONVERT(CHAR(2), SERVERPROPERTY('ProductVersion')), 2) >= '11'
	AND SERVERPROPERTY('EngineEdition') = 3
BEGIN
	-- Error 855: Uncorrectable hardware memory corruption detected
	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @Error855AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @Error855AlertName
			,@message_id = 855
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 900
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000';

	-- Add a notification if it does not exist
	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @Error855AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error855AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	-- Error 856: SQL Server has detected hardware memory corruption, but has recovered the page
	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @Error856AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @Error856AlertName
			,@message_id = 856
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 900
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000';

	-- Add a notification if it does not exist
	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @Error856AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error856AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END
END

DECLARE @IsHadrEnabled AS SQL_VARIANT

SET @IsHadrEnabled = (
		SELECT SERVERPROPERTY('IsHadrEnabled')
		)

IF @IsHadrEnabled = 1
BEGIN
	--AG
	DECLARE @ErrorAG1480AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 1480: AG Role Changed';
	DECLARE @ErrorAG35264AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 35264: AG data Movement Suspended';
	DECLARE @ErrorAG35265AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 35265: AG data Movement Resumed';
	DECLARE @ErrorAG41404AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 41404: AG is offline';
	DECLARE @ErrorAG41405AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 41405: AG not ready for Automatic failover';

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG35264AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG35264AlertName
			,@message_id = 35264
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG35264AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG35264AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG35265AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG35265AlertName
			,@message_id = 35265
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG35265AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG35265AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG41404AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG41404AlertName
			,@message_id = 41404
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG41404AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG41404AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG41405AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG41405AlertName
			,@message_id = 41405
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG41405AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG41405AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG1480AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG1480AlertName
			,@message_id = 1480
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	-- Add a notification if it does not exist
	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG1480AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG1480AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END
END
GO

Hope it helps !!


at No comments:
Subscribe to: Comments (Atom)

Extract DB Permission

Script to extract DB permissions   SET NOCOUNT ON GO SELECT 'Use ' + db_name ( ) PRINT 'go' GO SELECT 'EX...