Send Email for Service Restarts in SQL server

 --Send Email for Service Restarts in SQL server

USE msdb

GO

-- Declare variables for necessary email content

DECLARE @ServerName VARCHAR(128),

@ComputerNamePhysicalNetBIOS VARCHAR(128),

@Datetime DATETIME,

@EmailRecipients VARCHAR(512),

@EmailSubject VARCHAR(128),

@MessageBody VARCHAR(512)

-- Set variables to proper values

SELECT @ComputerNamePhysicalNetBIOS = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(128)),

@ServerName = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(128)),

@Datetime = GETDATE(),

@EmailRecipients = 'bajeyudu@sqldbanow.com', -- if more than one email address use ; between email addresses

@EmailSubject = 'SQL Server Services Have Been Started!!!'

SELECT @MessageBody = 'SQL Server services have been started on a SQL Server Instance named ' + @ServerName + CHAR(13) +

'running on windows server ' + @ComputerNamePhysicalNetBIOS + '.' + CHAR(13) + CHAR(13) +

'Investigate the service restart if it has not been communicated.'

EXEC sp_send_dbmail

@recipients = @EmailRecipients,

@subject = @EmailSubject,

@body = @MessageBody,

@body_format = 'TEXT'

Suspect Pages Alert in SQL Server

 --By using below mentioned script will get suspect pages report via email 

declare @count integer

declare @tableHTML  nvarchar(MAX);

declare @subj nvarchar(100)

select @count=count(1)

from msdb.dbo.suspect_pages;

set @subj = 'Suspect Pages Found in ' + @@SERVERNAME;

set @tableHTML =

    N'<H1>Suspect Pages Found in ' + @@SERVERNAME + ', details are below.</H1>' +

    N'<table border="1">' +

    N'<tr><th>Database ID</th><th>Database</th>' +

    N'<th>File ID</th><th>File</th><th>Page ID</th>' +

    N'<th>Event Desc</th><th>Error Count</th><th>Last Updated</th></tr>' +

    cast ( ( select td = sp.database_id,       '',

       td = d.name,       '',

       td = sp.file_id,       '',

       td = mf.physical_name,       '',

       td = sp.page_id,       '',

       td = case when sp.event_type = 1 then '823 or 824 error other than a bad checksum or a torn page'

            when sp.event_type = 2 then 'Bad checksum'

            when sp.event_type = 3 then 'Torn Page'

            when sp.event_type = 4 then 'Restored (The page was restored after it was marked bad)'

            when sp.event_type = 5 then 'Repaired (DBCC repaired the page)'

            when sp.event_type = 7 then 'Deallocated by DBCC'

       end,       '',

       td = sp.error_count,       '',

       td = sp.last_update_date

from msdb.dbo.suspect_pages sp

inner join sys.databases d on d.database_id=sp.database_id

inner join sys.master_files mf on mf.database_id=sp.database_id and mf.file_id=sp.file_id

              for xml path('tr'), TYPE 

    ) as nvarchar(max) ) +

    N'</table>' ;

IF @count > 0

  exec msdb.dbo.sp_send_dbmail

    @recipients=N'bandaruajeyudu@outlook.com',

    @body= @tableHTML, 

    @subject = @subj,

    @body_format = 'HTML',

    @profile_name ='SQLMail'

GO

delete from msdb.dbo.suspect_pages

where last_update_date < getdate()-90;

SQL Jobs Failed report via email from SQL Server

Using below mentioned script to get failed jobs report in SQL Server.

create table Failed_Jobs (

[Status] [varchar](6) NOT NULL,

[Job Name] [varchar](100) NULL,

[Step ID] [varchar](5) NULL,

[Step Name] [varchar](30) NULL,

[Start Date Time] [varchar](30) NULL,

[Message] [nvarchar](4000) NULL)

insert into Failed_Jobs

select 'FAILED' as Status, cast(sj.name as varchar(100)) as "Job Name",

       cast(sjs.step_id as varchar(5)) as "Step ID",

       cast(sjs.step_name as varchar(30)) as "Step Name",

       cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',

       sjh.message as "Message"

from sysjobs sj

join sysjobsteps sjs 

 on sj.job_id = sjs.job_id

join sysjobhistory sjh 

 on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id

where sjh.run_status <> 1

  and cast(sjh.run_date as float)*1000000+sjh.run_time > 

      cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at 7am

union

select 'FAILED',cast(sj.name as varchar(100)) as "Job Name",

       'MAIN' as "Step ID",

       'MAIN' as "Step Name",

       cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',

       sjh.message as "Message"

from sysjobs sj

join sysjobhistory sjh 

 on sj.job_id = sjh.job_id

where sjh.run_status <> 1 and sjh.step_id=0

  and cast(sjh.run_date as float)*1000000+sjh.run_time >

      cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at

