October 11, 2005 at 12:11 pm
I have a database that was created with one data file of 30000 Mb and one log file of 6000 Mb. Neither is set to auto grow. Only 1.5 GB of data space is being used. I want to 'shrink' the database, and change the options so it is set to auto grow. I know I can't shrink it below the size it was originally created (30000 Mb data + 6000 Mb log) using dbcc shrinkdb. How can I go about changing the sizes of the files to be much smaller and changing the properties so they will auto grow. Thanks for the help.
October 11, 2005 at 12:37 pm
Use the ALTER DATABASE command (see the BOL)
& MODIFY FILE
Then you can set the "original size" of a file & rerun DBCC SHRINKDATABASE
October 11, 2005 at 1:27 pm
I just did ALTER DATABASE MODIFY FILE to change the FILEGROWTH to 10% and MAXSIZE to UNLIMITED for both the data and the log file. Then I tried to change the size of the file:
ALTER DATABASE hollydb
MODIFY FILE
(NAME = hollydb_Data,
SIZE = 2MB)
GO
This failed with the following message:
Server: Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
This is what I actually thought would happen, so I forgive me if I misunderstood what you were suggesting, or my original question was not clear.
So, I still don't know how to go about making the files smaller than they were originally created. Thanks for any replies.
October 11, 2005 at 8:06 pm
You could match the size as closely to your datausage, so all the data still fits. (1500 MB)
October 12, 2005 at 12:47 pm
Run DBCC SHRINKFILE , then the ALTER/MODIFY.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy