Database space management and future capacity growth analysis?

I do not have any input on database future growth from business side so want to design standard approach for space management and database future capacity growth settings.

I could get history information whatever available on SQL database apart from that I do not have any source to design future capacity growth of database so anyone could help me to make a standard approach to set data file size, how much free space need to keep, auto grow and restriction. I also want to know best size figure for setting alerts for proactive action for increasing the database size.

Please let me know for more details…….

more ▼

asked Jul 18, 2012 at 11:34 AM in Default

avatar image

1.4k 90 93 97

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The only mechanisms I know for making this type of estimate is to evaluate data over time. You want to be able to track the size of your databases, yes, but also want to track the number of users in the system and the transaction count. Correlating all these together will give your best extrapolation to say that data growth will be X amount for Y amount of users performing Z level of transactions. Modifying any one of those numbers will change the output for your estimations.

more ▼

answered Jul 18, 2012 at 12:10 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left

You can use the below Query to get the value from the backupsize of your database that how much the database has grownup after the last backup.

     USE master
 CREATE PROC sp_track_db_growth
 @dbnameParam sysname = NULL
                 To see the file growth information of the current database:
                EXEC sp_track_db_growth
                Example 2:
                To see the file growth information for pubs database:
                EXEC sp_track_db_growth 'pubs'
 DECLARE @dbname sysname
 /* Work with current database if a database name is not specified */
 SET @dbname = COALESCE(@dbnameParam, DB_NAME())
 SELECT  CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
         CONVERT(char, backup_start_date, 108) AS [Time],
         @dbname AS [Database Name], [filegroup_name] AS [Filegroup Name], logical_name AS [Logical Filename], 
         physical_name AS [Physical Filename], CONVERT(numeric(9,2),file_size/1048576) AS [File Size (MB)],
         Growth AS [Growth Percentage (%)]
         SELECT  b.backup_start_date, a.backup_set_id, a.file_size, a.logical_name, a.[filegroup_name], a.physical_name,
                        SELECT  CONVERT(numeric(10,2),((a.file_size * 100.00)/i1.file_size)-100)
                        FROM    msdb.dbo.backupfile i1
                        WHERE   i1.backup_set_id = 
                                                      SELECT  MAX(i2.backup_set_id) 
                                                      FROM    msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
                                                              ON i2.backup_set_id = i3.backup_set_id
                                                      WHERE   i2.backup_set_id < a.backup_set_id AND 
                                                              i2.file_type='D' AND
                                                              i3.database_name = @dbname AND
                                                              i2.logical_name = a.logical_name AND
                                                              i2.logical_name = i1.logical_name AND
                                                              i3.type = 'D'
                                               ) AND
                                i1.file_type = 'D' 
                ) AS Growth
         FROM    msdb.dbo.backupfile a JOIN msdb.dbo.backupset b 
                ON a.backup_set_id = b.backup_set_id
         WHERE   b.database_name = @dbname AND
                a.file_type = 'D' AND
                b.type = 'D'
 ) as Derived
 WHERE (Growth <> 0.0) OR (Growth IS NULL)
 ORDER BY logical_name, [Date]

This SP will calculate the size of database from last backup to current one.

more ▼

answered Jul 20, 2012 at 01:07 PM

avatar image

basit 1
509 57 65 91

(comments are locked)
10|1200 characters needed characters left

The long-running operations are caused by locking and by the underlying physical sub-system. You must resolve this conflict to achieve a scalable system. So forecasting of future growth of disk is necessary to avoid these type of problem this can be easily overcome by a third party tool as well

more ▼

answered Feb 13, 2013 at 11:57 AM

avatar image

160 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 18, 2012 at 11:34 AM

Seen: 1916 times

Last Updated: Feb 13, 2013 at 11:57 AM

Copyright 2018 Redgate Software. Privacy Policy