declare @cnt int  

select @cnt=COUNT(1) from Failed_Jobs    

if (@cnt > 0)

begin

declare @strsubject varchar(100)

select @strsubject='Check the following failed jobs on ' + @@SERVERNAME

declare @tableHTML  nvarchar(max);

set @tableHTML =

N'<H1>Failed Jobs Listing - ' + @@SERVERNAME +'</H1>' +

N'<table border="1">' +

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

N'<th>Step ID</th><th>Step Name</th><th>Start Date</th>' +

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

CAST ( ( SELECT td = [Status], '',

                    td = [Job Name], '',

                    td = [Step ID], '',

                    td = [Step Name], '',

                    td = [Start Date Time], '',

                    td = [Message]

  FROM Failed_Jobs

  FOR XML PATH('tr'), TYPE 

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@from_address='BANDARUAJEYUDU@OUTLOOK.com',

@recipients='BANDARUAJEYUDU@OUTLOOK.com',

@subject = @strsubject,

@body = @tableHTML,

@body_format = 'HTML' ,

@profile_name='SQLMail'

end

drop table Failed_Jobs

Automatic alert when cluster fail over happened

When  fail over happen, both SQL server and Agent service will get restarted, Then we will get Notification Alert When Fail Over Happened 

DECLARE @importance AS VARCHAR(6)

DECLARE @body AS NVARCHAR(1000)

DECLARE @Subject  AS NVARCHAR(1000)

DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)

DECLARE @recipients VARCHAR(100)

SELECT  @InstanceName =@@SERVERNAME

SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))

SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName

SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName

SELECT  @importance ='High'

EXEC msdb.dbo.sp_send_dbmail

     @profile_name ='sqlmail',

         @recipients ='bajeyudu@SQLDBANOW.com;',

     --@recipients=@recipientsList,

     @subject = @subject ,

     @body = @body,

     @body_format = 'HTML' ,

     @importance=@importance

Trigger for enable and disable jobs alerts in SQL Server

below mentioned trigger script to monitor the jobs enable & disable alerts. 

USE [msdb]

GO

 

CREATE TRIGGER [dbo].[JobStatusAlert]

   ON [dbo].[sysjobs]

   AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

 

-- Check if job is enabled/disabled

DECLARE @MailBody VARCHAR(300)

-- Check if job status is changed (enabled/disabled)

IF (SELECT TOP 1 CASE WHEN d.enabled = i.enabled THEN 0 ELSE 1 END

FROM deleted d INNER JOIN inserted i

ON d.job_id = I.job_id) = 1

BEGIN

 

-- Get session detail and create a message

SELECT TOP 1 @MailBody = 'Job "'

+i.name

+'" is recently '

+CASE WHEN i.enabled = 0

THEN ' DISABLED '

ELSE ' ENABLED 'END

+ ' by user '

+login_name

+ ' with session id '

+ CAST (c.session_id AS VARCHAR(3))

+' and host name '

+host_name +' at '

+ CONVERT(VARCHAR(50),last_request_end_time,109)

FROM sys.dm_exec_connections c

INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)

CROSS APPLY inserted i

WHERE text LIKE '%exec msdb.dbo.sp_help_job%'

AND text NOT LIKE '%SELECT c.session_id'

ORDER BY last_read DESC

 

-- Send mail to DBA Team

EXEC msdb.dbo.sp_send_dbmail

@recipients='bajeyudu@SQLDBANOW.com', -- change mail address accordingly

@subject = 'Job Status Changed at SQLDBANOWDB01 Server',

@profile_name = 'Sqlmail', -- Change profile name accordingly

@body = @MailBody;

END

 

END

Blocking alert from SQL Server

Below mentioned script will send the blocking queries which are blocked for more than 5 minutes and we need to create job for below script to run every 10 minutes

SET NOCOUNT ON

DECLARE @xml nvarchar(max)

SELECT @xml = Cast((SELECT @@SERVICENAME AS 'td','',b.session_id AS 'td',

'',

(b.wait_duration_ms/1000)/60 AS 'td',

'',

b.wait_type AS 'td',

'',

b.blocking_session_id AS 'td',

'',

t.text AS 'td'

FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id

OUTER APPLY

sys.dm_exec_sql_text(sql_handle) t

WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>300000

FOR xml path('tr'), elements) AS NVARCHAR(max))

Declare @body nvarchar(max)

SET @body =

'<html>

<head>

<style>

table, th, td

 {

border: 1px solid black;

border-collapse: collapse;

text-align: center;

}

</style>

</head>

<body>

<H2>

Blocking queries

</H2>

<table>

 <tr>

<th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th>

 <th> Blocking Session ID</th><th>Query waiting to execute</th>

</tr>'

SET @body = @body + @xml + '

</table>

</body>

</html>'

if(@xml is not null)

