2

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?

Improve this question
1
  • Does your database have mulitple file groups? If so, perhaps one of them is growing an another isn't. this would explain the static free space and growth of the database.
    – Jeremy Lowell
    Nov 16, 2012 at 21:20

2 Answers 2

Reset to default
3

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

Improve this answer
2
  • I have already examined the default trace files and it appears that the data file has only autogrown 1 time in the last month. So, my issue is not that it is growing, my concern is that it isn't using the free space it already has before growing. If it doesn't use the 40ish GB it has, I am going to have to add space, but being respoinsible, I don't want to add space if something is wrong here. All my other databases use free space first....this is weird. I must be missing something unique about this database.
    – Donna
    Nov 19, 2012 at 16:11
  • When I look at the Disk Usage report, I see a huge amount of space labeled - unallocated. According to the MSDN site, this is space available for use. I am associating this space with free space. Dang it, why won't it allocate it then.
    – Donna
    Nov 19, 2012 at 16:31
1

Look at the free space on your transaction log file, and each filegroup. It could be that you have certain tables/indexes/etc. going into a certain filegroup and that one has free space, but other ones do not.

This query was copied from http://beyondrelational.com/modules/2/blogs/104/posts/11740/quick-way-to-find-the-free-space-on-each-filegroup-within-a-database.aspx

SELECT
    b.groupname AS 'File Group',
    Name, 
    [Filename],
    CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)) 
        [Currently Allocated Space (MB)],
    CONVERT (Decimal(15,2),
    ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))
        AS [Space Used (MB)],
    CONVERT (Decimal(15,2),
    ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))
        AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname

This one from Eric Johnson is good too: http://web.archive.org/web/20100116013910/http://sqlblog.com/blogs/eric_johnson/archive/2009/08/07/file-and-filegroup-space-details.aspx

Improve this answer
8
  • Thanks for the response. There is only 1 filegroup and the free space is all in the same file group.
    – Donna
    Nov 16, 2012 at 23:38
  • interesting, can you post your results from running sp_helpfile and also the query listed above? Thanks!
    – Ali Razeghi - AWS
    Nov 16, 2012 at 23:53
  • As requested, the results of the query above are:File Group Currently Allocated Space (MB) Space Used (MB) Available Space (MB) PRIMARY 192066.81 150394.63 41672.19
    – Donna
    Nov 19, 2012 at 16:03
  • Here is the sp_helpfile output.
    – Donna
    Nov 19, 2012 at 16:05
  • 1
    I think I figured it out with the help of Brent Ozar's contact named Jeremiah. I found that the only time the data file was growing over the last 2 month's was during the re-indexing job. I am 'not' using sort-in-tempdb which means SQL will use space in the data file to complete the re-index process and since some of those indexes are rather large, it exceeds the 40GB free.
    – Donna
    Nov 19, 2012 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.