Alert when New DB is created and send the available mount point storage details

Declare @New_DB_Count int

set @New_DB_Count = (select count (*) from sysdatabases
where CAST(crdate AS DATE) = (select CAST(GETDATE() AS DATE)))

If @New_DB_Count > 0
begin

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQL SERVER MAIL PROFILE NAME’, ——-Change mail profile name
@recipients = ‘TEAM DL or INDIVIDUAL MAIL ID’,———-Change
@subject = ‘New databases created on SERVER NAME. Please check available space.’, —–Change Server name
@query = ‘ set nocount on
SELECT CAST (Name as CHAR (45)) Name, CAST (physical_name AS CHAR (55)) Current_File_Location
FROM sys.master_files where Database_ID <> 2 and Database_ID in (select dbid from sysdatabases
where CAST(crdate AS DATE) = (select CAST(GETDATE() AS DATE)));
Print ” ”
Print ” ”
Print ”Please check theres enough space on the Mountpoints for them to grow. Thank You!!”
Print ” ”
Print ” ”

DECLARE @sqlver sql_variant
DECLARE @sqlver2 varchar(20)
DECLARE @sqlver3 int

SELECT @sqlver = SERVERPROPERTY(”productversion”)
SELECT @sqlver2 = CAST(@sqlver AS varchar(20))
select @sqlver3 = SUBSTRING(@sqlver2,1,1)

BEGIN

IF @sqlver3 = 1 GOTO SERVER2008

GOTO THEEND

END

SERVER2008:

declare @svrName varchar(255)
declare @sql varchar(400)

set @svrName = @@SERVERNAME

set @sql = ”powershell.exe -c “Get-WmiObject -Class Win32_Volume -Filter ””DriveType = 3”” | select name,capacity,freespace | foreach{$_.name+””|””+$_.capacity/1048576+””%””+$_.freespace/1048576+””*””}””
–creating a temporary table
CREATE TABLE #output
(line varchar(80))
–inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql

–script to retrieve the values in GB from PS Script output

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(”|”,line) -1))) as drive
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(”|”,line)+1,
(CHARINDEX(”%”,line) -1)-CHARINDEX(”|”,line)) )) as Float)/1024,0) as ”totalspace”
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(”%”,line)+1,
(CHARINDEX(”*”,line) -1)-CHARINDEX(”%”,line)) )) as Float) /1024 ,0)as ”freespace”
,((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(”%”,line)+1,
(CHARINDEX(”*”,line) -1)-CHARINDEX(”%”,line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(”|”,line)+1,
(CHARINDEX(”%”,line) -1)-CHARINDEX(”|”,line)) )) as Float)/1024,0)) * 100) as percentfree
from #output
where line like ”[A-Z][:]%”
order by drive

–script to drop the temporary table

drop table #output

THEEND:
‘

End