Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '12 at 21:20
add comment

2 Answers

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://sqlblog.com/blogs/eric_johnson/archive/2009/08/07/file-and-filegroup-space-details.aspx

share|improve this answer
    
Thanks for the response. There is only 1 filegroup and the free space is all in the same file group. –  Donna Nov 16 '12 at 23:38
    
interesting, can you post your results from running sp_helpfile and also the query listed above? Thanks! –  Ali Razeghi Nov 16 '12 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 '12 at 16:03
    
Here is the sp_helpfile output. –  Donna Nov 19 '12 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 '12 at 20:29
show 3 more comments

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

share|improve this answer
    
Great answer, +1. –  Thomas Stringer Nov 17 '12 at 4:20
    
Thanks Thomas! +1 –  Ali Razeghi Nov 17 '12 at 4:30
    
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 '12 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 '12 at 16:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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