database mdf file increase

Question

database mdf file increase

Mh-397891

SSCertifiable

Points: 6704
More actions
May 14, 2009 at 10:50 am

#130099

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

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?

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 3

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!