asked
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…….
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.
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.
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
10 People are following this question.
