Welcome to SQLDBANow.com! This blog, created by Bandaru Ajeyudu, is dedicated to learning and sharing knowledge about SQL DBA and Azure SQL. Join us as we explore insights, tips, and best practices in the world of SQL Database Administration and Azure SQL.
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.
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.
✔ 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
Automating Top 3 Table Row Count Reports in SQL Server
As database administrators, monitoring table sizes and row counts is crucial for performance tuning, space management, and optimizing database resources. One of the ways to accomplish this is by generating a report that lists the top 3 tables with the highest row counts in each database, along with space usage details.
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
In SQL Server environments, ensuring timely backups and monitoring their status is essential for maintaining database integrity. Here's a simple approach to automate the process of checking backup statuses and sending email notifications if there are any failures.
Set nocount ON
Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500),@cmd Varchar (1000),@profile_name Varchar(1000),@Bkupstatus Varchar (500)
If OBJECT_ID('tempdb..#DBBackup') is not NULL
Drop table tempdb..#DBBackup
If OBJECT_ID('tempdb..#Log') is not NULL
Drop table tempdb..#Log
Create Table #DBBackup (Server varchar(500),DB varchar(1000), Backup_Status varchar(1000))
Create Table #Log (Server varchar(500),DB varchar(1000),Last_Backup Datetime,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare cur CURSOR for Select Name from sys.databases where name not in ('tempdb') and state_desc='ONLINE' and recovery_model_desc='FULL' order by Name
Open cur
fetch next from cur into @DBName
while @@fetch_status=0
Begin
Select @dbIs=Databasepropertyex (@DBName,'Updateability'),@dbStatus=Databasepropertyex (@DBName,'Status')
Select @replicaid=replica_id from sys.databases where name=@DBName
If @replicaid is NULL And @dbStatus='ONLINE'
Begin
insert into #DBBackup Select Upper(@@SERVERNAME) "Server", Upper(@DBName) "Database", ''
End
Else If (@dbIs='READ_WRITE' or @dbIs IS NULL ) And @dbStatus='ONLINE' AND @replicaid is Not NULL
Begin
insert into #DBBackup Select Upper(@@SERVERNAME) "Server", Upper(@DBName) "Database", ''
End
fetch next from cur into @DBName
End
Close Cur
Deallocate Cur
insert into #Log
SELECT @@Servername 'Server', Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size/1048576) AS Backup_Size ,(Compressed_Backup_Size/1048576) AS Compressed_Backup_Size
FROM MSDB.dbo.BackupSet
WHERE Type = 'l'
GROUP BY Database_Name,backup_size,compressed_backup_size
Having DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) <5
order by 2
Update #DBBackup
Set Backup_Status='FAILED'
where DB not in (Select DB from #Log)
Update #DBBackup
Set Backup_Status='Completed'
where Backup_Status<>'FAILED'
--SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',L.Last_Backup as 'td','',L.Backup_Size as 'td',
--'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join #Log L on DB.DB=L.DB
--SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',convert(varchar(20),L.Last_Backup,120) as 'td','',L.Backup_Size as 'td',
--'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join #Log L on DB.DB=L.DB where DB.Backup_Status='FAILED'
Select @Bkupstatus=Backup_Status from #DBBackup
If @Bkupstatus='FAILED'
Begin
SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',convert(varchar(20),L.Last_Backup,120) as 'td','',L.Backup_Size as 'td',
'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join #Log L on DB.DB=L.DB where DB.Backup_Status='FAILED'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Log Database Backup status </H3>
<table border = 1>
<tr>
<th> Server Name </th> <th> Database Name </th> <th> Backup Status </th> <th> Last_Backup </th><th> Backup_Size (MB) </th> <th> Compressed_Backup_Size (MB) </th> </tr>'
--<td bgcolor=#F0E68C><b>FreeSpace(%)<b> </td> <th>Capture_Date</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @cmd= @@SERVERNAME + ': Log Database Backup '
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
End
DROP TABLE [#DBBackup]
DROP TABLE [#Log]
Set nocount ON
Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500),@cmd Varchar (1000),@profile_name Varchar(1000)
If OBJECT_ID('tempdb..#DBBackup') is not NULL
Drop table tempdb..#DBBackup
If OBJECT_ID('tempdb..#Log') is not NULL
Drop table tempdb..#Log
Create Table #DBBackup (Server varchar(500),DB varchar(1000), Backup_Status varchar(1000))
Create Table #Log (Server varchar(500),DB varchar(1000),Last_Backup Date,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare cur CURSOR for Select Name from sys.databases where name not in ('tempdb') and state_desc='ONLINE' order by Name
Open cur
fetch next from cur into @DBName
while @@fetch_status=0
Begin
Select @dbIs=Databasepropertyex (@DBName,'Updateability'),@dbStatus=Databasepropertyex (@DBName,'Status')
Select @replicaid=replica_id from sys.databases where name=@DBName
If @replicaid is NULL And @dbStatus='ONLINE'
Begin
insert into #DBBackup Select Upper(@@SERVERNAME) Server, Upper(@DBName) Database, ''
End
Else If (@dbIs='READ_WRITE' or @dbIs IS NULL ) And @dbStatus='ONLINE' AND @replicaid is Not NULL
Begin
insert into #DBBackup Select Upper(@@SERVERNAME) Server, Upper(@DBName) Database, ''
End
fetch next from cur into @DBName
End
Close Cur
Deallocate Cur
insert into #Log
SELECT @@Servername 'Server', Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size1048576) AS Backup_Size ,(Compressed_Backup_Size1048576) AS Compressed_Backup_Size
FROM MSDB.dbo.BackupSet
WHERE Type = 'd'
GROUP BY Database_Name,backup_size,compressed_backup_size
Having DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) 120
order by 2
Update #DBBackup
Set Backup_Status='FAILED'
where DB not in (Select DB from #Log)
Update #DBBackup
Set Backup_Status='Completed'
where Backup_Status'FAILED'
SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',L.Last_Backup as 'td','',L.Backup_Size as 'td',
'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join #Log L on DB.DB=L.DB
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='htmlbodyH3Full Database Backup status H3
table border = 1
tr
th Server Name th th Database Name th th Backup Status th th Last_Backup th th Backup_Size (MB) th th Compressed_Backup_Size (MB) th tr'
SET @body = @body + @xml +'tablebodyhtml'
Set @cmd= @@SERVERNAME + ' Full Database Backup '
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 = 'itesadba@sqldbanow.com', -- replace with your email address
@subject = @cmd
DROP TABLE [#DBBackup]
DROP TABLE [#Log]