Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.



177 posts

Master Geek
+1 received by user: 15


Topic # 136237 20-Nov-2013 08:40 Send private message

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



Filter this topic showing only the reply marked as answer Create new topic
1587 posts

Uber Geek
+1 received by user: 494

Subscriber

  Reply # 937697 20-Nov-2013 08:46 Send private message

select sum(datalength(column)) from table




Windows 7 x64 // i5-3570K // 16GB DDR3-1600 // GTX660Ti 2GB // Samsung 830 120GB SSD // OCZ Agility4 120GB SSD // Asus PB278Q @ 2560x1440
Samsung Galaxy S4 GT-I9505 w/Telecom

825 posts

Ultimate Geek
+1 received by user: 62


  Reply # 937803 20-Nov-2013 11:32 Send private message

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.




TwoSeven



177 posts

Master Geek
+1 received by user: 15


  Reply # 937832 20-Nov-2013 12:19 Send private message

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

825 posts

Ultimate Geek
+1 received by user: 62


  Reply # 937909 20-Nov-2013 13:54 Send private message

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'





TwoSeven

Infrastructure Geek
3668 posts

Uber Geek
+1 received by user: 80

Trusted
Microsoft NZ
Subscriber

  Reply # 938175 20-Nov-2013 21:09 Send private message

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


Conference tickets selling out fast, Keynote/TechLive only tickets still available - http://newzealand.msteched.com

TechEd New Zealand 2014 Sep 7-9




177 posts

Master Geek
+1 received by user: 15


  Reply # 938176 20-Nov-2013 21:10 Send private message

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



177 posts

Master Geek
+1 received by user: 15


  Reply # 938177 20-Nov-2013 21:11 Send private message

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

825 posts

Ultimate Geek
+1 received by user: 62


  Reply # 938308 21-Nov-2013 08:02 Send private message

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.).





TwoSeven

15 posts

Geek


  Reply # 938427 21-Nov-2013 12:14 Send private message

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)



177 posts

Master Geek
+1 received by user: 15


  Reply # 938981 22-Nov-2013 13:06 Send private message

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



177 posts

Master Geek
+1 received by user: 15


  Reply # 938988 22-Nov-2013 13:11 Send private message

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

Filter this topic showing only the reply marked as answer Create new topic




Twitter »
Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:




News »

Trending now »
Hot discussions in our forums right now:

Mr. Key to extradite Kim Dotcom?
Created by TimA, last reply by RickD on 15-Sep-2014 21:32 (104 replies)
Pages... 5 6 7


Moment of Truth?
Created by BarTender, last reply by marmel on 15-Sep-2014 21:32 (79 replies)
Pages... 4 5 6


Spark DNS Issues - Amazing - Broadband Service Alert
Created by PeteS, last reply by Demeter on 15-Sep-2014 14:13 (307 replies)
Pages... 19 20 21


hp bios update
Created by foxy38, last reply by foxy38 on 14-Sep-2014 19:08 (26 replies)
Pages... 2


2014 Holden SS (V8) or Ford XR6-T (in-line 6 turbo)
Created by joker97, last reply by joker97 on 15-Sep-2014 16:11 (69 replies)
Pages... 3 4 5


Have $10,000, now what?
Created by Unregistered, last reply by pbgben on 15-Sep-2014 12:51 (44 replies)
Pages... 2 3


Warning: Rage Ahead - Campbell Live and childhood poverty
Created by kawaii, last reply by nunz on 15-Sep-2014 20:25 (231 replies)
Pages... 14 15 16


Apple Watch - It's official!
Created by dickytim, last reply by joker97 on 13-Sep-2014 11:53 (60 replies)
Pages... 2 3 4



Geekzone Live »
Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.