Naresh Koudagani SQL Server DBA Blog

Sunday, March 13, 2022

adding secondary file to an exiting database

this is a online opertion and can be added in business hours

say for example:

disk E with MDF is almost full (only 10GB left), cannot shrink mdf and also disk cannot be extended , add a secondary files on F . this should help

Tuesday, March 1, 2022

Backup report daily via sp and job everyday 9am

EXEC..Backup_Report

@MailProfile = 'ABC' ,

@MailID = 'abc@abc.com',

@Server = 'ABC'

--keep sp and run job 9am everyday

USE [master]

/****** Object: StoredProcedure [dbo].[Backup_Report] Script Date: 3/1/2022 4:18:08 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[Backup_Report] (

@MailProfile NVARCHAR(200),

@MailID NVARCHAR(2000),

@Server VARCHAR(100) = NULL)

BEGIN

SET NOCOUNT ON;

SET ARITHABORT ON;

DECLARE @ServerName VARCHAR(100);

SET @ServerName = ISNULL(@Server,@@SERVERNAME);

CREATE TABLE #Backup_Report(

ServerName VARCHAR(300),

Database_name varchar(300),

BackupType varchar(50),

NotBackedUpSince varchar(100),

LastBackupDate VARCHAR(50),

Backuppath nvarchar(1000),

NotBackedUpSince_hours varchar(50));

INSERT INTO #Backup_Report

SELECT @@servername as ServerName,B.name as Database_Name ,

case when A.type = 'D' then 'Full'

when A.type = 'L' then 'Log'

When A.type ='I' then 'Diff'

end as BackupType ,

case

when A.type = 'D' then

ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Days'

when A.type = 'I' then

ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Days'

when A.type = 'L' then

ISNULL(STR(ABS(DATEDIFF(minute, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Minutes'

end as NotBackedUpSince

ISNULL(Convert(char(20), MAX(backup_finish_date), 120), 'NEVER') as LastBackupDate

max(convert(char(100),physical_device_name)) as BackupPath ,

-- case when A.type = 'D' then 'Full'

--when A.type = 'L' then 'Log'

--When A.type ='I' then 'Diff'

--end as BackupType ,

case

when A.type = 'D' then

ISNULL(STR(ABS(DATEDIFF(hour, GetDate(),MAX(Backup_finish_date)))), 'NEVER')

when A.type = 'I' then

ISNULL(STR(ABS(DATEDIFF(Hour, GetDate(),MAX(Backup_finish_date)))), 'NEVER')

when A.type = 'L' then

ISNULL(STR(ABS(DATEDIFF(minute, GetDate(),MAX(Backup_finish_date)))), 'NEVER')

end as NotBackedUpSince_hours

FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A

ON A.database_name = B.name

left outer join msdb..backupmediafamily C

on C.media_set_id = A.media_set_id

where B.name not in ('tempdb')

--and A.type ='D'

GROUP BY B.Name,A.type order by 1

/*************************************************************/

/****************** HTML Preparation *************************/

/*************************************************************/

DECLARE @TableHTML VARCHAR(MAX),

@StrSubject VARCHAR(100),

@Oriserver VARCHAR(100),

@Version VARCHAR(250),

@Edition VARCHAR(100),

@ISClustered VARCHAR(100),

@SP VARCHAR(100),

@ServerCollation VARCHAR(100),

@SingleUser VARCHAR(5),

@LicenseType VARCHAR(100),

@Cnt int,

@URL varchar(1000),

@Str varchar(1000),

@NoofCriErrors varchar(3)

-- Variable Assignment

SELECT @Version = @@version

SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))

SET @Cnt = 0

IF serverproperty('IsClustered') = 0

BEGIN

SELECT @ISClustered = 'No'

END

ELSE

BEGIN

SELECT @ISClustered = 'YES'

END

SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername'))

SELECT @strSubject = 'Backup Report ('+ CONVERT(VARCHAR(100), @SERVERNAME) + ')'

SET @TableHTML =

'<font face="Verdana" size="4">Backup Report</font>

