This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
DB last updated and last_select_query_update_script
Database Growth status report for All DB(s) via Email in SQL Server
DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @dbsize Table
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
insert into @dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type'
-------------------log size--------------------------------------
-- if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
--drop table #logsize
Declare @logsize Table
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
insert into @logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type'
--------------------------------database free size
--if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
--drop table #dbfreesize
Declare @dbfreesize Table
(name sysname,database_size varchar(50),Freespace varchar(50)default (0.00))
insert into @dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (202,204,211,212,213,214,215,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions'
-----------------------------------
--if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
--drop table #alldbstate
Declare @alldbstate Table
(dbname sysname,DBstatus varchar(55),R_model Varchar(30))
--select * from sys.master_files
insert into @alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
--select * from #dbsize
insert into @dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from @alldbstate where DBstatus <> 'online'
insert into @logsize(Dbname)
select dbname from @alldbstate where DBstatus <> 'online'
insert into @dbfreesize(name)
select dbname from @alldbstate where DBstatus <> 'online'
Declare @DB_space_Details Table
(
[Dbname] [sysname] NOT NULL,
[dbstatus] [varchar](50) NULL,
[Recovery_Model] [varchar](40) NULL,
[DBsize] [decimal](38, 2) NULL,
[file_Size_MB] [decimal](30, 2) NULL,
[Space_Used_MB] [decimal](30, 2) NULL,
[Free_Space_MB] [decimal](30, 2) NULL,
[Log_File_Size_MB] [decimal](38, 2) NULL,
[log_Space_Used_MB] [decimal](30, 2) NULL,
[log_Free_Space_MB] [decimal](30, 2) NULL,
[DB_Freespace] [varchar](50) NULL,
[Capture_Date] [datetime] NOT NULL
)
Insert into @DB_space_Details
select
d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace,getdate() 'Current_Date'
from @dbsize d join @logsize l
on d.Dbname=l.Dbname join @dbfreesize fs
on d.Dbname=fs.name
order by Dbname
--Select * from @DB_space_Details
--Select * from DB_space_Details
SET @xml = CAST(( SELECT DB.DBname As 'td',' ',DB.dbstatus AS 'td' ,' ',DB.Recovery_Model as 'td' ,' ',DB.DBsize 'td',' ',DB.File_Size_MB as 'td',
' ',DB.Space_Used_MB As 'td' ,' ',DB.Free_Space_MB as 'td',' ', DB.Log_File_Size_MB As 'td', ' ', DB.Log_Space_Used_MB As 'td', ' ',
DB.log_free_Space_MB As 'td',' ', DB.DB_Freespace As 'td' ,' ', convert(varchar(20), DB.Capture_Date,120) as 'td' from @DB_space_Details DB
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H5>Database Growth status </H5>
<table border = 1>
<th> Database Name </th><th> Ddbstatus </th> <th> Recovery_Model </th> <th> DBsize </th> <th> File_Size_MB </th> <th> Space_Used_MB </th>
<th> Free_Space_MB </th><th> Log_File_Size_MB </th>
<th> Log_Space_Used_MB </th> <th> log_free_Space_MB </th> <td bgcolor=#F0E68C><b>DB_Freespace<b> </td> <th>Capture_Date</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @cmd= @@SERVERNAME + ': Database Space '
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 = 'ajeyudub@sqldbanow.com', -- replace with your email address
@subject = @cmd
DB_Growth status Report Alert
Hi Team,
Please find below mentioned script to get an alert DB_Growth status Report via email.
Once executed the following script and then run exec [DB_Growth] proc to get an alert
USE [master]
GO
/****** Object: StoredProcedure [dbo].[DB_Growth] Script Date: 10/28/2021 5:26:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[DB_Growth]
As
Begin
DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @DBSpaceInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
PhysicalFileName NVARCHAR(520),
FileSizeMB DECIMAL(10,2),
SpaceUsedMB DECIMAL(10,2),
FreeSpaceMB DECIMAL(10,2),
FreeSpacePct varchar(8),
Capture_Date Datetime
)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName , name
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB,
CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
Getdate() As Capture_Date
from sys.database_files a'
INSERT INTO @DBSpaceInfo
EXEC sp_MSForEachDB @command
SET @xml = CAST(( SELECT DB.ServerName As 'td',' ',DB.DatabaseName AS 'td' ,' ',DB.PhysicalFileName as 'td' ,' ',DB.FileSizeMB as 'td',' ',Db.SpaceUsedMB as 'td',
' ',DB.FreeSpaceMB as 'td' ,' ',DB.FreeSpacePct as 'td',' ', convert(varchar(20), DB.Capture_Date,120) as 'td' from @DBSpaceInfo DB
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Database Growth status </H3>
<table border = 1>
<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th>
<td bgcolor=#F0E68C><b>FreeSpace(%)<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 + ': Database Growth status '
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 = 'sqldbanow@gmail.com', -- replace with your email address
@subject = @cmd
End
GO