Diskspace analysis, database growth (yearly)

I am using the following  query to forecast the yearly database growth


select cast(MAX(Space_Used_in_MB)as float) as max_size , min(Space_Used_in_MB) AS MIN_SIZE ,

cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 as [yearly percent growth] ,

Database_Name from SQLMonitor..SQLAdmin_Monitor_db_file_info

where FILE_ID = 1

and Time_collected >='01/13/2014'

group by Database_Name

order by database_name


--SQLMonitor is a user database
--SQLAdmin_Monitor_db_file_info  is a user table where a job ( code here) logs the database file sixe in every four hours

-- if the time frame you want to measure is two years change the time_collected to '01/13/2013'  and change the yearly percent growth calculation as follows

( cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 )/2

Growth of database ( http://www.sqlservercentral.com/articles/Administration/analyzingdiskcapacityforsqlserver/2467/)

Database growth can be of three types:

1. linear growth (grows at a constant amount over time)
2. compound growth (grows at a constant rate for a specific period of time)
3. geometric growth (grows periodic by some increment, which is in turn growing at a constant rate)

Below are the formulae for different type of growths:
Linear: Current disk space + (growth * No of periods)
Compound: Current disk space * (1 + Growth %) ^ No of periods
Geometric: Current growth + [initial increment * {1-Incrementgrowth rate^ (No of period+1)}]/ (1 – growth rate)
To illustrate the fact, let us take one example. Say for, you have a database and it is of 100 GB. If it grows at 10% per year, then after 2 years, it will be 100 + (10 * 2) = 120 GB (linear growth).
If it is so that the database grows at 5% rate/month for 2 years, then it will be
100 * (1 + 0.05) ^ 24 = 322.5 GB (compound growth).
Take another case. Say for you have already a database of 100GB. Now the growth rate is so that it starts at 10 GB/month and the increment itself increments at 5%/month, then in 24 months, it will be 100 + {(10 * (1- (1.05 ^ (24 + 1))) / (1-1.05)} = 100 + 477 = 577 GB (geometric growth).

Modifying database file

All the database files are set to autogrowth to 10% from the initial sizes. But,the auto growth can happen any time during business hours causing the database slow down. Therefore I am taking the following approach -

1 I have set up a sql job ( calls a script listed here) that logs the current datafile and the log file size in a table in interval of 4 hours daily. I use that historical data to research when the data file needs to grow.
2. Then I run this script from the table to find which database file has more than 95% filled up and those are the candidate for me to manually resize ( expand) the data file.
3. I use the follwing script to resize the datafile
use db1
Go

---select * from sys.sysfiles

alter database client_343
modify file
(
name = client_343,
size = 1024MB
);

Script: To log the space of the database

As a DBA one of my primary job is to keep track of the growth of the database so that I can forecast the space required. I have a static table where I run the following script and collect the data in about in 4 hours and analyze the data collected in the table.
I have scehduled this script as a sql job that runs in every 4 hours. Every Friday I look at the historic data and see by how large our databases grew.


DECLARE
DECLARE
DECLARE @rowcnt INT @iterator INT @dbname VARCHAR(200)DECLARE @exec_sql VARCHAR(500)SET @rowcnt = 0SET

 CREATE TABLE #db_file_info ([Database_Name] SYSNAME NOT NULL,[File_ID] SMALLINT NOT NULL,[File_Type] VARCHAR(10) NOT NULL,[File_Name] SYSNAME NOT NULL,[File_Path] VARCHAR(500) NOT NULL,[File_Size_in_MB] INT NOT NULL,[Space_Used_in_MB] INT NOT NULL,[Space_Left_in_MB] INT NOT NULL,[Time_collected] smalldatetime )
CREATE TABLE #db (dbid INT,name VARCHAR(200))INSERT INTO #dbSELECT dbid,nameFROM MASTER.dbo.sysdatabasesSET @rowcnt = @@ROWCOUNTWHILE@iterator <= @rowcntBEGINSELECT @dbname = '['+ name + ']'



Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,
name,filename,
[file_size] =
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)),
getdate()
from
dbo.sysfiles;
'FROM #dbWHERE dbid = @iteratorSET @exec_sql = ' USE ' + @dbname + '; Insert into #DB_FILE_INFO
EXEC( @exec_sql)
SET @iterator = @iterator + 1END
INSERT INTO SQLAdmin_Monitor_db_file_infoSELECT *FROM #db_file_infoDROP TABLE #dbDROP



/*SQLAdmin_Monitor_db_file_info is the static table where I collect the results returned by the above script. */TABLE #db_file_info @iterator = 1

Script: Find the tables and thier sizes in a database

Use the following script to  find out the size of different table in the database. If your database grows big suddenlt the following script comes very handy to find out what is causing the sudden growth.

use [yourdatabase]
GO

SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8 ) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
FROM sys.indexes i
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
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO