Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 unable to shrink mdf file
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 06/27/2013 :  10:29:23  Show Profile  Reply with Quote
Originally posted by ditch

Nope I haven't. But what is working is your approach where you shrink by blocks of 100MB.
That seems to be working great.

My concern with rebuilding indexes is the amount of time that it will interfere with production as some of our tables are quite large (in excess of 3 Billion rows).


You may want to just go with reorganize instead of rebuild on those large tables.

If you do a rebuild, you will need as much free space in that database as the clustered index of the largest table, and that might defeat the purpose of doing a shrink.

Also, unless you can do an online rebuild, those large tables may be locked for an extended amount of time. A reorganize is an online operation, so the tables would remain available.


Edited by - Michael Valentine Jones on 06/27/2013 10:30:05
Go to Top of Page

Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/27/2013 :  10:50:47  Show Profile  Visit ditch's Homepage  Reply with Quote
Thanks MVJ, I think I will try the reorganize approach then.

Go to Top of Page

Constraint Violating Yak Guru

285 Posts

Posted - 08/26/2013 :  01:28:15  Show Profile  Reply with Quote
If mdf is not shrinking by DBCC Shrinkfile,rebuilding, reorganizing & changing fill factor also!
Then think of this scenario:
Let's assume one table in database is having 5 columns with huge data. (Assume 60 GB of data)
Example:Table columns are (Fname,Lname,Dob,Text,Gender)
If we drop one column(Assume gender column deleted) from the table and after that we deleted some data/rows from table (Assume 35 GB of data)
And try to shrink the .mdf file it won't release the space even if we use DBCC,index rebuild,reorganize or fill factor.
In that case first we need to free the table column (Using DBCC Cleantable)
Next run the DBCC Shrinkfile(1) against your database. It may be help.

No one is perfect in this world!

In Love... With Me!
Go to Top of Page

Starting Member

2 Posts

Posted - 12/24/2014 :  15:51:58  Show Profile  Reply with Quote
I was facing the same problem on SQL Server 2008 R2. I ran dbcc shrinkfile with the notruncate option first to move data to the front of the file. After that I was able to shrink the file.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000