Files keep growing on old drive inspite of configuring secondary files growth on new drive!

Question

Files keep growing on old drive inspite of configuring secondary files growth on new drive!

Cobra-1032610

SSC Veteran

Points: 239
More actions
March 31, 2009 at 10:20 pm

#219585

Hi All,
Here is my concern (I’m using SQL 2k). We have two drives E and F which are loaded up to the brim with hardly any free space. So, I configured secondary files and allowed them to grow on a new drive G and stopped the growth on both E and F drives.
Every day when I monitor the drives, I see that the log files of one or two databases are still growing on E and F drives ( I have verified 100 times the “Automatically grow file option” is disabled or not for these DBs and it is) strange!!!!!!!
Well, this happens with some of the DBs and not all! Another oddity that I noticed is that, the log file of this particular DB is growing on F (though its growth has been stopped on F) and its growing on G as well!!
Please enlighten me!!
Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

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

homebrew01 SSC Guru Points: 55546 More actions · Answer 1

Automatically Grow applies to the data file, not the log file. The log file has to be able to grow, based on the transactions that occur. You can move the log file to another drive, or back it up more frequently to limit it's need to grow.

Cobra-1032610 SSC Veteran Points: 239 More actions · Answer 2

Thanks for the reply!!

But the data file that is on E: is growing, though I have restricted its growth on E: and have created a ndf on G: How can this be possible?

Please throw some light on this!!

Thanks in advance

homebrew01 SSC Guru Points: 55546 More actions · Answer 3

Restricted growth means it will grow up to the size you set. So if your file is 90 Gig, and restricted is set to 95, it still has 5 gig to grow before hitting the limit. I think if you disable autogrowth, then it should not grow at all. And remember that 1000 meg does not equal 1 Gig. Everyone uses that for convenience but it is not accurate. So consider actual sizes when comparing Gig, Meg and Kb

Sr SQL developer Default port Points: 1418 More actions · Answer 4

There could be a number of factors in play here. Did you specify an Initial Size for the new data file? Also, what is the Autogrowth setting for this new file? SQL Server writes to the data files based on free space of each file in the filegroup. If the original data file is 90 GB with autogrowth set to 10% it will grow 9 GB when more space is needed (up to the maximum size). If the second data file is 2 MB with autogrowth set to 1 MB it will only grow 1 MB when more space is needed. In this example, more new data added to the database will be written to the original data file because has more free space than the second data file. Source: Books Online file and filegroup fill strategy (found in Using Files and Filegroups).

Cobra-1032610 SSC Veteran Points: 239 More actions · Answer 5

Thanks for the reply!!

I agree that, "more new data added to the database will be written to the original data file because it has more free space than the second data file".

But this holds good if the original data file is still allowed to grow. What if the autogrowth option for the original data file has been stopped and only the secondary data file is allowed to grow?

Thanks in advance!!!

Sr SQL developer Default port Points: 1418 More actions · Answer 6

Based on BOL, I believe new data added to the database will be written to the original data file before the new data file grows if the original data file has free space and the new data file has no free space.