April 14, 2015 at 11:13 am
Hi Everyone,
I got into confusion while working on DB Growth report for 2012 databases which has compression on.
I am analyzing the DB Growth based on MSDB..dbo.backupset table which stores the backup information.
But here it gets tricky, In Previous versions we use "backup_size" column to get actual backup size and estimate the db growth based on the previous all backup file info. But now since compression is on in 2012 the "backup_size" colmuns gives a compressed file size(If i am right) so how do you know the actual backup size to estimate the db growth over a period of time??
April 14, 2015 at 12:19 pm
I wouldn't use the backups to estimate database size. Instead I'd monitor the size and growth the database itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2015 at 12:41 pm
I do the same thing once a quarter for my business. I use the following script in my environments to monitor file size. I took this basic shell and had it insert into a table in my DBA database. I then have it send me an email if a file gets 95% full so I can know if an autogrow event is around the corner so I can know what my DBs are doing and proactively size my DBs.
Since it's all logged to a table, I can query it every quarter, plug the numbers into an Excel spreadsheet I have and it will do rough estimates on DB size and growth:
SELECT [DateTimeChecked] = CURRENT_TIMESTAMP
,[Server] = CAST(SERVERPROPERTY('servername') AS CHAR(20))
,[Segment Name] = RTRIM(NAME)
,[Group Id] = groupid
,[File Name] = filename
,[Size in MB] = CAST(size / 128.0 AS DECIMAL(10, 2))
,[Space Used in MB] = CAST(FILEPROPERTY(NAME, 'SpaceUsed') / 128.0 AS DECIMAL(10, 2))
,[Available Space in MB] = CAST(size / 128.0 - (FILEPROPERTY(NAME, 'SpaceUsed') / 128.0) AS DECIMAL(10, 2))
,[Percent Used] = CAST((CAST(FILEPROPERTY(NAME, 'SpaceUsed') / 128.0 AS DECIMAL(10, 2)) / CAST(size / 128.0 AS DECIMAL(10, 2))) * 100 AS DECIMAL(10, 2))
FROM sysfiles
ORDER BY groupid DESC
April 14, 2015 at 12:42 pm
Hi,
It depends on the purpose what you use the information for.
I want to track my database backup file growth based on the backup information collected..
April 14, 2015 at 12:44 pm
CANCER (4/14/2015)
Hi,It depends on the purpose what you use the information for.
I want to track my database backup file growth based on the backup information collected..
As Grant pointed out, you don't want to use your DB backup size as an indicator of how big your DBs are, especially now that you have compression. It simply won't be accurate and you'd be just guessing. My method above allows you to be precise and accurate and can give you the flexibility of looking at what size the DBs were in the past.
April 14, 2015 at 12:49 pm
Mind you, tracking backup size is important too, but for managing the backups and their drives, not for maintaining the database size tracking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2015 at 1:11 pm
Thanks Grant..
Then what is the common process for database size tracking?
We do have a monitor tool which collect file utilization based on each file.
But i want to make one consolidate report and show it as graph to business.
April 14, 2015 at 4:55 pm
What you're doing, the file utilization. Although, that's the size of the files, not the size of the data. A simple way to get a view of this is to use this on a given database.
EXEC sys.sp_spaceused;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply