SQL Server database file not being truncated

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

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)

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

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!

You can use the DBCC commands to shrink the database.

Here is the reference to DBCC SHRINKDATABASE and DBCC SHRINKFILE

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.