asked
Calculating Database size?
I want to calculate or estimate the database size for each month of the previous year, and in turn use the monthly calculation to get the database size.(this value does not have to be exact but can be a estimate). The tables within the database have a created date field which i know a datepart function can be used to get the month each row was inserted. How can i achieve the calculating/ estimating database size based on calculating the row size? Any one with any script of a similar nature? beer in mind tables might have datatypes of unique character. i am using sql server.
I am not a DBA by trade, but I am fairly certain it doesn't work like that. For one, your created date is handy to calculate the following: "How many of the current records in the database were created between X and Y dates?", but unless nothing is ever truncated, deleted or dropped, you can't be sure that it is a full picture of what was there at that point in time. In addition, there are log files that track what has happened that aren't visible from the front end, Tempdb memory allocation, space used for indexes, and a whole host of other things I'm sure. That said, if you just want a bad guess at how many records were created in what timeframe, then something like this might help:
https://www.sqlservercentral.com/Forums/Topic271576-5-1.aspx Edit: I forgot to mention that in any of the solutions presented there, you'd want to calculate an interval off of your created date (the first day of the month of the created date, or the quarter, or whatever period of time you want to chunk your results by) then group by that in order to see the change
An alternative would be to look at the amount of data being backed up. Assuming you have been doing backups, of course... The information you want can be found in the [backupset][1] table in the msdb database: SELECT database_name, backup_start_date, backup_size, compressed_backup_size FROM msdb.dbo.backupset; [1]:
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql
18 People are following this question.