BEGIN

EXEC msdb.dbo.Sp_send_dbmail

@profile_name = 'sqlmail',

@body = @body,

@body_format ='html',

@recipients = 'bajeyudu@sqldbanow.com',

@copy_recipients ='ajay@sqldbanow.com', -- list of Email recipients

@subject = 'Blocking queries Alert-SQLDBANOWDB01';

END

SET NOCOUNT OFF

If you are not able to shrink tempdb execute below query and try again

  -----If you are not able to shrink tempdb execute below query and try again

USE TempDB

GO

--Use below query for clearing plan cache in the server.

DBCC FREEPROCCACHE

go

--Use below query for clearing buffers from bufferpool.

DBCC DROPCLEANBUFFERS

go

-- Use below query, this will Releases all unused cache entries from all caches.

DBCC FREESYSTEMCACHE ('ALL')

go

Before Database refresh script out user permissions

--Before Database refresh script out user permissions

DECLARE 

    @sql VARCHAR(2048)

    ,@sort INT 

DECLARE tmp CURSOR FOR

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

/*********   DB CONTEXT STATEMENT    *********/

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

SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],

        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],

        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],

        2 AS [-- RESULT ORDER HOLDER --]

UNION

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

/*********     DB USER CREATION      *********/

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

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],

        3 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],

        4 AS [-- RESULT ORDER HOLDER --]

FROM    sys.database_principals AS rm

WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

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

/*********    DB ROLE PERMISSIONS    *********/

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

SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],

        5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  'EXEC sp_addrolemember @rolename ='

    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],

        6 AS [-- RESULT ORDER HOLDER --]

FROM    sys.database_role_members AS rm

WHERE   USER_NAME(rm.member_principal_id) IN (  

                                                --get user names on the database

                                                SELECT [name]

                                                FROM sys.database_principals

                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas

                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

                                              )

--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],

        7 AS [-- RESULT ORDER HOLDER --]

UNION

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

/*********  OBJECT LEVEL PERMISSIONS *********/

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

SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

        8 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  CASE 

            WHEN perm.state <> 'W' THEN perm.state_desc 

            ELSE 'GRANT'

        END

        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects

        + CASE

                WHEN cl.column_id IS NULL THEN SPACE(0)

                ELSE '(' + QUOTENAME(cl.name) + ')'

          END

        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

        + CASE 

                WHEN perm.state <> 'W' THEN SPACE(0)

                ELSE SPACE(1) + 'WITH GRANT OPTION'

          END

            AS [-- SQL STATEMENTS --],

        9 AS [-- RESULT ORDER HOLDER --]

FROM    

    sys.database_permissions AS perm

        INNER JOIN

    sys.objects AS obj

            ON perm.major_id = obj.[object_id]

        INNER JOIN

    sys.database_principals AS usr

            ON perm.grantee_principal_id = usr.principal_id

        LEFT JOIN

    sys.columns AS cl

            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

--WHERE usr.name = @OldUser

--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],

    10 AS [-- RESULT ORDER HOLDER --]

UNION

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

/*********    DB LEVEL PERMISSIONS   *********/

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

SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

        11 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  CASE 

            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

            ELSE 'GRANT'

        END

    + SPACE(1) + perm.permission_name --CONNECT, etc

    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>

    + CASE 

            WHEN perm.state <> 'W' THEN SPACE(0) 

            ELSE SPACE(1) + 'WITH GRANT OPTION' 

      END

        AS [-- SQL STATEMENTS --],

        12 AS [-- RESULT ORDER HOLDER --]

FROM    sys.database_permissions AS perm

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0

    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas

    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],

        13 AS [-- RESULT ORDER HOLDER --]

UNION 

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

        14 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  CASE

            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

            ELSE 'GRANT'

            END

                + SPACE(1) + perm.permission_name --CONNECT, etc

                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

                + QUOTENAME(SCHEMA_NAME(major_id))

                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default

                + CASE

                    WHEN perm.state <> 'W' THEN SPACE(0)

                    ELSE SPACE(1) + 'WITH GRANT OPTION'

                    END

            AS [-- SQL STATEMENTS --],

        15 AS [-- RESULT ORDER HOLDER --]

from sys.database_permissions AS perm

    inner join sys.schemas s

        on perm.major_id = s.schema_id

    inner join sys.database_principals dbprin

        on perm.grantee_principal_id = dbprin.principal_id

WHERE class = 3 --class 3 = schema

ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp

FETCH NEXT FROM tmp INTO @sql, @sort

WHILE @@FETCH_STATUS = 0

BEGIN

        PRINT @sql

        FETCH NEXT FROM tmp INTO @sql, @sort    

END

CLOSE tmp

DEALLOCATE tmp 

Known issues with Azure SQL Managed Instance

Known issues with Azure SQL Managed Instance