SQL Server database file not being truncated -

-

i have database ~4gb in size. i've copied database , deleted 99% of data on because need database schema , basic data (mostly static data kept).

the problem mdf file still ~4gb in size. if read size of tables (using this, example), sum less 20 mb together. log file shrunk, none of scripts ran worked shrinking db file.

note: don't this, time need shrink database (i know it's not recommended)

edit: +useful info

command:

exec sp_spaceused

output:

database_name       database_size   unallocated_space accudemiaemptydb    3648.38 mb      4.21 mb

command:

select object_name(id) objname, sum(dpages*8) dpages, count(*) cnt sysindexes group id order dpages desc

output:

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

first run

exec sp_spaceused

within database check how can recover. if find shows no space unused, have misunderstood space allocation.

this how shrink test1 db, playpen stackoverflow queries. cut 3gb down 8mb.

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

for it's worth, use check allocation size table. maybe checking incorrectly? includes indexes.

select object_name(id), sum(dpages*8), count(*) sysindexes group id

edit - based on tables hogging space edited question

martin's comment moved answer: tables involved service broker conversations. http://social.msdn.microsoft.com/forums/en/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06 link has workaround.

there alternative; using cut down database

  1. generate script - objects - include options (keys, fulltext, defaults etc)
  2. include option script data
  3. create new db , populate scripts

(from recollection, sssb queues not included in generate-data scripts)


Comments

Post a Comment

Popular posts from this blog

c# - How to set Z index when using WPF DrawingContext? -

-
how set z-index drawing object when using drawingcontext.drawxxx() methods? the object drawn last have higher z index. can't change index of drawn objects. way draw in order. if using wpf (as placed tag), can use, example, canvas control. create shapes need like polyline obj = new polyline(); //... // ... set properties of obj and add them canvas uielementcollection: yourcanvasname.children.add(obj); //or yourcanvasname.children.insert(i, obj); first items of collection have higher z index. advantages in way: no need redraw on window changes, can anytime move objects , change order.
Read more

razor - Is this a bug in WebMatrix PageData? -

-
i think may have found bug in webmatrix's pagedata, not sure. concerns how pass data partial page calling page. in webmatrix documentation (tutorials, e.g. " 3 - creating consistent look ", , example code), pagedata recommended mechanism pass data between pages (e.g. content page layout page, or partial page). however have found not work other way, pass data partial page calling page. modifying or adding entries in pagedata in partial page, not seem calling page. cutting right down simplest possible example, in test page may have this: @{ pagedata["test"] = "initial entry"; } <p>before calling partial page, test value @pagedata["test"]</p> @renderpage("_testpartial.cshtml") <p>after returning calling page, test value @pagedata["test"]</p> and in _testpartial.cshtml page might have this: @{ pagedata["test"] = "modified entry"; } <p>in partial page...
Read more

visual c++ - Using relative values in array sorting ( asm ) -

-
i need sort through array , sort each individual row in array in ascending order. doesn't seem going (surprise!) keep getting hit 2 errors: a2101: cannot add 2 relocatable labels , a2026: constant expected here's sort, makes sense me, think i'm still trying implement high-lvl language techniqes assembly. there way around not being able use relative values? (the array 7 rows 9 columns, btw). mov cx, 7; cx = number of rows outer: ; outer loop walk through rows push cx mov cx, 9 mov row, cx ;rows middle: ; middle-loop walk through columns push cx sub cx, 1 ;cx = cx-1 mov column, cx ;columns inner: ;inner loop - compare , exchange column values cmp marray[row*9 + column], marray[row*9 + column+1] xchg marray[row*9 + column+1], marray[row*9 + column] ; compare , exchange values marray table inc column loop inner pop cx loop mid...
Read more