SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/27/2013 :  10:29:23  Show Profile  Reply with Quote
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).

Duane.
http://ditchiecubeblog.wordpress.com/



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.








CODO ERGO SUM

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

ditch
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.


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

India
285 Posts

Posted - 08/26/2013 :  01:28:15  Show Profile  Reply with Quote
Hi,
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
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000