Databases
StackOverflow2013
Postsdbo
POSQL Server database file not being truncated
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server database file not being truncated
    primarykey
    data
    text
    <p>I have a database which is ~4GB in size. I've copied that database and deleted 99% of the data on it because I need a database with only the schema and basic data (mostly static data is kept).</p> <p>The problem now is that the MDF file still is ~4GB in size. If I read the size of the tables (using <a href="http://devio.wordpress.com/2007/10/11/how-much-space-do-my-sql-server-tables-use/" rel="nofollow">this</a>, for example), they sum less than 20 MB all together. The log file is already shrunk, but none of the scripts I ran worked for shrinking the DB file.</p> <p><em>Note: I usually don't do this, but this time I <strong>need</strong> to shrink the database (I know it's not recommended)</em></p> <h2>Edit: +Useful info</h2> <p>Command:</p> <pre><code>exec sp_spaceused </code></pre> <p>Output:</p> <pre><code>database_name database_size unallocated_space AccudemiaEmptyDb 3648.38 MB 4.21 MB </code></pre> <p>Command:</p> <pre><code>select object_name(id) as objname, SUM(dpages*8) as dpages, COUNT(*) as cnt from sysindexes group by id order by dpages desc </code></pre> <p>Output:</p> <pre><code>object_name(id) sum(dpages*8) count(*) sysdercv 675328 1 sysxmitqueue 359776 1 sysdesend 72216 1 sysconvgroup 47704 1 sysobjvalues 4760 5 sec_OperationAccessRule 3472 5 sec_PageAccessRule 2232 5 syscolpars 656 11 AuditObjects 624 2 sysmultiobjrefs 408 5 HelpPage 376 8 sysschobjs 352 9 syssoftobjrefs 328 7 sysidxstats 272 10 sysrscols 200 1 Translation 160 3 sysallocunits 128 3 sysiscols 128 8 syssingleobjrefs 96 5 sysrowsets 80 4 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USChris Frederick
    1. USDiego Jancic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server database file not being truncated
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server database file not being truncated
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server database file not being truncated
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAll solutions that I found on internet... DBCC SHRINKDATABASE, changing to RECOVERY SIMPLE and then DBCC, DBCC DBREINDEX on all tables, defrag indexes and update statistics and then DBCC, backing up/restoring, detaching/attaching, DBCC CLEANTABLE, among other scripts...
      singulars
      1. POSQL Server database file not being truncated
      1. USDiego Jancic
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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