Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

database mdf file increase Expand / Collapse
Author
Message
Posted Thursday, May 14, 2009 10:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
Hi,

I have a db that is simple recovery model. The mdf file is increasing rapidly. I shrinked the database from 120gb to 80GB,but again the following day the mdf file size increased to 120GB. There is an application pointing to this database and we do some data imports from other servers to this database. Could you please tell me the reason for this increase in the size and what can I do to stop this rapid increase.

Thanks.
Post #717172
Posted Thursday, May 14, 2009 10:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 23,070, Visits: 31,598
If it is growing, it is because it needs to in order to store the data being inserted. Constantly growing and shrinking the mdf file is going to result in physical fragmentation of the file and will impact performace. If you have the available space on disk for the file, I'd leave it alone. If you don't, I'd look at moving the database to a larger disk drive or upgrade the disk itself.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #717181
Posted Thursday, May 14, 2009 10:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
First, always, always, ALWAYS have free space in your mdf data files (or ndf files). This goes for the logs as well. Growing a file on demand is an expensive process and will impact performance. These are not Excel or Word files, but database files for a server process.

Second, as you change data, your logs and data files get filled up. Log backups are there to help with DR, but also they mark the space in the logs as free once it's backed up, so have these set up.

In your data files, as you add, and possibly update data, you need more space. So if you are doing that, space will get used up. You should have 1-6 months of space for data growth in your data files. The amount depends on how often you want to check things.

Third, shrinking files causes internal fragmentation. Don't do it. Set a size.

Fourth, reindexing, and other maintenance operations need space, so they could cause an auto-grow.

If the file autogrows, it can grow by a set MB amount or a percentage. You can check this in the database properties, but this shouldn't be how you manage space, this is for emergencies.

Does that help? Other questiosn?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #717182
Posted Thursday, May 14, 2009 2:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
Thanks for the replies, that was helpful. I cleared up some space on that mdf file drive. As far as I understand there's not much we can do other than increase the mdf file's drive space.
Thanks!
Post #717396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse