SQL Server Failover Notification via Database Mail

When a failover occurs in a SQL Server Always On Availability Group or a Failover Cluster Instance (FCI), both the SQL Server and SQL Server Agent services restart on the new node. To ensure timely awareness of failovers, we can set up an automated email alert using Database Mail.

Below is a SQL script that sends an email notification whenever a failover happens:

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

SQL Server Alert: Trigger to Notify Job Enable/Disable Status Changes

As a SQL Server DBA, keeping track of job status changes is crucial, especially when jobs are disabled or enabled without prior notice. To enhance monitoring, I’ve created a SQL Server Trigger that automatically sends an email notification whenever a job's enabled/disabled status changes.

Here's the complete trigger script:

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

GO

Automating Blocking Query Alerts in SQL Server

Blocking in SQL Server can degrade performance and lead to application slowdowns. Identifying and addressing long-running blocking queries is crucial for database administrators. In this post, we’ll create an automated job that runs every 10 minutes to detect and alert on blocking queries that persist for more than 5 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

Unable to Shrink TempDB in SQL Server? Try These Steps

If you're struggling to shrink TempDB in SQL Server, executing the following commands might help. These queries will clear the plan cache, buffer pool, and unused cache entries, making it easier to shrink TempDB.

USE TempDB

GO

-- Clear the plan cache in the server

DBCC FREEPROCCACHE

GO

-- Clear buffers from the buffer pool

DBCC DROPCLEANBUFFERS

GO

-- Release all unused cache entries from all caches

DBCC FREESYSTEMCACHE ('ALL')

GO

After executing these commands, try shrinking TempDB again.

⚠ Note: Use these commands with caution in a production environment, as they may impact query performance temporarily.

SQL Server: Script Out User Permissions Before a Database Refresh

When performing a database refresh, it's crucial to retain existing user permissions to avoid post-refresh access issues. The script below extracts all user-related permissions, allowing you to restore them after the refresh.

What This Script Does:

✔ Extracts database user creation statements
✔ Captures role memberships
✔ Lists object-level permissions
✔ Retrieves database-level and schema-level 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 

--Note: Always test the extracted permissions before applying them in production.