I have a vendor application database with 700 tables. The data hasn't ever been archived and dates back to 2006. The business just wants all that data.
My question is as I am monitoring the size/growth of the database, I am noticing a trend where the database is growing but it has free space.
The database is 187GB, has 40GB free and the backup size is 146GB.
The database is growing by 1GB autogrow increment which is should not need to do if it has free space it can use, right?
I have looked at fill_factor as I wasn't sure what the vendor was doing, and at the database level it remains at the 0 default indicating the pages will be filled to 100%. There are some objects, indexes mostly, that have a 80% or 85% fill factor.
I am stumped.
I am re-indexing and the disk fragmentation reports are coming back with little or no fragmentation.
Got any ideas?
Best Answer
A couple of things worth noting here.
First, the database will grow whenever it needs the space to complete a transaction. I wrote about this here. If you performing maintenance on your database (say, rebuilding indexes) and you hit a large enough table then you could see a growth event happen as a result, even though it appears you have a large amount of free space.
Second, you can examine you default trace files in order to find out when your autogrowth events are happening. Often times you will find that the timing of the growth events coincides with maintenance, or perhaps a batch load of some kind.
HTH