Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
james_wells
Yak Posting Veteran
55 Posts |
 Posted - 2010-03-31 : 18:11:33
|
| I Have a database MDF file that is 41gb in size with 31GB of free space and it will not shrink less then the initial size of 41GB which was created by a database restore commend.There was a redundant table in the database which was 30GB in sizewhich was truncated by using the TRUNCATE TABLE command after the restore hence why we have a large free space percentage. The DBCC SHRINKFILE('DATABASE', target size) will not shrink the MDF data file (at all) in the primary group below its initial size.I have tried creating a secondry data file and move the data over using the DBCC SHRINKFILE ('DATABASE' , EMPTYFILE) command which moved most of the data over (not all) to leave 40GB of free space but agin it would not shrink.The Database was backed up between each operation to ensure truncation could take placeALTER DATABASE will not allow you to change the initial size below the current database sizeTried using Management Studio menu options - still will not shrink.Tried to manual change the initail size through the database properties - again this does not work Using SQL 2005 - compatible level 90Any ideas |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
james_wells
Yak Posting Veteran
55 Posts |
Posted - 2010-04-01 : 11:18:46
|
| Rebuild indexes ???????If the file was 41GB in size with 40GB of Free Space i would doubt that indexes would need rebuilding.Indexes on all tables are rebuilt on a weekly basis. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
james_wells
Yak Posting Veteran
55 Posts |
Posted - 2010-04-06 : 07:18:55
|
| I did not mean to upset you and grateful for any suggestionsI did a complete rebuild on all base tables which completed withoutany errors followed by a shrinkfile and then a shrinkdatabase and viewed the space recovered and database/file status after each stage.The MDF still refused to shrink below its created size.My only conclussion is that there are data pages/indexes pages that will not or cannot be moved towards the end of the MDF file as even after using the EMPTYFILE option with the SHRINKFILE command the remaining data in the MDF file with 40GB of space could not be shrunk. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-04-06 : 23:29:02
|
| Was that large table a HEAP? And, did it contain LOB data?If so, that data space is probably still allocated to that large table. When you try to shrink, it can't because the space is allocated. For this situation, you need to add a clustered index and then remove it. That will force the data space for the table to be reallocated and free that space.BTW - you don't need to perform both a SHRINKFILE and SHRINKDATABASE. I recommend using SHRINKFILE only - since that gives you more control over the process and what is affected. |
|
|
|
james_wells
Yak Posting Veteran
55 Posts |
Posted - 2010-04-07 : 16:42:24
|
| jeffwWas that large table a HEAP? N0 it has a clustetred index And, did it contain LOB data? YesNormally i would only use SHRINKFILE but i was scrapping the barrell a bit as i was running out of ideas.However i was inspired by your sugestion jeffw.Dropping the clusted index first followed by Truncate tablefollwed by DBCC cleantable (not sure if this did anything)followed by shrinkfile did the trickAlthough i am supprised that dropping the clustered index first made a difference wether it was due to the large amount of data truncated or because most of the space was LOB data because i was pretty sure that i have truncated tables before that had clustered indexes wihtout any issues.Many Thanks jeffw for your help and guidance |
|
|
|
|
|
|
cant shrink MDF file