<tr>

<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>

</tr>

<tr>

<td width="39%" height="27"><font face="Verdana" size="2">' + @ServerName +'</font></td>

</tr>

</table>

<tr>

</table>

<tr>

</tr>'

SELECT

@TableHTML = @TableHTML +

'</table>

<font face="Verdana" size="4">Backup Report</font>

<tr>

<font face="Verdana" size="1" color="#FFFFFF">ServerName</font></th>

<font face="Verdana" size="1" color="#FFFFFF">Database_Name</font></th>

<font face="Verdana" size="1" color="#FFFFFF">BackupType</font></th>

<font face="Verdana" size="1" color="#FFFFFF">NotBackedupsince</font></th>

<font face="Verdana" size="1" color="#FFFFFF">lastBackupDate</font></th>

<font face="Verdana" size="1" color="#FFFFFF">backuppath</font></th>

</tr>'

SELECT

@TableHTML = @TableHTML +

'<tr>

<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ServerName), '') +'</font></td>' +

'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Database_Name), '') +'</font></td>' +

'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), BackupType), '') +'</font></td>' +

CASE WHEN NotBackedUpSince_hours > 170 and backuptype = 'Full' THEN

'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'

WHEN NotBackedUpSince_hours >30 and BackupType = 'Diff' THEN

'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'

WHEN NotBackedUpSince_hours >30 and BackupType = 'Log' THEN

'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'

ELSE

'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'

END +

'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(1000), lastBackupDate), '') +'</font></td>' +

'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(1000), backuppath), '') +'</font></td>' +

'</tr>'

FROM

#Backup_Report

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @MailProfile,--'MMStuckup',

@recipients=@MailID, --'uday.arumilli@ge.com',

@subject = @strSubject,

@body = @TableHTML,

@body_format = 'HTML' ;

DROP TABLE #Backup_Report;

SET NOCOUNT OFF;

SET ARITHABORT OFF;

END

send job failure report

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME

DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc

FROM sys.dm_hadr_availability_replica_states AS a

JOIN sys.availability_replicas AS b

ON b.replica_id = a.replica_id

WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'

BEGIN

DECLARE @Document NVARCHAR(MAX)

, @DocumentHead NVARCHAR(MAX)

, @DocumentTail NVARCHAR(MAX)

, @TableHead NVARCHAR(MAX)

, @TableBody NVARCHAR(MAX)

, @TableTail NVARCHAR(MAX)

, @Now DATETIME

SET @DocumentHead = N'<html><head>'

+ N'<style>'

+ N'th, td'

+ N'{ '

+ N' border: solid black 1px;'

+ N' padding: 1px 5px 1px 5px;'

+ N' font-size: 11pt;'

+ N'}'

+ N'</style>'

+ N'</head>'

+ N'<body>'

+ N'<h3>'

+ N'The following Jobs failed in FPSQL1Listener SQL Server'

+ N'</h3>';

SET @DocumentTail = '</body></html>';

SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'

+ N'<tr bgcolor=#C0C0C0>'

+ N' <th>Server Name</th>'

+ N' <th>Job Name</th>'

+ N' <th>Job Failed DateTime</th>'

+ N' <th>Status</th>'

+ N' <th>Error Message</th>'

+ N'</tr>';

SET @TableTail = '</table>';

SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By run_date) % 2 As [TRRow],

T1.server AS [TD],

SUBSTRING(T2.name,1,140) AS [TD],

msdb.dbo.agent_datetime(run_date, run_time) as [TD],

CASE T1.run_status

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Cancelled'

WHEN 4 THEN 'In Progress'

END AS [TD],

T1.message AS [TD]

FROM msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id

WHERE T1.run_status NOT IN (1, 4) AND T1.step_id != 0 AND run_date >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)

FOR XML RAW('tr'), ELEMENTS);

--SELECT @TableBody

IF @TableBody IS NULL

BEGIN

PRINT 'No Jobs failed';

RETURN;

END

ELSE

BEGIN

-- Replace the entity codes and row numbers

