SQL Server Database File Not Truncated

Question

I have a ~ 4 GB database. I copied this database and deleted 99% of the data because I need a database with only the schema and basic data (mostly static data is saved).

Now the problem is that the MDF file is still ~ 4 GB in size. If I read the size of the tables (like this ), they summarize less than 20 MB together. The log file is already compressed, but not one of the scripts that I ran worked to shorten the DB file.

Note. I usually don’t do this, but this time I need to reduce the database (I know this is not recommended)

Edit: + Useful information

Team:

exec sp_spaceused

Conclusion:

database_name       database_size   unallocated_space
AccudemiaEmptyDb    3648.38 MB      4.21 MB

Team:

select object_name(id) as objname, SUM(dpages*8) as dpages, COUNT(*) as cnt
from sysindexes
group by id
order by dpages desc

Conclusion:

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

Answer 1

, , !

, :

DROP SERVICE [//Audit/DataWriter] 
GO

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

, 5 ! , , sysxmitqueue . , :

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

DBCC SHRINKFILE ! =) 40

, !

Answer 2

exec sp_spaceused

, , . , , .

test1 db, StackOverflow. 3 8 .

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

, , . , ? .

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

- ,

, : Service Broker. http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06 .

;

• script - - (, , ..)
• script data​​li >
• db

( SSSB )

Answer 3

Edit: it seems like space is still allocated somewhere. Can you try this query (based 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

Answer 4

You can use DBCC commands to compress the database.

Here is a link to DBCC SHRINKDATABASE and DBCC SHRINKFILE

Answer 5

What if you copy a database? Right-click on the database and complete the tasks, copy the database. Just a thought that you can easily try.