SQL Server 2008 - How can I find the size (disk usage ) of a column in a table

Forums › IT Pro and developers › SQL Server 2008 - How can I find the size (disk usage ) of a column in a table

clinty

596 posts

Ultimate Geek
+1 received by user: 125

Topic # 136237 20-Nov-2013 08:40

Hi,

Have a client whose SQL Server 2008 (Express) DB is very close to the 10GB hard limit. Upgrading to Full SQL Server is not an option.

I have told them we need to remove some data, and that it is likely that the photos that are stored with each contact are the issue. However they refuse to remove data unless I can prove that will resolve the issue (and even then I am not sure if they will do it)

I have confirmed with SSMS that the table containing the photos is 9.8GB but as it also holds all the other relevant contact data I can't prove it is the photos.

This is a commercial program so I can't alter any of the DB tables etc.

Is there an SQL command I can use to show the total size (MB,GB) of all the data stored in a column in a table so I can prove it is the photos?

cheers
Clint

Inphinity

2533 posts

Uber Geek
+1 received by user: 940

Subscriber

Reply # 937697 20-Nov-2013 08:46

select sum(datalength(column)) from table

TwoSeven

1328 posts

Uber Geek
+1 received by user: 156

Reply # 937803 20-Nov-2013 11:32

Its not really a simple answer.

SQL Server stores data in 8K data pages, there are 8 pages to an extent (64kb) and 16 extents to a megabyte. A row in a page cannot be bigger than the page. There are three types of allocation unit, IN_ROW_DATA (normal stuff), LOB_DATA (your images, text etc,) and your ROW_OVERFLOW_DATA (varchar, varbinary etc.). If the data in a column in a data row (IN_ROW_DATA) contains more than 8K it will be moved to the ROW_OVERFLOW_DATA allocation unit. LOB data such as your images is ok.

To get the size of the database on disk SELECT used_page_count /128.0 FROM sys.dm_db_partition_stats will give you the size in megabytes.

To determine the allocation units and sizes used in each,

SELECT obj.name, alloc.type_desc, alloc.total_pages, alloc.used_pages, alloc.data_pages
FROM sys.allocation_units as alloc
JOIN sys.partitions as part on alloc.containter_id = part.partition_id
JOIN sys.objects as obj ON part.object_id = obj.object_id
WHERE obj.name = 'mytable'

This will give you the number of pages used in the LOB_DATA allocation unit which you can use to calculate the size on disk. It will also allow you to see the size of any large data fields you have that are moved to the ROW_OVERFLOW_DATA allocation unit.

Hope that provides some useful information regarding the issue.

Software Engineer

clinty

596 posts

Ultimate Geek
+1 received by user: 125

Reply # 937832 20-Nov-2013 12:19

TwoSeven: Its not really a simple answer.

SQL Server stores data in 8K data pages, there are 8 pages to an extent (64kb) and 16 extents to a megabyte. A row in a page cannot be bigger than the page. There are three types of allocation unit, IN_ROW_DATA (normal stuff), LOB_DATA (your images, text etc,) and your ROW_OVERFLOW_DATA (varchar, varbinary etc.). If the data in a column in a data row (IN_ROW_DATA) contains more than 8K it will be moved to the ROW_OVERFLOW_DATA allocation unit. LOB data such as your images is ok.

To get the size of the database on disk SELECT used_page_count /128.0 FROM sys.dm_db_partition_stats will give you the size in megabytes.

To determine the allocation units and sizes used in each,

SELECT obj.name, alloc.type_desc, alloc.total_pages, alloc.used_pages, alloc.data_pages
FROM sys.allocation_units as alloc
JOIN sys.partitions as part on alloc.containter_id = part.partition_id
JOIN sys.objects as obj ON part.object_id = obj.object_id
WHERE obj.name = 'mytable'

This will give you the number of pages used in the LOB_DATA allocation unit which you can use to calculate the size on disk. It will also allow you to see the size of any large data fields you have that are moved to the ROW_OVERFLOW_DATA allocation unit.

Hope that provides some useful information regarding the issue.

Thanks TwoSeven,

This looks promising but I get an error about container_id not being a valid column name. Tried replacing it with the column i want to check, but still some errors.

Can we assume the following:

DB is called D
Table is called T
Column (not surprisingly) is called C

Can you show me where I need to change the variables

Cheers
Clint

TwoSeven

1328 posts

Uber Geek
+1 received by user: 156

Reply # 937909 20-Nov-2013 13:54

There is a typo in the query for container_id, if you do a Select * from SYS.allocation_units, you can see what the field name should be.

In answer to your second part, in SQL Management Studio, select the database and then 'new query' then at the top do Using D, the where clause will change to be obj.name = 'T'

Software Engineer

Regs

Infrastructure Geek
4057 posts

Uber Geek
+1 received by user: 195

Trusted

Microsoft NZ

Subscriber

Reply # 938175 20-Nov-2013 21:09

i have a script that returns the number of rows, data space and index space used by each table in an SQL database. quite handy for investigating where space is going. grab it here:
http://www.oasystems.co.nz/files/regan/spacecalc.txt

Technical Evangelist
Microsoft NZ
about.me/nzregs
Twitter: @nzregs

clinty

596 posts

Ultimate Geek
+1 received by user: 125

Reply # 938176 20-Nov-2013 21:10

Thanks all for your help.

Both queries seem to give a similar answer (always good to have verification)

Now I just need to convince the client that the photos need to be remove or they need to spend some money on SQL Server :)

cheers
Clint

clinty

596 posts

Ultimate Geek
+1 received by user: 125

Reply # 938177 20-Nov-2013 21:11

Regs: i have a script that returns the number of rows, data space and index space used by each table in an SQL database. quite handy for investigating where space is going. grab it here:
http://www.oasystems.co.nz/files/regan/spacecalc.txt

Thanks will check this out as well (didn't see this before posting my previous reply)

regards,
Clint

TwoSeven

1328 posts

Uber Geek
+1 received by user: 156

Reply # 938308 21-Nov-2013 08:02

Out of interest, before you talk to the customer do you have your solutions prepared - what version of SQL server will they need, cost, depreciation, hardware etc. Or what changes need to be made to their existing database (architecture, how are files being stored, duplicate files etc.).

Software Engineer

shaned

18 posts

Geek

Reply # 938427 21-Nov-2013 12:14

Since it's image data taking the space it should be possible to simply re-encode each image with a lower quality (and smaller size). The customer is much more likely to accept a photo quality reduction than they are to completely loosing that data.

If you take this route I'd suggest also backing up the original photo data as you process them. This allows you to re-run the compression later with different parameters but using the originals rather than getting cumulative losses due to re-re-encoding. (I'd also backup the hash of the re-encoded image so that on a re-run you can detect if the image has been changed in the DB, in which case you want to take the DB image as the source)

clinty

596 posts

Ultimate Geek
+1 received by user: 125

Reply # 938981 22-Nov-2013 13:06

shaned: Since it's image data taking the space it should be possible to simply re-encode each image with a lower quality (and smaller size). The customer is much more likely to accept a photo quality reduction than they are to completely loosing that data.

If you take this route I'd suggest also backing up the original photo data as you process them. This allows you to re-run the compression later with different parameters but using the originals rather than getting cumulative losses due to re-re-encoding. (I'd also backup the hash of the re-encoded image so that on a re-run you can detect if the image has been changed in the DB, in which case you want to take the DB image as the source)

TwoSeven: This is what I have proposed to the client.

For a bit more background - The actual program is ACT! 2012 CRM software, which has used SQL Express as its database. The client uses it to managed their workforce and the leads (they are a temp agency). They take a photo of each worker when they sign them up. It looks like the photos have been taken in High res since about 2005 each pic is around 2-3MB. Not normally a big issue except they have around 5,000 staff on the books over the years - meaning around 10GB of pics in the DB.

Resolution is not an issue, so have adjusted their camera to do 640x480, which is perfectly adequate for their needs - file size is now around 100KB

They have all the images stored outside of the database ( the sysadmin in me deplores the double up, but looks like it might come in handy), so i will use a bulk image processor to reduce these down to 640x480.

I don't really want to go messing with the SQL DB in case i break ACT!, however it takes less than a minute for a user to replace the old large photo with the new smaller one, so have suggested they do this over the next few months as they access the contacts via the ACT! interface.

I will run SQL Shrink DB each month or so to hopefully bring the file size down.

This seems to be the best solution with least impact.

I am sure we could automate the pic swap out - but this client seems prone to the adage - "if it can break, it will" :) so prefer to take the slow road

Am waiting to hear back from the client if they are happy with this

cheers
Clint

edit: Typos

clinty

596 posts

Ultimate Geek
+1 received by user: 125

Reply # 938988 22-Nov-2013 13:11

TwoSeven: Out of interest, before you talk to the customer do you have your solutions prepared - what version of SQL server will they need, cost, depreciation, hardware etc. Or what changes need to be made to their existing database (architecture, how are files being stored, duplicate files etc.).

Just expand a wee bit on this....

Normally yes - two or three different options if possible, with costing of each - depreciation cost etc depend on the client being address. Reasons why they may have to change the method of doing things to better look after the DB or server

This client prefers not to have their status quo method of doing tasks changed in any way shape or form, even if there is a good technological benefit. Frustrating to work with as an IT guy, but they are client, and good people so i roll with it :)

cheers