0

I have a table the holds snapshots of data. These snapshots are all tagged with 'jan2010' or 'april2011'. All the snapshots will grow exponentially over time and I wanted to see if I could forecast when we'd need to upgrade our storage.

Is there any way to 

select monthlysnapshot, sum(size)
from tblclaims_liberty 
group by monthlysnapshot 
order by monthlysnapshot desc

What am I missing to get the size of the data returned? Is there a system function I can call?

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Adam McC
  • 213
  • 4
  • 18

3 Answers3

3

EXEC sp_spaceused 'tablename'

This will return a single result set that provides the following information:

Name - the name of the table

Rows - the number of rows in the table

Reserved - amount of total reserved space for the table

Data - amount of space used by the data for the table

Index_Size - amount of space used by the table's indexes

Unused - amount of usused space in the table

Kapil
  • 504
  • 3
  • 4
  • using this i calculated the space used by each snapshot and was able to forcast when i should think about expanding. thanks. :) – Adam McC Jul 06 '11 at 13:44
1

Is it what you are looking for?

C# Getting the size of the data returned from and SQL query

Changed:

EXEC sp_spaceused 'tablename'

If you can do this in your code then in C# (Change code to whatever lang you are using)

long size = 0;
object o = new object();
using (Stream s = new MemoryStream()) {
BinaryFormatter formatter = new BinaryFormatter();
formatter.Serialize(s, o);
size = s.Length;

Code copied from: How to get object size in memory?

Community
  • 1
  • 1
Kashif
  • 14,071
  • 18
  • 66
  • 98
  • 1
    I think the title and the final question are in contradiction to the other information supplied. It's more like the OP wants to know the size of certain data *stored* rather than *returned*. – Andriy M May 24 '11 at 09:35
  • that exactly my problem. all my data is in the one table. i want to know how much the data will frow for each snapshot. – Adam McC May 24 '11 at 12:32
  • @Adam McC can you do this in your code. If you are using C# then look at my Edit. – Kashif May 25 '11 at 12:12
0

I think you can Insert the selected data into a table via "select * into newtable from table" and get the size of that newly created table.

Vimal Kumar Prajapati
  • 1