Sending Missing backup status report through e-mail in SQL Server

use msdb
go
create table Missing_Backups (
[DB Name] [varchar](100) NOT NULL,
[Type] [varchar] (5) NOT NULL,
[Last Backup] [varchar](100) NULL)

insert into Missing_Backups
SELECT d.name AS "Database",'Full' as "Type",
       ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
           WHERE type LIKE 'D'
           GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)
  AND d.name <> 'tempdb'
UNION
SELECT d.name AS "Database",'Trn' as "Type",
       ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
           WHERE type LIKE 'L'
           GROUP BY database_name,type) b on d.name=b.database_name
WHERE recovery_model = 1
  AND (backupdate IS NULL OR backupdate < getdate()-1)
  AND d.name <> 'tempdb'
 
declare @cnt int 
select @cnt=COUNT(1) from Missing_Backups   
if (@cnt > 0)
begin

declare @strsubject varchar(100)
select @strsubject='Check for missing backups on ' + @@SERVERNAME

declare @tableHTML  nvarchar(max);
set @tableHTML =
N'<H1>Databases Missing Backups Listing - ' + @@SERVERNAME +'</H1>' +
N'<table border="1">' +
N'<tr><th>DB Name</th><th>Type</th>' +
N'<th>Last Backup</th></tr>' +
CAST ( ( SELECT td = [DB Name], '',
                    td = [Type], '',
                    td = [Last Backup]
  FROM Missing_Backups
  FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
--@from_address='svc_ajay@ajay.com',
@recipients='ajeyudu@ajay.com',
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML' ,
@profile_name='SQLMail'
end

drop table Missing_Backups

Sending query results to Excel through e-mail in SQL Server

declare @body1 varchar(4000)
declare @body2 varchar(4000)
DECLARE @tab char(1) = CHAR(9)
DECLARE @CRLF char(2)
SELECT @CRLF=CHAR(13)+CHAR(10)

--@query_result_separator=

set @body1 = 'Please find the attached list. '

  +@CRLF+ N'Thanks, SQL DBA Team.'

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='sqldbmail', --- Please chnage the profile name

    @recipients='Compliance@ajeyudu.com', -- Please change the email address
--@blind_copy_recipients='SQLADBA@ajeyudu.com',

    @subject = 'ajeyudu database:-AC-206 control to audit permissions Monthly Report.', --- Please change the subject line as per your requirement

    @body = @body1,

    @body_format = 'HTML',
             
    @query = 'Select * from DB_Name..object_name', --- Select select statement

    @query_result_header = 1,
@query_result_separator =@tab,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'Ajeyudu_SY_USR.csv', -- File name

    @query_result_no_padding = 1

              

List All the Stored Procedure Modified in Last Few Days

SELECT name, modify_date, create_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE())<7 --- This is for 7 days if you want more than 7 day, you can the change

AlwaysOn – The secondary database doesn’t come automatically when the primary instance of SQL Server goes down

In this blog we would discuss about behavior of AlwaysOn availability group where the secondary database doesn't come automatically when the primary instance goes down. The secondary database goes into Resolving state. On the failover cluster manager the resource appears in fail state.

While doing some research, we found that if we stop SQL Service manually on primary replica, it fails over to second node only once. Any further attempts of stopping SQL Service (to test auto failover) would not cause failover. We looked into cluster logs and found below.

From the Cluster Log:
------------------------------
00001b50.00001e54::2012/01/31-03:22:05.170 INFO  [RCM] HandleMonitorReply: TERMINATERESOURCE for 'KEXPServices_Staging', gen(3) result 0.
00001b50.00001e54::2012/01/31-03:22:05.170 INFO  [RCM] TransitionToState(KEXPServices_Staging) [Terminating to Failed]—>Failed.
00001b50.00001e54::2012/01/31-03:22:05.170 INFO  [RCM] rcm::RcmGroup::UpdateStateIfChanged: (KEXPServices_Staging, Pending --> Failed)
00001b50.00000cb4::2012/01/31-03:22:05.185 WARN  [RCM] Not failing over group KEXPServices_Staging, failoverCount 2, failover threshold 4294967295, nodeAvailCount 1.
00001b50.00000cb4::2012/01/31-03:22:05.185 INFO  [RCM] Will retry online of KEXPServices_Staging in 3600000 milliseconds.

We looked further into resource in failover cluster manager and found the cause as below.
By default the failover properties for the Resource group is set as follows.

Maximum Failures in the specified period was set to 1
Period (Hours) was set to 6
This means that when a failover happens and the resource fails to come online, the cluster service would try to get the resource online only once. It would try only once in 6 hours. Since the cluster service would check the log on how many errors have occurred in the past, it verifies the count set for “Maximum Failures in the specified period” and hence would fail if the number of failures equals or exceeds.

Workaround:

A manual failover will work here.
Hence in order for us to get this work automatically, is to have the value set to a higher number for “Maximum Failures in the specified period”.
Have these values set as follows. This means that within an hour the cluster service will try to get the group online 60 times. In an ideal production environment we don’t expect so many failovers to happen.
1. Maximum Failures in the specified period: set to 60
2. Period (Hours): set to 1


Here is the screenshot showing the properties

DISCLAIMER: The number shown in the blog are for testing purpose only. Please perform testing in your environment before implementing the values.

Source: MS Article

How To Find "Last Checkdb Run For All Databases" in SQL server

DECLARE @svr_name varchar(100)
select @svr_name = CAST(SERVERPROPERTY('ServerName')AS sysname)
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [OBJECT] VARCHAR(255),
       Field VARCHAR(255),
       [VALUE] VARCHAR(255)
)

CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,
       ServerName varchar(100), 
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum INT
)

DECLARE
@DBName SYSNAME,
@SQL    VARCHAR(512);

DECLARE dbccpage CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT name
FROM sys.databases
WHERE 1 = 1
AND STATE = 0
 
OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Use [' + @DBName +'];' + CHAR(10)+ CHAR(13)
SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' + CHAR(10)+ CHAR(13)

INSERT INTO #temp
EXECUTE (@SQL);
SET @SQL = ''

INSERT INTO #DBCCRes
( ServerName,DBName, dbccLastKnownGood,RowNum )
SELECT @svr_name,@DBName, VALUE
, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
FROM #temp
WHERE Field = 'dbi_dbccLastKnownGood';

TRUNCATE TABLE #temp;

FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;

SELECT ServerName,DBName,dbccLastKnownGood
FROM #DBCCRes
WHERE RowNum = 1;

DROP TABLE #temp
DROP TABLE #DBCCRes