T-SQL query to get tables size in GB with no of rows in SQL Sever

SELECT
 s.name + '.' + t.Name AS [Table Name],
 part.rows AS [Total Rows In Table - Modified],
 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))
 AS [Table's Total Space In GB]
FROM
 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id
                    AND idx.Index_id = part.Index_id
 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255
GROUP BY t.Name, s.name, part.rows
ORDER BY [Table's Total Space In GB] DESC

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