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.



158 posts

Master Geek
+1 received by user: 14


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



Create new topic

This is a filtered page: currently showing replies marked as answers. Click here to see full discussion.

1461 posts

Uber Geek
+1 received by user: 426

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

788 posts

Ultimate Geek
+1 received by user: 51


  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

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:

Hierarchy of a mistake: Gerry Brownlee
Created by joker97, last reply by sdav on 25-Jul-2014 13:16 (47 replies)
Pages... 2 3 4


Geekzone giveaway: Thecus N2310 NAS
Created by freitasm, last reply by Dunnersfella on 24-Jul-2014 23:17 (81 replies)
Pages... 4 5 6


MH 17 "shot down" all dead
Created by joker97, last reply by ScuL on 24-Jul-2014 21:40 (203 replies)
Pages... 12 13 14


Is chorus going to deliberately slow adsl internet down
Created by rugrat, last reply by NonprayingMantis on 25-Jul-2014 13:00 (43 replies)
Pages... 2 3


Huge Fuss, didn't even make it a year.
Created by networkn, last reply by Glassboy on 22-Jul-2014 19:50 (121 replies)
Pages... 7 8 9


Sickening floral smell in car, What next?
Created by TimA, last reply by TimA on 25-Jul-2014 12:51 (38 replies)
Pages... 2 3


Skinny's new aggressive ad campaign
Created by Yabanize, last reply by Yabanize on 22-Jul-2014 23:35 (52 replies)
Pages... 2 3 4


Giving notice
Created by IlDuce, last reply by kharris on 24-Jul-2014 17:36 (15 replies)


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.