The SQL to enter in the “Execute SQL Task”
if not exists(select * from master..sysdatabases where name=’dbamaint’)
create database dbamaint
go
alter database dbamaint
set RECOVERY SIMPLE
go
use dbamaint
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_filespacestats]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[sp_filespacestats]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
–if not exists (select * from dbamaint.dbo.sysobjects where name = ‘sp_filespacestats’ and type=’P’)
create procedure sp_filespacestats
as
Begin
declare @dbname sysname
if not exists (select * from dbamaint.dbo.sysobjects where name = ‘filespacestats’ and type=’U’)
begin
CREATE TABLE dbamaint.[dbo].[filespacestats] (
[server_name] [sysname] NOT NULL ,
[dbname] [sysname] NOT NULL ,
[flag] [bit] NULL ,
[Fileid] [tinyint] NULL ,
[FileGroup] [tinyint] NULL ,
[total_space] [decimal](20, 1) NULL ,
[usedspace] [decimal](20, 1) NULL ,
[freespace] [decimal](20, 1) NULL ,
[freepct] [decimal](20, 1) NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileName] [sysname] NULL ,
[report_date] [datetime] default getdate()
) ON [PRIMARY]
end
delete dbamaint.[dbo].[filespacestats]
declare cur_dbname cursor for
select name from master..sysdatabases
open cur_dbname
fetch next from cur_dbname into @dbname
while @@fetch_status=0
begin
if exists (select * from tempdb..sysobjects where name like ‘#datafilestats%’)
drop table #datafilestats
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (20, 1),
UsedExtents dec (20, 1),
[Name] varchar(50),
[FileName] sysname )
declare @string sysname
set @string = ‘use [‘ + @dbname + ‘] DBCC SHOWFILESTATS with no_infomsgs’
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents
, [Name] , [FileName])
exec (@string)
insert dbamaint.dbo.filespacestats (server_name,dbname, [flag],Fileid,FileGroup,total_space,usedspace,freespace,freepct,Name,FileName)
select @@servername,@dbname,flag,Fileid,FileGroup,(TotalExtents*64/1024),(UsedExtents*64/1024),
((TotalExtents*64/1024)-(UsedExtents*64/1024)),((TotalExtents*64/1024)-(UsedExtents*64/1024))*100/(TotalExtents*64/1024),Name,FileName from #datafilestats
fetch next from cur_dbname into @dbname
end
CLOSE cur_dbname
DEALLOCATE cur_dbname
if exists (select * from tempdb..sysobjects where name like ‘#logspacestats%’)
drop table #logspacestats
create table #logspacestats
( dbname varchar(25),
flag bit default 1,
Totallogspace dec (20, 1),
usedlogspace dec (20, 1),
status char(1)
)
insert #logspacestats (dbname,Totallogspace,usedlogspace,status)
exec (‘dbcc sqlperf(logspace) with no_infomsgs’)
–dbcc sqlperf(logspace) with no_infomsgs
insert dbamaint.dbo.filespacestats (server_name,dbname,flag,total_space,freepct,Name)
select @@servername,dbname,flag,Totallogspace,(100-usedlogspace),’logspace_for_db’ from #logspacestats
select * from dbamaint.dbo.filespacestats order by flag ,report_date desc
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec dbamaint.dbo.sp_filespacestats
The text of Db_space_monitor.Bat
@echo OFF
@if not “%ECHO%”==”” echo %ECHO%
SET LANG=en_US.iso88591
rem *******************************************
rem Script : space_monitor.bat
rem Author : Neeraj Nagpal
rem Date : 09/26/2005
rem The script is used to monitor the space on all the DB servers.
rem It will send an email for database with less than 10% free space
rem *******************************************
rem Set Local scope and call main proc.
setlocal & pushd & Set RET=
set SCRIPTNAME=%~n0
set SCRIPTPATH=%~f0
set DBASQL=E:dbaSQL
set DBAOUT=E:DBAout
set DEBUG=1
if “%DEBUG%”==”1” (set TRACE=echo) else (set TRACE=rem)
if /i {%1}=={/Help} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/?} (call :HELP %2) & (goto :HELPEXIT)
call :MAIN %*
:HELPEXIT
popd & endlocal & set RET=%RET%
goto :EOF
rem ********************************************
rem Help Information Follows
:HELP
if defined TRACE %TRACE% [proc %0 %*]
echo The script is used to monitor the space on all the DB servers.
echo Usage : Double click
goto :EOF
rem *******************************************
rem Main Procedure will follow
:MAIN
if defined TRACE %TRACE% [proc %0 %*]
REM ************** Step 0 *** Delete the data in the table for current date ***************************
isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -w 3000 -i%DBASQL%bef_space_mon.sql > %DBAOUT%bef_space_mon.out
REM **************Step 1 *** Populate the exception report table ***************************
echo “*******Date=%date%::%time% || Start of the DTS step ************” >%DBAOUT%db_space_monitor.log
dtsrun /S SQLSRVCNTREP /N space_mon /E >>%DBAOUT%db_space_monitor.log
set err=%ERRORLEVEL%
echo Return code is %err%
echo “Return Code = %err%”>>%DBAOUT%db_space_monitor.log
echo “End of the DTS step” >>%DBAOUT%db_space_monitor.log
REM **************Step 2 *** Run the sql to get the Space Monitoring report ***************************
echo “*******Date=%date%::%time% || Start of the Query execution step ************” >>%DBAOUT%db_space_monitor.log
isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -w 3000 -i%DBASQL%db_space_monitor.sql > %DBAOUT%db_space_monitor.out
echo “*******Date=%date%::%time% || Sending exception report in email ************” >>%DBAOUT%db_space_monitor.log
blat %DBAOUT%db_space_monitor.out -t infy_dba@paramount.com,roberto_james@paramount.com,tammy_rogers@paramount.com -s “Space Monitoring Report” -server timeline -f neeraj_nagpal@paramount.com
The text of db_space_monitor.sql
set nocount on
use dbamaint
go
select getdate() as ‘Report Generation Date’
go
print ‘The following SQL Server databases have less than 10% free data space’
print ‘—————————————————————————-‘
print ”
print ”
select substring(server_name,1,15) as ‘Server Name’ ,substring(dbname,1,15) as ‘Database’,convert(varchar(12),sum(total_space)) ‘Total space(MB)’,convert(varchar(12),sum(freespace)) ‘Free space(MB)’ ,
convert(varchar(8),(sum(freespace)*100)/sum(total_space)) ‘% Free space’ from db_all_spacestats
where flag=0 and datediff(dd,report_date,getdate())=0
and dbname not in (‘model’,’master’,’msdb’,’tempdb’)
group by report_date,server_name,dbname
having (sum(freespace)*100)/sum(total_space)<10
order by server_name, “% Free space” ,report_date desc
go
print ”
The text of bef_space_mon.sql
use dbamaint
go
print ‘Deleting all the space monitoring data from same date’
go
delete db_all_spacestats where datediff(dd,report_date,getdate())=0
and datediff(mm,report_date,getdate())=0
and datediff(yy,report_date,getdate())=0
go
The query to get the growth rate statistics
select * from vw_sql_growth_mnth_qtr order by Used_growth_mb desc
Create view script of view vw_sql_growth_mnth_qtr
create view vw_sql_growth_mnth_qtr
as
select Server_Name,”Database”,
convert(decimal(10,2),used_space_MB) -(select convert(decimal(10,2),used_space_MB) from vw_prm_dbspace_stats
where datepart(dd,report_date)=1
and datepart(mm,report_date)=case (datepart(mm,getdate()))
when 1 then 12
else (datepart(mm,getdate())-1)
end
and Server_Name=v.Server_Name
and “Database”=v.”Database”
) as “Used_growth_mb” ,
(convert(decimal(10,2),used_space_MB) -(select convert(decimal(10,2),used_space_MB) from vw_prm_dbspace_stats
where datepart(dd,report_date)=1
and datepart(mm,report_date)=case (datepart(mm,getdate()))
when 1 then 10
when 2 then 11
when 3 then 12
else (datepart(mm,getdate())-3)
end
and Server_Name=v.Server_Name
and “Database”=v.”Database”
))/3 as “Avg_per_month_grth_for_last_qtr” ,
convert(decimal(10,2),total_space_MB) -(select convert(decimal(10,2),total_space_MB) from vw_prm_dbspace_stats
where datepart(dd,report_date)=1
and datepart(mm,report_date)= case (datepart(mm,getdate()))
when 1 then 12
else (datepart(mm,getdate())-1)
end
and Server_Name=v.Server_Name
and “Database”=v.”Database”) as “Alloc_in_curr_month”,
Total_space_MB as “Total_alloc_spc”, Used_space_MB, Free_space_MB,report_date
from vw_prm_dbspace_stats v
where datepart(dd,report_date)=1
and datepart(mm,report_date)=datepart(mm,getdate())
Create view script of view vw_prm_dbspace_stats
create view vw_prm_dbspace_stats
as
select substring(server_name,1,15) as ‘Server_Name’ ,substring(dbname,1,15) as ‘Database’,
convert(varchar(12),sum(total_space)) as ‘Total_space_MB’,
convert(varchar(12),sum(usedspace)) as ‘Used_space_MB’,
convert(varchar(12),sum(freespace)) as ‘Free_space_MB’ ,report_date
from prm_all_spacestats
where flag=0
and dbname not in (‘model’,’master’,’msdb’,’tempdb’)
group by report_date,server_name,dbname