Downsizing data files… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Downsizing data files…

aside from the usual shrinkfile, or shrinkdb… i’m looking for a way that i can reduce the size of a data file. one of the databases were created way to large, and i would
simply like to reduce the original file size. books online reports that when ever you use ‘modify file’
that the file must be adjusted to a larger size than originally
created. i find it hard to believe that this is the only way
to change a file size. thoughts?
Hi,
whats is the parameter for Max Size / auto graw for your file its in MB or % and how much MB or % ?
if its on % basis and if your datafile size is 12 gb and if you have set it to 15% then it will increase (12*1024)/10% = 1843.2 MB … would you please check those options ! Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

What’s wrong with SHRINKFILE? When you specify a targetsize (even lower than the initial size), SQL Server will try to shrink it to that size. If the size of your data is larger than what you have spcified, SQL Server will issue a warning and will only shrink to that size. <br />Another way I can think of, is to script database objects, BCP the data out, create a new database with the properties you wish to have, BCP the data back in, drop the original db and rename the new. But frankly, SHRINKFILE sound a whole lot easier to me. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
Ensure there aren’t any uncommitted in transaction log and is not allowing to shrink. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I think, this is DBCC OPENTRAN…[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
thanks for the feedback every one… ghemant the database options are as follows: autogrowth by 10mb.
– SQL.Admin – US.Florida
10% growth setting is by default and check how fast and quickly data is inserted on the database.
If it is not as big then you can shrink the database using Books online defined process. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
i think i may have caused some confusion. it’s not that my database has grown to big, or that
the database is approaching the drive limit, and thus
would require some kind of database shrinkage. i’m just trying to reduce the size that it was created
with. the database was created with 20Gb. it only has about 1Gb of data in it, and growth isn’t
expected to be huge. so in affect i would like to reduce
the size of the .mdb data file to around 1.5 Gb see what i mean? ________________________________________________________
by the way satya… we’ve spoken before in sqlmag forums.
what is your take on the new forums format? have you
experienced any problems? i have. they lost my original
user account. ‘sqladmin’ can’t post under that name
any more. tech support says i need a different account,
and that they some how lost it. you believe that? – SQL.Admin – US.Florida
Hey .. good to see some old peer here again.
Yes I have had lot of timeouts and most of questions were repetitive, with all that fuss I keep a distance from that forum now a days. Sometimes when I’m free from work then I check for interesting questions to answer. Ok, coming to your issue:
You can use DBCC SHRINKDATABASE shrinks data files on a per-file basis. However, DBCC SHRINKDATABASE shrinks log files as if all the log files existed in one contiguous log pool.
For each file, SQL Server calculates a target size, which is the size to which the file is to be shrunk. When DBCC SHRINKDATABASE is specified with target_percent, SQL Server calculates target size to be the target_percent amount of space free in the file after shrinking. For example, if you specify a target_percent of 25 for shrinking mydb. SQL Server calculates the target size for this file to be 8 MB (6 MB of data plus 2 MB of free space). Therefore, SQL Server moves any data from the last 2 MB of the data file to any free space in the first 8 MB of the data file and then shrinks the file. Have a good read abotu DBCC SHRINKDATABASE in books online. You would choose MODIFY FILE.. when using ALTER DATABASE and to shrink the database referred DBCC statements are suggested. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
thanks bro. i went ahead, and experimented with the shrinkfile… turns out that i could reduce the size below it’s original
creation size without any problems. thanks for the feed back though. ———–
about sqlmag forums…
there is too much fuss for sure.
time-outs, non reply populations to name a few. ah well… at least we still have sql-server-performance.com ! cheers – SQL.Admin – US.Florida
There is one doubt about this issue.. the Author asked to how to reduce he size of the database..? Is database size growing immensily ?? Do you find this is a problem? As far as my experiance.. using "shrink", that will reduce the side of the database for some time, thats not a solution, if I am not wrong. But other way I assume that, we can split the data into secondry filegroup and we put the most frequently used tables with indexes(clustered) in that. Through this, the performance will also improve upto an extend, instead of this, we can use the filegroup for filegroup-backup as well. -Johnson
ah well… at least we still have sql-server-performance.com !
I assure you will get a quicker response here…. SO keep in mind use only prescribed DBCC statements to reduce size of database and may not experiment with other statements. The database cannot be reduced lesser than model database, so make sure there is a nominal size is set.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>