May 28, 2008 at 6:49 pm
One of my customers's admins set the initial size of their database to several gigabytes. That was fine for their main production file, but the same schema is also used for much smaller companies that they run with 1/100th of the data.
The initial size on all the databases is many Gigabytes, but the free space in all the smaller companies is over 96% free.
The DBCC SHRINKDATABASE command will not allow me to shrink the database below this initial size that was set by some other admin.
The database schema has changed over the years so there's no way to go to a backup from prior to the other admin dropping the lower size.
I've got hundreds of gigabytes of disk space tied up by these SQL databases that could be freed up if the initial size could just be changed back to a small number like 500MB instead of 50 GB.
There has GOT to be a way to get this done.
I was thinking maybe I could add a new file (called "NewFile) to the filegroup, set its initial size to 500MB then use this command:
DBCC SHRINKFILE (N'MyMDFName', EMPTYFILE)
to move all the data out of the MDF to the NewFile.ndf (which is the only other file in the filegroup). But that command fails to move all the data out of the original file. My hope was that I'd be able to move the data out of the mdf into this other file, then delete the MDF, but apparently that's not possible.
Then I thought OK maybe I can just "create" myself another database -- right-click the current db, script as create to a new window -- that approach works -- I can modifiy the initial size in that script, but that doesn't give me all my tables, indexes, views, stored procs, etc.
I already know that the fact that "you can't lower the initial size is by design". It being "by design" does not change the fact that there are legitimate scenarios where you have to do this. I can't be the first person to have this problem, yet after over 4 hours of searching I have yet to find a workable solution.
Can anyone offer a way that I can bring these files that contain only a few hundred megabytes of data back down to size when the initial size is set to several gigabytes?
May 28, 2008 at 7:30 pm
Is this SQL Server 2005? I thought that that Initial Size limitiation of SHRINKFILE had been removed for 2005?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 7:49 pm
Hah, found it. from BOL, "Shrinking a Database":
Manual Database Shrinking:
...
When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.
That should do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 8:11 pm
You can also just set the initial size lower using the ALTER DATABASE syntax.
As I recall it will do both for you (set the initial size and shrink the files as well), so pick a good time to do so.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 8:33 pm
rbarryyoung (5/28/2008)
Hah, found it. from BOL, "Shrinking a Database":However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.
[/i]
That should do it.
Yes, the database is running on SQL 2005 but in Mode 80 compatibility.
I tried running the command:
DBCC SHRINKFILE (N'DataFile_dat', 500)
which should shrink this file to 500MB or the actual size of the data, whichever is greater, but when statement executes, the file isn't actually reduced in size.
If I use the GUI - right-click on the database, then choose the Tasks --> Shrink --> Files option, the dialog shows that the available free space in the file is 91%. The Shrink actions are:
- Release unused space
- Reorganize pages before releasing unused space (with the ability to set a file size in the "Shrink File To" box)
Both of those options have the same result. Runs for a while, but doesn't actually shrink the file.
I tried change the database mode to Mode 90 compatibility and re-ran the above, and also tried to shrink the database itself -- nothing helped.
Also tried detatching / re-attaching the database. Also no difference.
May 28, 2008 at 8:43 pm
Setting the size of the files during the ALTER DATABASE statement will both change the initial size AND shrink the files.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 9:06 pm
Matt Miller (5/28/2008)
Setting the size of the files during the ALTER DATABASE statement will both change the initial size AND shrink the files.
Assuming that you mean the command:
ALTER DATABASE BLS_NewCo MODIFY FILE (Name = N'DataFile_dat', SIZE=700MB)
Running that command returns error:
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
So I tried changing the size in that command line to various other (bigger) sizes, but all fail with the same error.
Perhaps you're thinking of a different syntax?
May 28, 2008 at 9:24 pm
Darren Myher (5/28/2008)
Matt Miller (5/28/2008)
Setting the size of the files during the ALTER DATABASE statement will both change the initial size AND shrink the files.Assuming that you mean the command:
ALTER DATABASE BLS_NewCo MODIFY FILE (Name = N'DataFile_dat', SIZE=700MB)
Running that command returns error:
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
So I tried changing the size in that command line to various other (bigger) sizes, but all fail with the same error.
Perhaps you're thinking of a different syntax?
Hmm.... I'm befuddled - I could have sworn I'd used it to shrink stuff before, and yet - it doesn't do it. I guess I used it to GROW the initial size....
Looks like DBCC shrinkfile does shrink the file however.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 18, 2008 at 3:21 pm
Interestingly I am in this exact same scenario; needing to reduce the initial size of the mdf file *sigh*
If anyone has any further feedback/ideas in this respect it would be great to hear them as I am a little out of options (and disk space)
Cheers
Troy
October 29, 2008 at 6:56 am
I was in the same situation, but what helped me was to run a full backup right before I attempted to shrink the file. It did work for me.
October 30, 2008 at 9:15 am
I'm having the same situation - I restored a DB backup where the initial size of the mdf file is set to 60GB when the actual data is around 6GB.
Since I have enough space on my server I'm planning to create a new DB with a saner initial file size and move the data there and drop the old DB :unsure:
October 31, 2008 at 4:57 pm
I would agree with winash. You could create a new db with a smaller inital size and script out all the objects and data into the smaller db.
EDIT - oops, didn't realize how old this thread is.
November 4, 2008 at 6:43 am
hi, similar situation. any other means?
November 4, 2008 at 10:11 am
Yes, it seems that the "create a new blank database then script all the objects to that DB" is the only option.
Unfortunately for me that option didn't work. There were tens of thousands of objects. It would have taken weeks to debug the script.
But that may be an option for some people.
November 4, 2008 at 7:52 pm
scripting out is not a solution for me either ...
What's the take of microsoft on this?
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply