Capacity Management Script

Hi All,
As most of our fellow DBA's suffers lesser disk space issues and when they ask there Business Unites to add more space they ask to provide a previous utilisation pattern. In order to do this here by I am posting a script which will run on a specific server and capture database space information and later we can check the summary.

This script has 2 main procedures.
1) DBGrowthTableProcedure
2) DBGROWTHANALYSIS

DBGrowthTableProcedure -- This procedure creates table and a job which is scheduled to run every day (we can schedule it as per our need) and stored database space related information in a table.

DBGROWTHANALYSIS -- We need to pass Database Name, From Date & To Date in order to run this procedure. Once executed this procedure will show us space utilisation for said database between said dates.

/* Step 1: creating the table to capture the Event information */
USE Master
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[MASTER].[DBO].[DBGrowthTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[DBGrowthTable]

GO

Create Table MASTER.DBO.DBGrowthTable(TableEntryID int identity(1,1) primary key, EntryDate smalldatetime not null default getdate(), Servername Varchar(100), DBNAME Varchar(100), DBsizeMB bigint, unallocMB bigint, reservedMB bigint, dataMB bigint, indexMB bigint, unusedDB bigint)

GO

CREATE INDEX [DBGrowthTable_IDX01] ON [dbo].[DBGrowthTable]([EntryDate]) WITH FILLFACTOR = 100

GO

/*Step 2 : Creating the Procedure that will capture Database Space Utilisation*/

USE [Master]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID(N'[MASTER].[DBO].[DBGrowthTableProcedure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE DBO.DBGrowthTableProcedure

GO

Create Procedure DBO.DBGrowthTableProcedure as

DECLARE DBCURSOR CURSOR FOR SELECT NAME FROM SYS.DATABASESOPEN DBCURSOR;
DECLARE @dbnames varchar(100), @sql nvarchar(4000), @sql1 nvarchar(4000), @sql2 nvarchar(4000), @sql3 nvarchar(4000);
FETCH NEXT FROM DBCURSOR into @dbnamesWHILE (@@FETCH_STATUS<>-1)BEGINset @sql='USE '+@dbnames+';
DECLARE @dbsize bigint, @dbname varchar(100), @logsize bigint, @reservedpages bigint, @usedpages bigint, @pages bigint; set @dbname=DB_NAME();
select @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end)), @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles; select @reservedpages = sum(a.total_pages), @usedpages =sum(a.used_pages), @pages = sum( case when it.internal_type IN (202, 204) 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 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;
Insert into MASTER.DBO.DBGrowthTable( ServerName, DBNAME, DBsizeMB, unallocMB, reservedMB, dataMB, indexMB, unusedDB)select @@servername,@dbname, cast(((@dbsize + @logsize) * 8192/1048576.) as decimal(15, 2)) "DB Size(MB)", (case when @dbsize >= @reservedpages then cast(((@dbsize - @reservedpages) * 8192/1048567.) as decimal(15, 2)) else 0 end) "Unalloc. Space(MB)", cast((@reservedpages * 8192/1048576.) as decimal(15, 2)) "Reserved(MB)", cast((@pages * 8192/1048576.) as decimal(15, 2)) "Data Used(MB)", cast(((@usedpages - @pages) * 8192/1048576.) as decimal(15, 2)) "Index Used(MB)", cast(((@reservedpages - @usedpages) * 8192/1048576.) as decimal(15, 2)) "Unused(MB)"'
Exec (@sql)
FETCH NEXT FROM DBCURSOR INTO @dbnames;
END;
CLOSE DBCURSOR;
DEALLOCATE DBCURSOR;
Go

/*Step 3: Create SQL Agent Job Which Will run every day and collect SQL Databases Information */

--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''
BEGIN TRANSACTIONDECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
DECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Database Space Utilization Daily', @enabled=1, @notify_level_eventlog=2, @notify_level_email=3, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Job for capturing Database Space utilization daily',@category_name=N'[Uncategorized (Local)]', --@owner_login_name=@ServiceAccount, --@notify_email_operator_name=@SQLOperator, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 4: Adding Steps To The Job */

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute Procedure to capture Database Growth',@step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC DBGrowthTableProcedure', @database_name=N'master', @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO
EndSave
QuitWithRollback:IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO

/*Step 5: Create procedure to print space information for a said database */

CREATE PROCEDURE DBGROWTHANALYSIS (@DATABASENAME VARCHAR(100),@MONTH_FROM SMALLDATETIME,@MONTH_TO SMALLDATETIME) AS
--DECLARE @DATABASENAME VARCHAR(100)
DECLARE @DBID INT
--DECLARE @MONTH_FROM SMALLDATETIME--DECLARE @MONTH_TO SMALLDATETIME
DECLARE @MAX_MONTH SMALLDATETIME
DECLARE @MIN_MONTH SMALLDATETIME
DECLARE @GROWTH INT
DECLARE @RowCtr INT
DECLARE @NoOfRows INT
DECLARE @GROWTH1 INT
--SET @DATABASENAME='SQLNEXUS'
--SET @MONTH_FROM='03-JAN-2009'
--SET @MONTH_TO='06-May-2010'
SET @DBID = DB_ID(@DATABASENAME)
IF @DBID IS NULL
BEGIN PRINT 'Database ' +@DATABASENAME +' Does not exists in current server'
END
ELSE
IF @MONTH_FROM IS NULL
BEGIN
SET @MONTH_FROM=(SELECT MIN(ENTRYDATE) FROM DBGROWTHTABLE WHERE DBNAME=@DATABASENAME);
END
ELSE
SELECT @MONTH_FROM
IF @MONTH_TO IS NULL
BEGIN SET @MONTH_TO=(SELECT MAX(ENTRYDATE) FROM DBGROWTHTABLE WHERE DBNAME=@DATABASENAME);
END
ELSE
SELECT @MONTH_TOIF @MONTH_FROM >@MONTH_TO
BEGIN
PRINT 'Start Date '+CAST(@MONTH_FROM as varchar(50))+' Cannot be more then End Month '+CAST(@MONTH_TO as varchar(50))
END
ELSE
SELECT @MIN_MONTH=MIN(EntryDate), @MAX_MONTH=MAX(EntryDate2) FROM DBO.DBGrowthTableWHERE DBNAME=@DATABASENAME
IF @MIN_MONTH>@MONTH_TO BEGIN PRINT 'Provided Range For Backup is invalid. Actual Backup For '+@DATABASENAME +' is between '+CAST(@MIN_MONTH as varchar(50)) +' '+CAST(@MAX_MONTH as varchar(50))
END
IF @MAX_MONTH<@MONTH_TOBEGIN SET @MONTH_TO=@MAX_MONTHEND
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DBO].[#DBROWS]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE DBO.#DBROWS ( RowID Int Identity, DBGrowthSize Int, DBUNALLOCSIZE Int, DBName Varchar(200), MonthYear Varchar(100), DBGrowth Int, DBUAGrowth Int )
Insert Into DBO.#DbRows (DBGrowthSize, DBUNALLOCSIZE,DBName, MonthYear) SELECT SUM(DBSIZEMB), SUM(UNALLOCMB), DBNAME, cast(DATEPART(mm,ENTRYDATE) as varchar(4))+'-'+cast(DATEPART(yy,ENTRYDATE)as varchar(4)) FROM DBO.DBGrowthTable WHERE DBNAME=@DATABASENAME AND ENTRYDATE >= @MONTH_FROM AND ENTRYDATE <=@MONTH_TO GROUP BY DBNAME,cast(DATEPART(mm,ENTRYDATE)as varchar(4))+'-'+cast(DATEPART(yy,ENTRYDATE)as varchar(4)) ORDER BY cast(DATEPART(mm,ENTRYDATE) as varchar(4))+'-'+cast(DATEPART(yy,ENTRYDATE)as varchar(4))

Select @NoOfRows = count(*) from DBO.#DBROWS
SET @RowCtr = 1
SET @GROWTH1 = 0
SET @GROWTH=0
WHILE @RowCtr <= @NoOfRows
BEGIN
UPDATE DBO.#DBROWS SET DBGrowth = CASE WHEN @RowCtr = 1 THEN 0 ELSE DBGrowthSize - @GROWTH1 END WHERE RowID = @RowCtr

UPDATE DBO.#DBROWS SET DBUAGrowth = CASE WHEN @RowCtr = 1 THEN 0 ELSE DBUNALLOCSize - @GROWTH END WHERE RowID = @RowCtr

SELECT @GROWTH1 = DBGrowthSize FROM DBO.#DBROWS WHERE RowID = @RowCtr
SELECT @GROWTH = DBUNALLOCSize FROM DBO.#DBROWS WHERE RowID = @RowCtr
SET @RowCtr = @RowCtr + 1 END
SELECT ROWID,DBGrowthSize as "Database Size (in MB)",DBNAME as "Database Name", MonthYear as "Month-Year", DBGrowth as "Database Growth In MB",DBUAGROWTH as "Database Unallocated Space In MB"
FROM DBO.#DBROWS
DROP TABLE DBO.#DBROWS

Regards
Gursethi