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.


clinty

1182 posts

Uber Geek

Lifetime subscriber

#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
Inphinity
2780 posts

Uber Geek


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

select sum(datalength(column)) from table



TwoSeven
1623 posts

Uber Geek

Subscriber

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




Software Engineer
   (the practice of real science, engineering and management)
A.I.  (Automation rebranded)
Gender Neutral
   (a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

 

 ...they/their/them...


clinty

1182 posts

Uber Geek

Lifetime subscriber

  #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



TwoSeven
1623 posts

Uber Geek

Subscriber

  #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'





Software Engineer
   (the practice of real science, engineering and management)
A.I.  (Automation rebranded)
Gender Neutral
   (a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

 

 ...they/their/them...


Regs
4066 posts

Uber Geek

Trusted
Snowflake

  #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




clinty

1182 posts

Uber Geek

Lifetime subscriber

  #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

clinty

1182 posts

Uber Geek

Lifetime subscriber

  #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

 
 
 

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.
TwoSeven
1623 posts

Uber Geek

Subscriber

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





Software Engineer
   (the practice of real science, engineering and management)
A.I.  (Automation rebranded)
Gender Neutral
   (a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

 

 ...they/their/them...


shaned
20 posts

Geek


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

clinty

1182 posts

Uber Geek

Lifetime subscriber

  #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

clinty

1182 posts

Uber Geek

Lifetime subscriber

  #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





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









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.