August 16, 2007 at 2:00 pm
Take the row size for each table, as demonstrated in the other thread you opened on this subject, and multiply by the number of rows for that table. Sum up everything in the end. Keep in mind that this is going to be a VERY VERY rough estimate, unless all of your columns are fixed width (it will still not be exact, but closer), and not really that useful for determining space usage, needs, etc.
August 16, 2007 at 2:10 pm
An INTEGER is 4 bytes. A smalldatetime is also 4 bytes.
There is no change in storage if you decide to convert INT to SMALLDATETIME.
exec sp_MSforeachtable 'select count(*) as recs, ''?'' as table_name from ?
where date BETWEEN -1463 AND -1100'
N 56°04'39.16"
E 12°55'05.25"
August 16, 2007 at 2:20 pm
can i just have the row size and multiply by no.of rows or shud i also count no.of bytes assigned for each column in a table.
August 17, 2007 at 10:02 am
jp - it seems to me that you are trying to estimate the amount of space that will be 'freed up' by archiving all the database content for the year 1995.
it also seems to me that you are approaching this question as a one-time shot. DON'T BELIEVE ANYONE WHO TELLS YOU THAT!
You should do the table analysis for ALL existing years in the database.
On a more fundamental level - what are the archival policies in place for the entity using this database? There should be business rules established to govern the exercise. Either the business needs the information or not. Packrats are not allowed to vote in this process - there are regulatory implications (just because nobody knows what they are doesn't mean they don't exist) - always there's regulatory implications (e.g., Sarbanes-Oxley), internal auditors, external auditors, etc etc etc.
you're just the tail being wagged by the dog. What you will ultimately need is a rule that file backups will be the ultimate repository where the content is kept, or there will be an archival database mirroring the current database where this information will be kept.
Trying to estimate space in advance of moving the content will take almost as long as building an archival database of only 1995 data. And, the space used in building the 1995 archival image will give a more accurate estimate than what you are trying to calculate now.
Happy Friday, and have a wonderful weekend. Get lots of rest. You'll be glad next week that you did.
August 17, 2007 at 11:08 am
I am not sured If i followed you..
does it work if am comapring the backup of database for 1995 and 1996 to estimate the growth.
like comparing backup of 12/31/1995 with 12/31/1996
August 17, 2007 at 9:39 pm
A count of rows from all the tables in a database for the year 1995...
Why?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply