473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Calculating Row Size including blobs

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.

Thanks in advance,
Jesse Wolgamott
Jul 20 '05 #1
1 15739
[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(datalength(t.blobcol), 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Roy Danon | last post by:
Hi, How can I find out what is the size of the HTML page i've sent to the client's browser? (Without using the server logfiles) Roy.
2
by: Manish Pandit | last post by:
Hi, I have tried looking for a solution to this problem but no luck. The thing is, in my system, I have a set of users who have a 'quota' limiting their usage. They can create n number of tables...
2
by: Stanley Sinclair | last post by:
About to create a table which will "include" a BLOB. Am not sure how large to make the container and the tablespace. What I see says that BLOB is stored "separately." However, I don't know...
7
by: Curious | last post by:
Hi, I have created my own data structure. Basically it is a two way 'stack', where I can push elements and pop elements both from top and bottom. I also included a counter to show the number...
4
by: Roy | last post by:
I have an object with array of some data types. How do I get the number of bytes for it in managed code? The underlying data types could be primitive types or userdefined structures. For example I...
7
by: carterweb | last post by:
This is how I do it now. 1. Determine the dimensions of the rectangle. 2. Set a my font size to a fixed maximum size. 3. Apply the font my string and measure the string using the graphics...
2
by: Connie | last post by:
We have a blob in one table that is storing pdf files. I need to write a select query that will grab that column and write those *.pdf files out to a location on my hard drive. Does anyone know...
5
by: desktop | last post by:
I have a function that takes two pointers to an array. The first point to the first element while the other points to the last element. int nums = { 1, 2, 3, 4, 5, 7, 8, 9}; int* result; int...
1
by: cmb3587 | last post by:
My code runs fine for the most part...the only time it fails is when I type in a negative to end the array. I don't want the negative number to be included in the array and I thought that is what...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.