Query storage size on your tables or tables in the database.

A helpful query on finding the size of your specific tables in SQL Server. Just replace with your string list of tables.

Code Snippet

1. SELECT
2. sum(RowCounts) as RowCounts,
3. SUM(TotalSpaceKB) AS TotalSpaceKB,
4. SUM(UsedSpaceKB) AS UsedSpaceKB,
5. SUM(UnusedSpaceKB) AS UnusedSpaceKB
6. FROM
7. (
8.     SELECT
9.         sum(p.rows) AS RowCounts,
10.         SUM(a.total_pages) * 8 AS TotalSpaceKB,
11.         SUM(a.used_pages) * 8 AS UsedSpaceKB,
12.         (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
13.     FROM
14.         sys.tables t
15.     INNER JOIN      
16.         sys.indexes i ON t.OBJECT_ID = i.object_id
17.     INNER JOIN
18.         sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
19.     INNER JOIN
20.         sys.allocation_units a ON p.partition_id = a.container_id
21.     WHERE
22.         t.is_ms_shipped = 0
23.         AND i.OBJECT_ID > 255
24.         AND t.NAME IN ( <TABLE_NAMES> )
25.     GROUP BY
26.        p.Rows
27. )x

Or to get a list of all tables and their sizes in the database:

Code Snippet

1. SELECT
2.     t.NAME as TableName,
3.     sum(p.rows) AS RowCounts,
4.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
5.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
6.     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
7. FROM
8.     sys.tables t
9. INNER JOIN      
10.     sys.indexes i ON t.OBJECT_ID = i.object_id
11. INNER JOIN
12.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
13. INNER JOIN
14.     sys.allocation_units a ON p.partition_id = a.container_id
15. WHERE
16.     t.is_ms_shipped = 0
17.     AND i.OBJECT_ID > 255
18. GROUP BY
19.    t.Name, p.Rows

WCF & Silverlight max buffer size issue

Ran into these problems a few times with the WCF errors, in which the buffer size was the issue:

• “The remote server returned an error: NotFound”
• "Unable to read data from the transport connection: The connection was closed"
• "The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element."

This would be an easy solution if only dealing with the WCF. Since, we are using the WCF layer to pass data to/from the Silverlight layer the problem arises in two locations -- not one.

Over at Mehroz's Experiments (http://smehrozalam.wordpress.com/2009/01/29/retrieving-huge-amount-of-data-from-wcf-service-in-silverlight-application/) he goes into good detail on solving this problem.

So in the Silverlight's ServiceReferences.ClientConfig we increase the buffer size.

Code Snippet

 <binding name="BasicHttpBinding_IScientificDataService"   closeTimeout="00:01:00"openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" maxBufferSize="2147483647" maxReceivedMessageSize="2147483647" textEncoding="utf-8" transferMode="Buffered">
    <security mode="None"/>
binding>

In the WCF's Web.config, we needed to increase the maxBufferSize and maxReceivedMessageSize to a larger number Code Snippet   <system.serviceModel>     <behaviors>       <serviceBehaviors>         <behavior>           <serviceMetadata httpGetEnabled="true" />           <serviceDebug includeExceptionDetailInFaults="true" />           <dataContractSerializer maxItemsInObjectGraph="2147483647"/>         behavior>       serviceBehaviors>     behaviors>     <serviceHostingEnvironment multipleSiteBindingsEnabled="true" aspNetCompatibilityEnabled="false" />   system.serviceModel>