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 '12 at 11:34 AM in Default

Amardeep gravatar image

1.3k 84 88 89

(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 '12 at 12:10 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(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 '12 at 01:07 PM

basit 1 gravatar image

basit 1
449 49 61 81

(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 '13 at 11:57 AM

denisrichi gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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 '12 at 11:34 AM

Seen: 1196 times

Last Updated: Feb 13 '13 at 11:57 AM