How can I shrink the database file using dbcc shrinkdatabase?
search cancel

How can I shrink the database file using dbcc shrinkdatabase?

book

Article ID: 153820

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

The database file Symantec_CMDB (Symantec_CMDB.mdf) is taking up a very large amount of space. Often databse files larger than a 100Gb should be considered large - although some large customer environments see databases larger than this, and would be expected to.

A general rule of thumb, would be to expect a database size of 10-25Mb per computer.  Thus for every 1000 computers fully managed in the database you should expect a databse size between 10 and 25Gb.

 

Resolution

The SQL command to reduce file-space usage of the database .mdf file is DBCC SHRINKDATABASE.

The command has various options, which can be found in Microsofts KB system amongst others. For this KB we will demonstrate two usage scenarios and describe the two. We will use Symantec_CMDB as the database name in these exampled. Please substitute this name as appropriate in your environment.

DBCC SHRINKDATABASE (Symantec_CMDB,NOTRUNCATE)

This command will move all file fragments within the Symantec_CMDB.mdf (database) file to the beginning of the file. It will not actually free up any space. Sometimes you need to run this command to free up space in the database, if the free space is not already at the end of the file. You'll see this, if you run a db_spaceused and that doesn't match up with something like the sum of the size of the tables reported other places.

DBCC SHRINKDATABASE(Symantec_CMDB, TRUNCATEONLY)

This command will truncate and free up all empty file fragments at the END of the database file. As mentioned above, you may need to run the other shrinkdatabase command - or use some of the other syntax options available. More syntax options can be found on Microsofts website as well as other sources.