December 13, 2007 at 3:35 pm
The .mdf file of one of my database is growing at an alarming rate. infact the .ldf file is much smaller then the .mdf file..what could be the reason for this state and how do u reduce the size of the .mdf file or stop it from growing at this rate..any help would be highy appreciated.
thanku
December 13, 2007 at 4:04 pm
Unless you have a secondary file set up (an ndf file by default) as the default file*, the mdf file is where all the data is stored. That means it should be growing (assuming an active database). The ldf file is just the transaction log.
*Which, by the way, is highly recommended.
December 13, 2007 at 9:32 pm
You must be adding data to the mdf, as mentioned above. That's why it grows. To stop it from growing, stop adding data.
You should have an idea of growth and make sure your MDF (and database) are large enough to accomodate growth for some length of time (weeks, months even)
December 14, 2007 at 1:18 am
December 14, 2007 at 7:48 am
the file growth of the database file has been 'Unrestrictedgrowth, By 1MB' and recoevery model is 'Simple'. has this anything to do with the service pack installed or something else?
December 14, 2007 at 8:13 am
If you mdf is growing large, it is because there is lots of data being inserted. You have confirmed that the growth is set to 1mb unrestricted. This means that everytime data get inserted and there is not enough space in the database it grows (1mb increments) to accomodate the new data.
Be careful if you restrict the growth because users will begin to get errors if the database has no allocated space and cannot grow.
If there is a service pack problem, I believe you would have seen a strange number in the growth setting.
December 14, 2007 at 8:21 am
Be careful with the growth rate. Growth 1MB at a time is a very small amount. Each time more than 1 mb is added the file will grow again (impacting performance; albeit in a small way) There is also a high probability that the MDF file will become phyically fragmented. It is much better to allocate a large amount of contiguous space and have the file seldom expand that to have it constantly grow. But to stop growth stop using the database. If you add data it gets physically larger.
Francis
December 14, 2007 at 8:22 am
So should the file growth be set to "By 10 or 20MB unrestricted" ?.. and also is there a way to clean up the drive were the .mdf file resides because it "shows low disk space on the drive F" all the time..does the change in the filegrowth setting solve the problem..i have no clue on this...please guide me on this...appreciate your help...:)
December 14, 2007 at 8:28 am
You don't want the file to grow by itself, ever. That's an emergency thing if you can't respond.
You should be monitoring this and if you see it getting low, add the space yourself. It would help if you could let us know what the size is and how quickly it's growing. I would typically add 50MB or 100MB at a time so this isn't a problem on a daily or weekly basis. I would expect to add space to a database 1-4 times a year.
If you are running out of space on a drive, you can do a few things. one is detach the database, copy to a new drive with more space, attach it back.
You can also add another file or filegroup to the database (right click, properties) and then specify a file on the new drive. You should mark the new file as the default and you could conceivably need to move tables from the old filegroup if they are growing.
December 14, 2007 at 8:50 am
Actual size of the .mdf file is around 84.5GB, Database size is around 86.8GB and the total size of the drive is 136GB. does shriking the mdf file help in any way?? below is the free space report...free space on F drive is 200KB.
--- F:
Date
Percent Free of 136 GB
07-12-13
5 %
07-12-12
9 %
07-12-11
11 %
07-12-10
0 %
07-12-09
4 %
07-12-08
10 %
07-12-07
16 %
07-12-06
20 %
Average
9 %
December 14, 2007 at 8:55 am
You can only shrink a database file if it has free space. The script below will give you that information.
Get Server Database File Information
December 14, 2007 at 9:06 am
Research these.
Try truncating the log
placing in simple recovery OR
backing up the transaction log
December 14, 2007 at 9:21 am
Right click on the database --> tasks --> Shrink --> database and see what percentage of allocated space is not being used.
By shrinking the database you can return space to the file system.
December 14, 2007 at 9:24 am
If there is not much space to gained from shrinking the database, your best options are to create secondary data files on seperate drive/mount point, or detach/attach the mdf to a drive with the appropriate space.
December 14, 2007 at 9:26 am
HEY,
I THINK YOU SHOULD ALLOW YOUR DATABASE .mdf TO GROW AT WILL AND RUN DBCC FOR MAINTENANCE BASIS MAY ON WEEKEND OR SO...
AND IF YOU DATABASE HAS BEEN AROUND FOR YEARS YOU HAVE DUMP IT THEN WITH APPROVE FROM YOUr BOSS PRUNE HISTORIC DATA BASE ON YEARS.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply