|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 30, 2009 8:16 AM
Points: 7,
Visits: 25
|
|
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?
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 5:10 PM
Points: 8,401,
Visits: 7,823
|
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 5:10 PM
Points: 8,401,
Visits: 7,823
|
|
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.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 10:25 PM
Points: 6,014,
Visits: 8,062
|
|
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?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 30, 2009 8:16 AM
Points: 7,
Visits: 25
|
|
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.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 10:25 PM
Points: 6,014,
Visits: 8,062
|
|
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?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 30, 2009 8:16 AM
Points: 7,
Visits: 25
|
|
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?
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 10:25 PM
Points: 6,014,
Visits: 8,062
|
|
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?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 17, 2009 2:00 PM
Points: 191,
Visits: 173
|
|
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
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 12:52 PM
Points: 83,
Visits: 81
|
|
| 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.
|
|
|
|
