SQL Server database file not being truncated

Question

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

The problem now is that the MDF file still is ~4GB in size. If I read the size of the tables (using this, 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.

Note: I usually don't do this, but this time I need to shrink the 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) as objname, SUM(dpages*8) as dpages, COUNT(*) as cnt
from sysindexes
group by id
order by 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

Ammunition

All 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... – Ammunition

Answer 1

Thank you guys, and mainly Richard for all the information!

To fix the problem, I had to drop and recreate my SERVICES:

DROP SERVICE [//Audit/DataWriter] 
GO

CREATE SERVICE [//Audit/DataWriter] 
    AUTHORIZATION dbo 
ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])

Once I did that, the database was 5GB! But this time the second query I put in my question, showed sysxmitqueue as first result. Digging a bit more on Internet, I was able to purge the big table doing so:

ALTER DATABASE [your_database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [your_database] SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [your_database] SET MULTI_USER
GO

Then, run DBCC SHRINKFILE and that's all!! =) It's now only 40MB

Thank you guys!

Ammunition

Answer 2

First run

exec sp_spaceused

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.

This is how I normally shrink my test1 db, which is where I playpen all my StackOverflow queries. I just cut it from 3GB down to 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 what it's worth, this is what I use to check allocation size by table. Maybe you were checking incorrectly? This includes indexes.

select object_name(id), SUM(dpages*8), COUNT(*)
from sysindexes
group by id

EDIT - based on tables hogging the space edited into question

Martin's comment moved to answer: the tables involved are Service Broker conversations. http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06 The link has a workaround.

There is an alternative; using your already cut down database

1. Generate script - all objects - include all options (keys, fulltext, defaults etc)
2. include the option to script data
3. Create a new db and populate it from scripts

(From recollection, the SSSB queues are not included in generate-data scripts)

Borek

OK. It says Database Size = 3.6GB, Unallocated = 8 MB --- How do I find where the space is being used? Scripts as the mentioned in the original question say the tables are only ~20 MB in size. – Ammunition

@Diego have you checked the last query in this answer? – Borek

OK Cool! The top 5 rows are sysdercv, sysxmitqueue, sysdesend, sysconvgroup, sysobjvalues. What does it mean? How do I empty space from there? Thanks! – Ammunition

@Diego - please edit question with output from (1) sp_spaceused (2) the last query in this answer – Borek

Looks like a service broker issue that Remus Rusanu dealt with here – Pericline

Good catch Martin. Nice teamwork :) +1 for your answer (can't +1 your comment) – Borek

Answer 3

Edit: so it seems that the space is still allocated somewhere. Can you try this query (based on sp_spaceused)?

select OBJECT_NAME(p.object_id),
 reservedpages = sum(a.total_pages),
    usedpages = sum(a.used_pages),
    pages = sum(
            CASE
                -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                When a.type <> 1 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
            END
        )
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
GROUP BY p.object_id
with rollup

Pericline

check the "not an answer" (at bottom) comment #5173186 – Borek

@Richard - Yes was just looking at that in Profiler. The value returned by that query is different from that returned as MinSize from DBCC FILEHEADER ('AdventureWorks2008', 1); so still unsure which of them would be regarded as the minimum size by DBCC SHRINKDATABASE – Pericline

OK. The object with more reserved space is "NULL" :(, after that sysxmitqueue, sysdercv, sysdesend and so on... – Ammunition

@Diego - The big row with NULL is just 'cos I added a rollup on to get grand totals. – Pericline

Answer 4

You can use the DBCC commands to shrink the database.

Here is the reference to DBCC SHRINKDATABASE and DBCC SHRINKFILE

Ideo

Answer 5

What if you copy the database? Right click on the database and do tasks, Copy Database. Just a thought that might be easy to try.

Orthochromatic