StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First run</p> <pre><code>exec sp_spaceused </code></pre> <p>within the database to check how much you can recover. If you find that it shows no space unused, then you have misunderstood the space allocation.</p> <p>This is how I normally shrink my <code>test1</code> db, which is where I playpen all my StackOverflow queries. I just cut it from 3GB down to 8MB.</p> <pre><code>use test1; exec sp_spaceused; checkpoint; alter database test1 set recovery simple; alter database test1 set recovery full; dbcc shrinkfile(1,1); dbcc shrinkfile(2,1); </code></pre> <p>For what it's worth, this is what I use to check allocation size by table. Maybe you were checking incorrectly? This includes indexes.</p> <pre><code>select object_name(id), SUM(dpages*8), COUNT(*) from sysindexes group by id </code></pre> <h3>EDIT - based on tables hogging the space edited into question</h3> <p>Martin's comment moved to answer: the tables involved are Service Broker conversations. <a href="http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06" rel="nofollow">http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06</a> The link has a workaround.</p> <p>There is an alternative; using your already cut down database</p> <ol> <li>Generate script - all objects - include all options (keys, fulltext, defaults etc)</li> <li>include the option to script data</li> <li>Create a new db and populate it from scripts</li> </ol> <p>(From recollection, the SSSB queues are not included in generate-data scripts)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload