September 13, 2011 at 7:32 am
We have some internal space reports that report on the free space available within each data file for each database. Some of the files are always full, so they always show up on the reports. It ends up being a bit of a false positive. My question is, if a database has many files associated with it, why should we care about file free space, rather than database free space? I've always worried about database free space and if that was running low, then I'd dig into the file details. This is a different approach and I'm hoping someone out there might be able to share why this way might be better.
thanks....
September 13, 2011 at 7:45 am
Space in tables and indexes is allocated to filegroups, so it there is no available space in a specific file group, then no data can be added, even if files in other filegroups have plenty of space.
September 13, 2011 at 7:48 am
Here is a script by Michael that will help you get exact space details from each file of a database.
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a
September 13, 2011 at 7:59 am
Michael Valentine Jones (9/13/2011)
Space in tables and indexes is allocated to filegroups, so it there is no available space in a specific file group, then no data can be added, even if files in other filegroups have plenty of space.
Great point. I've only been on the job here about a week, so I'm still learning the systems. It appears as though all the databases only use the primary file group. When they need more space and they can't extent an existing file, they add a new one of a different drive. And from what I've been told, indexing on these systems are minimal.
I ran the query you provided and it looks like it gives more accurate information than what the reports have been generating. If I go by those, all my databases are out of space. I think I need to dig into that code some more. I don't want to chase problems that don't exist.
September 13, 2011 at 8:05 am
pveilleux (9/13/2011)
appears as though all the databases only use the primary file group. When they need more space and they can't extent an existing file, they add a new one of a different drive.
All files belonging to a particular filegroup are propotionately filled. If one file in a filegroup is 100% full and another one (newly added file) is 100% free, you cannot insert data to any table belonging to this filegroup.
Adding a filegroup to different drive and then moving some tables to that is a possible option.
Edit - added the text (newly added file)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply