reduce initial size of mdf file
-
hello experts, a user created one database, i checked its initial size is 191 GB and filgrowth 12800%. and i checked by sp_spaceused only 4 gb is reserved space. i want to reduce the initial size of DB. and filegrowth to 10%. please help its a production DB.
-
you might want to explain what the issue is that you are trying to resolve. even if you change the "Initial size" of the db, it's not going to affect the actual size of the database at all.
as a general rule, you never want to shrink a database, unless a real reason came along...you moved massive amounts of data out of the database for example.
you cannot make the database smaller than the data it contains... if the db is 191 gig, with 4 gig free, the smallest you could make it would be 187 gig (191 -4)
here's the place to change the initial size..right click the database...properties... but it has no real effect, since the db is already bigger.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
no i am saying data size is 4 gb but mdf has initial size 191 GB. and m reducing it from the same window wat shown in screenshot but its not reducing. and autogrowth is 12800% it is also not reducing.
-
Zeal-DBA (2/3/2011)
no i am saying data size is 4 gb but mdf has initial size 191 GB. and m reducing it from the same window wat shown in screenshot but its not reducing. and autogrowth is 12800% it is also not reducing.exactly...the initial size does not change the actual size...only shrinking will do that.
shrinking is something you do rarely/almost never in a production environment....but it sounds like you meet the exception-to-the-rule criteria.
after changing the size to say , 16 Gig to allow for some expansion,
you can right click on the database in SSMS...asks....Shrink...
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I would be very careful if I change the file size (since it is production). if you alter the file size and try to set it up to a smaller size, it will issue a ShrinkFile Command. Alter Database ... MODIFY File fails, if you try and set it to a size that is smaller than current size. Therefore a DBCC ShrinkFile command is issued.
-
is that possible to shrink a file less than to its initial size. i used to think that shrinkfile can be used to claim only unalloacted space. when i see through SP_SPACEUSED it shows me only 2 mb unallocated space. then how can i reduce file size from 191gb to 5 gb. is that possible???
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply