sp_spaceused

I wud like to know how i can change sp_spaceused store proc for
getting data for the table where datepart(yyyy,convert(char(10),cast(date - 36161 as datetime),101))=1995

The sp_spaceused proc is in the master database. You should be able to modify it if you have the appropriate permissions. I would not suggest that you do that though. Copy the details of this proc into your own instead and use that.

Note: changed my question above please read again, sorry for that
yeah I wud like to create identical store proc and change it.

Unfortunately you won't be able to do that with modifying sp_spaceused.

so what wud be your suggestion for this. I hope u got wht am trying to do.
space used for the data in the year 1995

Yip, got it []
It's very difficult to get a 100% accurate figure for this, especially because of varchar datatypes that do not always use all the allocated space. My suggestion would be to get an estimate amount of bytes used per record (either from the combined potential sizes of all your fields or from the actual size of the table). Then you can multiply this value with the amount of records you have for 1995.
Hope this helps.

thats where my calculations are going wrong...
I calculated row length of each table and multiplied by the no .of rows but tht doesnt seem to be good result i have.

You will have to do this for each table individually if you want the most accurate answer. Remember that calculating it in this way will not take indexes into consideration, and that might be a reason for your figure looking very low.
Post your calculation, maybe we can spot the problem.

I calculatedrow length for each individual table and summed up which came to 87940 and mutiplied that with row count for the enitre Db and size toooo much than what iwas expecting

Ok, lets try this:
Say you have a table called Table1. Run sp_spaceused 'Table1', which will give you space used for data and indexes as well as the record count. If the record count = 330147 and space used = 49320 KB (data) + 272 KB (index) then your estimate size per row = (49320 + 272) / 330147
That gives you 0.15 KB per record. Now run a query on that table to return a record count for 1995 and you get 55000, then your space used (in this table) for 1995 is 0.15 * 55000 = 8250 KB.
I hope you are doing something similar to this. You will have to do it like this for every table separately if you want fairly accurate results.

This looks ok but am not sure if this is the exact number.
for the year 1995
Total space used = 141656
Total RecordCount = 277121
RecordCount for 1995 = 5046
so totallu space used for the year 1995 came up to 2.5MB

Reddy, your calculation as described above is as close as you are going to get. This will change over time, especially if you change the table definition. If I were you I would stick with what you have as probably an 80% accurate estimate. Do this calculation periodically to see if there is an upward or downward trend.
I don't think there is a way that you will get a more accurate figure though...

Maybe to give you an idea what we do...
I have a 1.2 TB datawarehouse (growing by 1 GB every day) and we do daily loads. I track the total space used in my databases and save the results to a table after each load. In doing that it is easy to see the trend and get an average growth for a period.
Hope this helps.

[quote user="Reddy"]
thats where my calculations are going wrong...
I calculated row length of each table and multiplied by the no .of rows but tht doesnt seem to be good result i have.
[/quote]
And I had warned you about that already: http://sql-server-performance.com/Community/forums/t/22489.aspx []

ndinakar..
do u want to share anything or shed some light on what martin has advised me to do in this post?

My question would be how will that information be of any use? You cannot accurately find the table size going back to history. You can get an approximate size but it may not be accurate. You can measure the % of growth today and see how much it is next week or next month and project the growth rate. You can also measure by number of rows as"We are growing by 1 million rows every month" or "we are growing by 5 GB every month".

you are right dinakar, thts exactly what i need growth for each year. i do not need any accurate calculation, just need an approx growth for each year.
So do you think am doing the rgith thing ?