October 13, 2008 at 10:41 am
We moved several tables to a new filegroup. The PRIMARY mdb where they used to reside did not shrink after the move.
Do I need to change the Initial Size of the PRIMARY file to something smaller then shink it or is there another option?
I have already used sp_spaceused but no significant unallocated space is reported.
Many thanks,
--Scott
October 13, 2008 at 10:46 am
Any particular reason why you expected it to shrink?
Have you set your database to autoshrink?
On the other hand, it will never shrink to a size smaller than the original allocation set at creation time.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 13, 2008 at 10:54 am
I just assumed (you what that does :)) that moving tables to another filegroup, located on another drive would logically free up space on the PRIMARY. This didn't seem like to far fetched of a notion.
--Scott
October 13, 2008 at 11:07 am
... and it did actually free up space... inside your affected PRIMARY filegroup 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 13, 2008 at 11:11 am
then do we just need to lower the initial size of the primary file and restart sql services to gain the space back?
--Scott
October 13, 2008 at 12:43 pm
The only way that a SQL data or log file will decrease in size is if you explicitly shrink it or if you have autoshrink enabled (which is a bad idea). Shrinking a DB should be a rare event, typically done after archiving, large deletes or moving tables to other file groups.
You can use DBCC ShrinkFile to shrink the file down. Bear in mind that by doing so you will have badly fragmented all of the indexes in that file. It's a good idea to rebuild indexes after a shrink operation.
No need to restart the SQL Service before or after.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 12:59 pm
we'll give that a shot.
many thanks
October 13, 2008 at 2:18 pm
This is the first time I have asked a question here but here goes. In reading the previous information, I think he has the same problem as me. I cannot shrink my files to recapture the space because the initial size was not calculated correctly. I need help on how to move my database to files with a smaller "initial" size. It seems if I create a database with smaller files with a different name, and restore from a backup of the one I am having problems with, it resets the initial size on the new database.
I am seeking help with how to best move the database to files with the correct initial sizes......
thanks
October 14, 2008 at 1:17 am
1- Take two backups, verify after completion and have dump files in different sets of media.
2- Drop your database
3- Pre Create your database with the proper sizes.
4- Restore your database on top of your pre-created one.
5- Be happy forever.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 14, 2008 at 7:05 am
I thought of that - but it seems that when you do a restore - even into precreated files it reallocates them to the initial size of the dump?
I will try it though
October 14, 2008 at 8:50 am
Well look what I started. I appreciate everyone's input. Unfortunately we won't have time to test any of these solutions soon. I will try and update if we have any success.
Many thanks once again to all.
--Scott
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply