x

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

Amardeep gravatar image

Amardeep
1.3k 87 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, 2012 at 12:10 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 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
GO

CREATE PROC sp_track_db_growth
(
@dbnameParam sysname = NULL
)
AS
BEGIN

/***********************************************************************************************************
                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 (%)]
FROM
(
        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]

END

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

more ▼

answered Jul 20, 2012 at 01:07 PM

basit 1 gravatar image

basit 1
499 52 64 85

(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

denisrichi gravatar image

denisrichi
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1853
x8
x6

asked: Jul 18, 2012 at 11:34 AM

Seen: 1481 times

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