How to reduce the MDF Files

Question

How to reduce the MDF Files

hariharanc

SSC Journeyman

Points: 91
More actions
April 21, 2010 at 10:37 pm

#218151

My database has MDF datafile 20G bytes, when I backup DB, the backup file = 20G also
After I delete many records, many backup tables in database, the backup file reduce to 10G. I use Shrink database, the LDF file reduces size, but MDF file is still big with 20G. The number of record in DB is reduced but MDF file is not reduced
So how to reduce it?
Many Thanks in advance
Regards,
Hariharan

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

In SSMS open the shrink files dialog and choose the MDF file from the dropdown list. Does any free space show up? If so, try shrinking it with the "reorganize pages before releasing usused space" option. Set the desired size to 0 (it will automatically revert to the minimum allowed size).

Does it shrink this way?

-- Gianluca Sartori

hariharanc SSC Journeyman Points: 91 More actions · Answer 2

Hi,

Is this is the only way of doing it..? Could you please elaborate more on this. I am not able to follow you..

Regards,

Hariharan

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

No, it's not the only way. You can use the DBCC SHRINKFILE command.

Usually, for the MDF file (file_id=0) you can use:

DBCC SHRINKFILE(0,0)

It shrinks the file with id = 0 (usually MDF file) to the specified size (= 0, that means the minimum possible size). Before shrinking, it reaorganizes pages.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

I forgot to mention that this command works on current database, so be sure to issue USE databasename before running.

-- Gianluca Sartori

hariharanc SSC Journeyman Points: 91 More actions · Answer 5

Hi,

When I execute this above command, I am getting the below error,

Msg 8985, Level 16, State 2, Line 2

Could not locate file '0' for database 'PrecastArchive' in sys.database_files. The file either does not exist, or was dropped.

Please advice on this.

Regards,

Hariharan

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Ooops! MDF file has usually id = 1, not 0.

Try to run this and you'll get the file list for your database:

select *

from sys.database_files

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 7

You can also use the logical name of the file if you have it.

DBCC shrinkfile('MyFileName',2)

But, shrinking files should only be done if you know, for sure, that the files aren't just going to grow again. If they grow/shrink/grow/shrink you get a lot of fragmentation which can seriously impact performance, not to mention all the extra processing time to grow and shrink the file.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning