How to reduce MDF file size ?

Question

How to reduce MDF file size ?

Joy Smith San

SSC-Insane

Points: 24877
More actions
February 3, 2015 at 12:16 am

#295058

Hi,
Database size - 300GB
Only one MDF file.
Only 3 GB space left in the drive where MDF file is placed, and there 250 GB space available in a different drive.
If I add an NDF file on this second drive, will SQL server start writing into this new NDF *** soon its added ?
Will it move any existing data from MDF to NDF automatically ?
Also I would like to reduce the size of MDF file, what's the best approch to do the same ?
Thaks,
San.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 1

1. If you can allow the shrink operation then you can shrink the data file and after rebuild the indexes, because the shrink will introduce a lot of fragmentation.

2. You can create another NDF on the second drive and rebuild some of the clustered indexes with drop existing

CREATE CLUSTERED INDEX index_name ON table(column)

WITH (DROP_EXISTING = ON)

https://msdn.microsoft.com/en-us/library/ms188783.aspx

which will equally distribute data of the index on the two files. SQL Server uses proportional fill and round robin algorithms.

3. For more space, you can additionally shrink your Log file. It will not introduce fragmentation in data.

Igor Micev,My blog: www.igormicev.com

CodedSteve Old Hand Points: 344 More actions · Answer 2

Hello,

Have you tried to shrink file? right click database in SSMS, tasks, shrink and then choose files (not database) on the window that pops up you get "shrink action" options, i'd choose reorganize pages before releasing unused space & choose the min option.

if this doesn't work and you can find time to have the database offline you can back it up, delete the database and then restore it placing the mdf on a drive with extra space.

regards

satnam.singh 44358 Ten Centuries Points: 1300 More actions · Answer 3

Do you have proper Re-indexing of the Database happening on a Periodic basis?

With Thanks,

Satnam

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Thanks Igor Micev and CodedSteve.

No much free space in the DB, so shrinking will not release any space to drive.

Shrinking log file is also not an option as log files are placed in different drive and with MDF

So the scenario is like almost the entire drive is occuppied by the actual data and only 3GB left in the drive.

Let me try the options u specified. Thanks again !.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

satnam.singh 44358 (2/3/2015)
Do you have proper Re-indexing of the Database happening on a Periodic basis?
With Thanks,
Satnam

Yes, it runs every weekend.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Joy Smith San (2/3/2015)
If I add an NDF file on this second drive, will SQL server start writing into this new NDF *** soon its added ?

Are you creating the file in the same filegroup?

Joy Smith San (2/3/2015)
Will it move any existing data from MDF to NDF automatically ?

No it will not

Joy Smith San (2/3/2015)
Also I would like to reduce the size of MDF file, what's the best approch to do the same ?
Thaks,
San.

You could try the suggestion proposed by Igor

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Joy Smith San (2/3/2015)
Thanks Igor Micev and CodedSteve.
No much free space in the DB, so shrinking will not release any space to drive.
Shrinking log file is also not an option as log files are placed in different drive and with MDF
So the scenario is like almost the entire drive is occuppied by the actual data and only 3GB left in the drive.
Let me try the options u specified. Thanks again !.

I wouldn't do the shrink. Just add the new filegroup/file on the different drive.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

Perry Whittle (2/3/2015)
Joy Smith San (2/3/2015)
If I add an NDF file on this second drive, will SQL server start writing into this new NDF *** soon its added ?
Are you creating the file in the same filegroup?
Joy Smith San (2/3/2015)
Will it move any existing data from MDF to NDF automatically ?
No it will not
Joy Smith San (2/3/2015)
Also I would like to reduce the size of MDF file, what's the best approch to do the same ?
Thaks,
San.
You could try the suggestion proposed by Igor

Yes, I am adding the file in the same filegroup.

Basically my requirement is that, it should not use the remaining 3GB space and leave that free space in the drive.

I will try the options suggested by Igor

Thanks all.

Tac11 SSCertifiable Points: 6980 More actions · Answer 9

if you can't reduce the size of .mdf file, I think you should STOP auto growth on the drive which is almost full and add a .ndf file on different drive. so it will only write to another drive (new).

dan-572483 SSCoach Points: 15870 More actions · Answer 10

If you set the maximum size of the MDF file to it's current size, it will stop further growth and force new data to be written to the NDF on the other drive. Just be sure that the NDF is large enough to accommodate further expected growth.