Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


database mdf file increase


database mdf file increase

Author
Message
Mh-397891
Mh-397891
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 1514
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.
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26827 Visits: 38145
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.

Cool
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)
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40942 Visits: 18863
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
My Blog: www.voiceofthedba.com
Mh-397891
Mh-397891
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 1514
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search