| Author |
Topic |
|
geosync
Starting Member
7 Posts |
 Posted - 2007-10-01 : 22:03:39
|
| Admin is kind of new to me. Could someone recommend how to reduce these file sizes?In Dev, my MDF is 16 MB, LDF .5 MB.I run a script to recreate the schema and insert rows on the Host.When downloading a db backup from the Host and restoring it to Dev, my MDF is 24 MB, LDF 25 MB.The same dataset grew by ~35 MB!!!Any advice is welcome. Thank you. Rick |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-01 : 22:10:33
|
| If the db is not in simple recovery mode, you need do log backup to prevent log file from growing too big. |
 |
|
|
geosync
Starting Member
7 Posts |
Posted - 2007-10-01 : 22:44:50
|
| btw, I only want to shrink these files once. |
|
|
|
geosync
Starting Member
7 Posts |
Posted - 2007-10-01 : 22:57:28
|
| Ah, using Task > Shrink > Files (menu choices from right-click on specific database), I was able to drop file size from 50 MB to 25 MB (50% !!).Don't know how the log file got so big in the first place, but I think this will do for now. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-01 : 23:12:40
|
| Because you inserted rows, that genareted log. |
|
|
|
geosync
Starting Member
7 Posts |
Posted - 2007-10-01 : 23:25:43
|
| I didn't insert any rows; it's the same data.Something happens in the conversion when the data travels DEV-PROD-DEV. The result is larger MDF & LDF. Shrinking these files helps, but their combined size is still 7 MB larger than the original DEV files. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 04:27:03
|
"I didn't insert any rows; it's the same data."Well this:"I run a script to recreate the schema and insert rows on the Host."would have inserted rows and generated log  You will also need to Reindex to defrag the indexes - to get "tightest" filesize. And probably set the index fill-factor to 100%, which may be a rally bad idea for some indexes.Then when you shrink the database all that hard work Reindex made to make the index blocks contiguous may very well get thrown out the window in the effort to coalesce all free space, in order to minimise the file size.Why are you trying to get the production database smaller? Unless it is read-only the first thing that will happen is that the users will add data, and the database will have to grow. Better to pre-set a decently large size to allow for expansion, because extending the database has some "cost" associated with it.Kristen |
|
|
|
geosync
Starting Member
7 Posts |
Posted - 2007-10-02 : 11:16:51
|
| Thank you, Kristen. Now I see the error of my thought process.I forgot about reindexing. I'll give that a go.I am trying to make the db smaller before I move it to a new host env't. I want to start with the smallest possible file sizes, hoping to avoid buying more database space for a little while.Thanks again for your time amd valuable input.Regards, Rick |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 16:33:26
|
| "I want to start with the smallest possible file sizes, hoping to avoid buying more database space for a little while.""Right-sizing" your database would be better. That would mean that a number of things were predictable:1) The size of the database2) The performance of the database (unencumbered by file-extensions)3) The size of the backups.Shrinking the database to the smallest possible size leaves NO room for Real World Data to be added, without immediately jeopardising all three of these rules.One possible justification would be to Wow! journalists with how small your database is for the job at hand - despite the fact that you could probably store 1,000 such databases on my 8-year-old-Daughter's-MP3-playerKristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 03:25:46
|
| funny that 50MB is considered big.also, keep in mind that new databases are created from the model database. you might have different sizes specified for mdf and ldf files on your DEV system for model databae than you do on your Prod system.-ec |
|
|
|
geosync
Starting Member
7 Posts |
Posted - 2007-10-03 : 09:28:07
|
| Thank you, again, Kristen,Right-sizing seems like a better approach. Now I have learnt more about db admin.I am grateful.Rick |
|
|
|
geosync
Starting Member
7 Posts |
Posted - 2007-10-03 : 09:33:57
|
ec-My signature (not shown here) usually says "It's all relative."For me, 50 MB is only big relative to an MDF / LDF combination amounting to 16 MB (>3 x orig size for "same dataset".)I find this info quite enlightening: quote:
Also, keep in mind that new databases are created from the model database. you might have different sizes specified for mdf and ldf files on your DEV system for model databae than you do on your Prod system.
Thank you!I need to freshen-up on my db admin concepts.Regards,Rick |
|
|
|
|
Large File (MDF & LDF) Sizes