Author |
Topic  |
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 06/27/2013 : 10:29:23
|
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 |
 |
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1466 Posts |
|
raghuveer125
Constraint Violating Yak Guru
India
285 Posts |
Posted - 08/26/2013 : 01:28:15
|
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! |
 |
|
floook
Starting Member
USA
2 Posts |
Posted - 12/24/2014 : 15:51:58
|
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. |
 |
|
Topic  |
|