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


No comments:

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

No comments:

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 

Read more »
No comments:

Known issues with Azure SQL Managed Instance

Known issues with Azure SQL Managed Instance 

No comments:

Top 3 Table Row count report via email alert in SQL Server

 DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)  

DECLARE @xml NVARCHAR(MAX)  

DECLARE @body NVARCHAR(MAX)  

DECLARE @Max_Records TABLE (

[DB_name] [nvarchar](128) NULL,

[SchemaName] [sysname] NOT NULL,

[TableName] [sysname] NOT NULL,

[RowCounts] [bigint] NOT NULL,

[Used_MB] [numeric](36, 2) NULL,

[Unused_MB] [numeric](36, 2) NULL,

[Total_MB] [numeric](36, 2) NULL,

[Capture_Date] Date

)

SELECT @command = 'use [?]; 

SELECT Top 3

DB_Name(),s.Name AS SchemaName,

t.Name AS TableName,

p.rows AS RowCounts,

CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,

CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,

CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB,Getdate() As Capture_Date

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

GROUP BY t.Name, s.Name, p.Rows

ORDER BY CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) desc' 

INSERT INTO @Max_Records   

EXEC sp_MSForEachDB @command     

--Select * from @Max_Records

SET @xml = CAST(( SELECT DB.DB_name As 'td',' ',DB.SchemaName AS 'td' ,' ',DB.TableName as 'td' ,' ',DB.RowCounts as 'td',' ',Db.Used_MB as 'td',  

' ',DB.Unused_MB as 'td' ,' ',DB.Total_MB as 'td',' ', convert(varchar(20), DB.Capture_Date,120) as 'td'  from @Max_Records  DB  Order by DB.RowCounts Desc

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  

SET @body ='<html><body><H3>Top 3 Table Row count</H3>  

<table border = 1>   

<th> Database Name </th><th> Schema  Name </th> <th> TableName </th> <th> RowCounts</th> <th> Used_MB </th> <th> Unused_MB </th>  

<td bgcolor=#F0E68C><b>Total_MB(%)<b> </td> <th>Capture_Date</th></tr>'      

--<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th><th> FreeSpace(%) </th> <th>Capture_Date</th></tr>'      

SET @body = @body + @xml +'</table></body></html>'  

Set @cmd= @@SERVERNAME + ': Top 3 Table Row count '  

SELECT Top 1 @profile_name=name  FROM msdb.dbo.sysmail_profile  

EXEC msdb.dbo.sp_send_dbmail  

@profile_name = @profile_name, -- replace with your SQL Database Mail Profile   

@body = @body,  

@body_format ='HTML',  

@recipients = 'IT.ESA-DBA@sqldbanow.com', -- replace with your email address  

@subject = @cmd

No comments:
Subscribe to: Posts (Atom)