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
Sunday, March 13, 2022
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]
GO
/****** Object: StoredProcedure [dbo].[Backup_Report] Script Date: 3/1/2022 4:18:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Backup_Report] (
@MailProfile NVARCHAR(200),
@MailID NVARCHAR(2000),
@Server VARCHAR(100) = NULL)
AS
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>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">
<tr>
<td width="39%" height="22" bgcolor="#000080"><b>
<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>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
<tr>
</table>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
</tr>'
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Backup Report</font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
<tr>
<th align="left" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ServerName</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database_Name</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">BackupType</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">NotBackedupsince</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">lastBackupDate</font></th>
<th align="left" width="136" bgColor="#000080">
<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]
GO
/****** Object: StoredProcedure [dbo].[AlertSendIndexFragmentationdetails_Job_Step1] Script Date: 3/1/2022 4:13:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[AlertSendIndexFragmentationdetails_Job_Step1]
AS
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
END
Subscribe to:
Posts (Atom)