[posted and mailed, please reply in news]
Jesse Wolgamott (je************ *@gmail.com) writes:
I've seen plenty of posts regarding the estimation of table size,
usually in the processing of planning for server storage needs.
Well, I've got a different problem. I need to know how much data each
of our Customers are using in a Database. (1 SQL DB stores multiple
customers).
Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
655 MB.
So, how can I ask SQL Server how much data each Row in each table is
taking up? I want to be able to calculate nightly the total size, so I
would take each row in each table that belong to the customer, and add
all the sizes together. I want to take into account blobs that are
storing images and PDF files also.
This is not a problem with a clearcut answer, since if a table gets
fragmented, that extra space is not really any particular customer,
but presumably someone should be accounted for it.
Therefore I would for the non-blob data take the number of rows for
each customer and scale that with the number of rows for each customer.
For the blob data I would simply sum the sizes of each field:
SELECT t.CustomerID,
MB = ((1E0 * COUNT(*) * i.reserved / i.rows) * 8192 +
SUM(coalesce(da talength(t.blob col), 0)) / 1E6
FROM tbl t
CROSS JOIN sysindexes i
WHERE i.id = object_id('tbl' )
AND i.indid IN (0, 1)
GROUP BY t.CustomerID
You should run DBCC UPDATEUSAGE on the database prior to running this-
The result will be skewed if you have tables with long non-blob
character/binary columns where different customer have very differing
average length on these columns. Also, if you are using the "text in row",
customers with small blobs may be accounted twice for the same thing.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
