SQL SERVER DBA and Azure SQL

Trigger for enable and disable jobs alerts in SQL Server

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

USE [msdb]

CREATE TRIGGER [dbo].[JobStatusAlert]

ON [dbo].[sysjobs]

AFTER UPDATE

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

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

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

DBCC FREEPROCCACHE

--Use below query for clearing buffers from bufferpool.

DBCC DROPCLEANBUFFERS

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

DBCC FREESYSTEMCACHE ('ALL')

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

SQL SERVER DBA and Azure SQL

Pages

Trigger for enable and disable jobs alerts in SQL Server

Blocking alert from SQL Server

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

Before Database refresh script out user permissions

Known issues with Azure SQL Managed Instance

Report Abuse

My Blog List