SET @TableBody = Replace(@TableBody, '_x0020_', space(1))

SET @TableBody = Replace(@TableBody, '_x003D_', '=')

SET @TableBody = Replace(@TableBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');

SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default'

, @recipients= 'SQL1Operator@frontpointsecurity.com'

, @subject = 'Failed SQL Server Jobs on SQL1'

, @body = @Document

, @body_format = 'HTML';

END

END;

ELSE

BEGIN

RETURN

END

Alert index fragmentatondetails

USE [WorkBench2Prod]

/****** Object: StoredProcedure [dbo].[AlertSendIndexFragmentationdetails_Job_Step1] Script Date: 3/1/2022 4:13:33 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER Proc [dbo].[AlertSendIndexFragmentationdetails_Job_Step1]

BEGIN

Author:Naresh DBA

Date: 04/01/2016

Purpose:Send Index fragmentation details every 1 hour

Paremeters: page_count_minimum = 1000 and fragmentation_minimum = 30

DECLARE

@Document NVARCHAR(MAX) ,

@DocumentHead NVARCHAR(MAX) ,

@DocumentTail NVARCHAR(MAX) ,

@TableHead NVARCHAR(MAX) ,

@TableBody NVARCHAR(MAX) ,

@TableTail NVARCHAR(MAX) ,

@page_count_minimum SMALLINT ,

@fragmentation_minimum INT;

SET @page_count_minimum =1000;

SET @fragmentation_minimum = 30;

SET @DocumentHead = N'<html><head>' + N'<style>' + N'th, td' + N'{ '

+ N' border: solid black 1px;' + N' padding: 1px 5px 1px 5px;'

+ N' font-size: 11pt;' + N'}' + N'</style>' + N'</head>' + N'<body>'

+ N'<h3>'

+ N'Below are the Indexes in Workbench2Prod Database with Average Fragmentation above 30 Percent'

+ N'</h3>';

SET @DocumentTail = '</body></html>';

SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'

+ N'<tr bgcolor=#C0C0C0>' + N' <th>TableName</th>'

+ N' <th>IndexName</th>' + N' <th>AverageFragmentationPercent</th>'

+ N' <th>PageCount</th>' + N'</tr>';

SET @TableTail = '</table>';

SELECT @TableBody = ( SELECT ROW_NUMBER() OVER ( ORDER BY avg_fragmentation_in_percent DESC )

% 2 AS [TRRow] ,

sys.objects.name AS [TD] ,

sys.indexes.name AS [TD] ,

CONVERT(VARCHAR(10), avg_fragmentation_in_percent, 121) AS [TD] ,

page_count AS [TD]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,

NULL, NULL,

'LIMITED')

INNER JOIN sys.objects ON sys.objects.object_id = sys.dm_db_index_physical_stats.object_id

INNER JOIN sys.indexes ON sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id

AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id

WHERE avg_fragmentation_in_percent > @fragmentation_minimum

AND objects.is_ms_shipped = 0

AND sys.dm_db_index_physical_stats.index_id > 0 and sys.indexes.name<>'NIX_AccountTicketNote_AccountTicketID_TicketStateID'--Excluded This particular index Condition on may5th 2016

AND page_count > @page_count_minimum

ORDER BY avg_fragmentation_in_percent DESC

FOR

XML RAW('tr') ,

ELEMENTS

);

IF @TableBody IS NULL

BEGIN

RETURN;

END;

ELSE

BEGIN

SET @TableBody = REPLACE(@TableBody, '_x0020_', SPACE(1));

SET @TableBody = REPLACE(@TableBody, '_x003D_', '=');

SET @TableBody = REPLACE(@TableBody, '<tr><TRRow>1</TRRow>','<tr bgcolor=#C6CFFF>');

SET @TableBody = REPLACE(@TableBody, '<TRRow>0</TRRow>', '');

SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail;

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',

@recipients ='AlertIndexFragmentationDetails@frontpoint.com',

-- 'naresh.koudagani@frontpointsecurity.com;mark.hildreth@frontpointsecurity.com',

@subject = 'Average Index Fragmentation with above 30 Percent',

@body = @Document,

@body_format = 'HTML';

END

Wednesday, January 19, 2022

The connection type "AzureStorage" specified for connection manager "SSIS Connection Manager for Azure Storage" is not recognized as a valid connection manager type

I was trying to upload CSV files to Azure using SSIS blob upload task

received many errors

1. The connection type "AzureStorage" specified for connection manager "SSIS Connection Manager for Azure Storage" is not recognized as a valid connection manager type

2.Executed as user: FPSSI\sa_SQLSvcAdmin. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 1:34:04 PM Error: 2022-01-19 13:34:05.29 Code: 0xC0016016 Source: Copy2Azure Description: Failed to decrypt protected XML node "AccountKey" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2022-01-19 13:34:05.37 Code: 0x00000000 Source: Copy2Azure Connection manager "SSIS Connection Manager for Azure Storage" (SSIS Connection Manager for Azure Storage) Description: Property "AccountKey" is not specified. End Error Error: 2022-01-19 13:34:05.37 Code: 0xC001401D Source: Copy2Azure Description: Connection "SSIS Connection Manager for Azure Storage" failed validation. End Error Error: 2022-01-19 13:34:05.39 Code: 0x00000000 Source: Copy2Azure Connection manager "SSIS Connection Manager for Azure Storage" (SSIS Connection Manager for Azure Storage) Description: Property "AccountKey" is not specified. End Error Error: 2022-01-19 13:34:05.39 Code: 0x00000000 Source: Copy2Azure Connection manager "SSIS Connection Manager for Azure Storage" (SSIS Connection Manager for Azure Storage) Description: Property "AccountKey" is not specified. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:34:04 PM Finished: 1:34:05 PM Elapsed: 0.5 seconds. The package execution failed. The step failed.

issue was:

SQL SERVER 2016
VS is 2017
Installed Azure SSIS Feature pack 2017

solution was:

SQL SERVER 2016
VS is 2017 but saved package as SSDT 2016 and modified the package with password
Azure SSIS Feature pack 2016 we have both 64 and 32 bit on dev server (uninstall 2017)

put password in command line
script out and see if its there or not, GUI not visible

Option "OFF" is not valid. The command line parameters are invalid.The step failed SQL Agent job error while running package with password

Option "Off" is not valid. The command-line parameters are invalid.The step failed

SQL Agent job error while running package with password

make sure you did not remove any space while adding /DECRYPT Password

at edit command line manually.

I removed some space and got the error, but got fixed after re doing it with out any space

Monday, January 3, 2022

failed to start project exception deserializing the package "The process cannot access the file because it is being used by another process."

Exception deserializing the package

"The process cannot access the file because it is being used by another process."

TITLE: Microsoft Visual Studio

------------------------------

Failed to start project

------------------------------

While running SSIS package i got the error “The process cannot access the file ‘*.ispac’ because it is being used by another process”.

The solution?/Fix:

1. Go to Task Manager

2. Details Tab.

3. Locate the process “DtsDebugHost.exe“.

4. Kill this process. There might be multiple instances of this process. Kill all of them.

5. Reexecute SSIS package

Wednesday, December 1, 2021

parameter sniffing explained well

https://indatawetrust.blog/2011/08/19/making-ssrs-reports-faster-get-rid-of-parameter-sniffing/

Saturday, October 2, 2021

SSIS Date variables tips

---getdate with HH MM Seconds

(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+" " +RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)+":"+RIGHT("0" + (DT_STR, 4, 1252)DATEPART("ms", GETDATE()), 4)

---getdate -9 days with HH MM Seconds
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" +(DT_STR, 2, 1252)DATEPART("dd" , DATEADD("Day",-9,GETDATE())), 2)
+" " +RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)+":"+RIGHT("0" + (DT_STR, 4, 1252)DATEPART("ms", GETDATE()), 4)

-----: Todays date
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

---: minues 1 month and minus 1 day:
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , DATEADD("mm",-1,GETDATE())), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , DATEADD("Day",-1,GETDATE())), 2)

---Last 15 minutes:
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" +(DT_STR, 2, 1252)DATEPART("dd" , DATEADD("Day",-9,GETDATE())), 2)
+" " +RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2)
+":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", DATEADD("mi",-15,GETDATE())), 2)
+":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)+":"+RIGHT("0" + (DT_STR, 4, 1252)DATEPART("ms", GETDATE()), 4)

Friday, August 20, 2021

alert index fragmentation

USE [FPAuth]

/****** Object: StoredProcedure [dbo].[AlertSendIndexFragmentationdetails_Job_Step3] Script Date: 8/20/2021 8:06:37 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER Proc [dbo].[AlertSendIndexFragmentationdetails_Job_Step3]

BEGIN

Author:Naresh DBA

Date: 04/01/2016

Purpose:Send Index fragmentation details every 1 hour

Paremeters: page_count_minimum = 1000 and fragmentation_minimum = 30

DECLARE

@Document NVARCHAR(MAX) ,

@DocumentHead NVARCHAR(MAX) ,

@DocumentTail NVARCHAR(MAX) ,

@TableHead NVARCHAR(MAX) ,

@TableBody NVARCHAR(MAX) ,

@TableTail NVARCHAR(MAX) ,

@page_count_minimum SMALLINT ,

@fragmentation_minimum INT;

SET @page_count_minimum =1000;

SET @fragmentation_minimum = 30;

SET @DocumentHead = N'<html><head>' + N'<style>' + N'th, td' + N'{ '

+ N' border: solid black 1px;' + N' padding: 1px 5px 1px 5px;'

+ N' font-size: 11pt;' + N'}' + N'</style>' + N'</head>' + N'<body>'

+ N'<h3>'

+ N'Below are the Indexes in FPAuth Database with Average Fragmentation above 30 Percent'

+ N'</h3>';

SET @DocumentTail = '</body></html>';

SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'

+ N'<tr bgcolor=#C0C0C0>' + N' <th>TableName</th>'

+ N' <th>IndexName</th>' + N' <th>AverageFragmentationPercent</th>'

+ N' <th>PageCount</th>' + N'</tr>';

SET @TableTail = '</table>';

SELECT @TableBody = ( SELECT ROW_NUMBER() OVER ( ORDER BY avg_fragmentation_in_percent DESC )

% 2 AS [TRRow] ,

sys.objects.name AS [TD] ,

sys.indexes.name AS [TD] ,

CONVERT(VARCHAR(10), avg_fragmentation_in_percent, 121) AS [TD] ,

page_count AS [TD]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,

NULL, NULL,

'LIMITED')

INNER JOIN sys.objects ON sys.objects.object_id = sys.dm_db_index_physical_stats.object_id

INNER JOIN sys.indexes ON sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id

AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id

WHERE avg_fragmentation_in_percent > @fragmentation_minimum

AND objects.is_ms_shipped = 0

AND sys.dm_db_index_physical_stats.index_id > 0

--AND sys.indexes.name<>'NIX_AccountTicketNote_AccountTicketID_TicketStateID'--Excluded This particular index Condition on may5th 2016

AND page_count > @page_count_minimum

ORDER BY avg_fragmentation_in_percent DESC

FOR

XML RAW('tr') ,

ELEMENTS

);

IF @TableBody IS NULL

BEGIN

RETURN;

END;

ELSE

BEGIN

SET @TableBody = REPLACE(@TableBody, '_x0020_', SPACE(1));

SET @TableBody = REPLACE(@TableBody, '_x003D_', '=');

SET @TableBody = REPLACE(@TableBody, '<tr><TRRow>1</TRRow>','<tr bgcolor=#C6CFFF>');

SET @TableBody = REPLACE(@TableBody, '<TRRow>0</TRRow>', '');

SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail;

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',

@recipients = 'abc@xyz.com',

@subject = 'Average Index Fragmentation with above 30 Percent',

@body = @Document,

@body_format = 'HTML';

END

Track Blocking and insert in to a table: when blocking happens

---1 create table to log info

USE [AuditDB]

/****** Object: Table [dbo].[NK_BlockingReport] Script Date: 8/20/2021 7:46:13 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[NK_BlockingReport](

[ID] [INT] IDENTITY(1,1) NOT NULL,

[ObjectName] [sysname] NULL,

[BlockingObjectName] [sysname] NULL,

[StatementText] [VARCHAR](MAX) NULL,

[BlockingStatementText] [VARCHAR](MAX) NULL,

[SessionID] [INT] NULL,

[BlockingSessionID] [INT] NULL,

[Status] [VARCHAR](50) NULL,

[BlockingStatus] [VARCHAR](50) NULL,

[DateCreated] [DATETIME] NULL,

[DatabaseName] [VARCHAR](100) NULL,

[CPU] [BIGINT] NULL,

[RunningTime] [INT] NULL,

[RuuningFrom] [VARCHAR](800) NULL,

[RuuningBy] [VARCHAR](800) NULL,

[ProgramName] [VARCHAR](100) NULL,

[LoginName] [VARCHAR](100) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

---2. Create view:

USE [AuditDB]

/****** Object: View [dbo].[NK_SP_Active] Script Date: 8/20/2021 7:49:26 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[NK_SP_Active]

SELECT

OBJECT_NAME(objectid) AS ObjectName

,SUBSTRING(stateText.text, (statement_start_offset/2)+1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(stateText.text)

ELSE statement_end_offset

END - statement_start_offset)/2) + 1) AS statement_text

,DB_NAME(sess.database_id) AS DatabaseName

,req.cpu_time AS CPU_Time

,DATEDIFF(MINUTE, last_request_start_time, GETDATE()) AS RunningMinutes

,req.Percent_Complete

,sess.HOST_NAME AS RunningFrom

,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy

,sess.session_id AS SessionID

,req.blocking_session_id AS BlockingWith

,req.reads

,req.writes

,sess.[program_name]

,sess.login_name

,sess.status

,sess.last_request_start_time

,req.logical_reads

FROM

sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id

AND sess.is_user_process = 1

CROSS APPLY

sys.dm_exec_sql_text(sql_handle) AS stateText

---3.Create job

INSERT INTO dbo.NK_BlockingReport (ObjectName,BlockingObjectName, StatementText,BlockingSessionID,BlockingStatementText,

BlockingStatus,DateCreated,[Status],DatabaseName,CPU,RunningTime,RuuningFrom,RuuningBy,ProgramName,LoginName)

SELECT W1.ObjectName AS ObjectName,

W2.ObjectName AS BlockingObjectName,

W1.Statement_Text AS BlockedStatementText,

W2.sessionid AS BlockingSessionID,

W2.Statement_Text AS BlockingStatementText,

W2.status AS BlockingStatus,

GETDATE() AS DateCreated,

w1.[Status] AS CurrentStatus,

w1.DatabaseName ,

w1.[CPU_time] ,

w2.[RunningMinutes] ,

w2.[RunningFrom] ,

w1.[RunningBy] ,

w1.[Program_Name] ,

w1.[Login_Name]

FROM NK_SP_Active W1

INNER JOIN NK_SP_Active W2

ON W1.BlockingWith > 0

AND W1.BlockingWith = W2.sessionid;

---4. Create alert --MANUALY BEST, instaed of script

USE [msdb]

/****** Object: Alert [NK_Report Blocked Processes] Script Date: 8/20/2021 7:44:40 AM ******/

EXEC msdb.dbo.sp_add_alert @name=N'NK_Report Blocked Processes',

@message_id=0,

@severity=0,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@notification_message=N'Blocking Occured please look at BlockingReport in AuditDB',

@category_name=N'[Uncategorized]',

@performance_condition=N'MSSQL$DBA_TEST:General Statistics|Processes blocked||>|1',

@job_id=N'6095582e-4202-497b-af3c-1f7842383739'