July 31, 2013 at 4:01 am
Hi,
Can anyone please provide me the query to find the how much disk space used in last year?
I need to provide approximate space for one more year as disk space for particular drive is about to full.
Any immediate assistance will be a great help.
July 31, 2013 at 5:57 am
The way I do it is to track the total and free space for each volume. I capture the data into a table daily. That allows me to see how much growth we've had even if the SAN volumes have been increased for any time period.
I don't know of any metrics that are maintained by SQL Server out of the box for a whole year for disk space.
July 31, 2013 at 6:53 am
If you don't have monitoring in place to track disk space and disk usage, there's no way to tell. Nothing within SQL Server will automatically remember that on Jan 1 you were using X amount of database space and now you're using X + Y amount. You need to set up monitoring yourself to track that information.
"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
July 31, 2013 at 7:45 am
I look at backup history to give me approximate database growth over time. Maybe you can adapt that to get what you need ? Backup size is usually smaller than live database size since backups don't include free space, but it might be "good enough". You can multiply the backup size by a "free space fudge factor" to guess at original DB size.
select substring(a.database_name,1,35) as 'Database',
datepart(year,a.backup_start_date) as 'year',
datepart(month,a.backup_start_date) as 'month' ,
avg(cast((a.backup_size /1073741824) as decimal (9,2)))as 'Avg Gig'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D'
and a.backup_start_date >= '2012-12-01'
GROUP BY a.database_name,datepart(year,a.backup_start_date),datepart(month,a.backup_start_date)
order by a.database_name,datepart(year,a.backup_start_date) desc,datepart(month,a.backup_start_date) desc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply