May 21, 2009 at 2:14 pm
Hi,
Without existence of permanent table(s) to hold historical stats for each db in an instance, is/are there system object(s) that can be queried to display historical stats (space usage/allocation) of databases within a SQL instance?
Many thanks in advance
May 21, 2009 at 3:41 pm
No - that kind of information is not stored or tracked by SQL Server. You have to build your own process to get that information to be able to trend.
One way of identifying space usage is to look at the backup sizes over time. The difference between the backup sizes should reflect the growth of the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2009 at 5:26 pm
Jeffrey is correct, it isn't stored.
the closest thing is to check the backup file sizes. There is a size stored in msdb.dbo.backupset, which gives you an "idea" of data size. Not database size.
May 21, 2009 at 5:41 pm
Thanks Steve - I should have been clearer on that.
FWIW - I use Powershell and SMO to connect to the SQL Server and pull the DataSpaceUsage and IndexSpaceUsage properties of each database on a daily basis. I store this in a central repository and use that data to trend the growth of the databases.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2009 at 5:58 pm
I've taken an opposite tact. Often I've just tracked the file sizes of backups.
Either way works, just be consistent.
May 22, 2009 at 3:06 am
Thank you much Jeff, Steve.
Your responses confirmed my suspicion.
Eventually built the query using combination of both the Profiler and the "Reports" menu on SSMS when right-clicking over a database.
Thanks & regards,
-Gary Sjah-
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply