SQL Server space related question

Question

SQL Server space related question

Sam 1,476

Hi All,

I have a question about a disk space issue related to SQL Server. We have a 100 GB drive, with 90 GB being used space and 10 GB as free space. Within a span of two weeks, a log table has grown from 80 GB to 90 GB, but when we check the drive's free space, it still shows 10 GB available. I'm puzzled as I expected it to have filled the entire 100 GB drive.

Can anyone explain why we still see 90 GB used and only 10 GB free? Interested in any insights.

Regards,

Sam

Anonymous

2023-08-23T09:41:26.27+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.

Answer accepted by question author

0 additional answers

Your answer

Anonymous

2023-08-23T09:41:26.27+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.

Answer 1

Erland Sommarskog 129.6K MVP Volunteer Moderator

First question: is this table located on a filegroup that the file on this particular drive is part of? If the table is located elsewhere, there is no reason why this file would grow.

And if this table is located on a filegroup, this file is part of, how many other files are there in that filegroup? Space is allocated in a round-robin fashion.

But even if we assume that the table is located on a filegroup that is solely located on this drive, the file does not have to grow. If we assume that this table is the only one in this file and the table was 80GB and the file 90GB, there was room for 10GB more of data in the file. (But would the table continue to grow, we can expect the file to start growing and the amount of free space on the disk to decrease.)

Also, keep in mind that a table may be located on multiple filegroups:

The table may be partitioned.
Indexes may be placed on a different filegroup than the data pages.
LOB data may be on a separate file group.

Sam 1,476 Reputation points

2023-08-23T10:56:00+00:00

Yes sir., the table is part of file group.
Sam 1,476 Reputation points

2023-08-23T11:27:14.8566667+00:00

You mean to say, due to autogrowth the space has already been allocated and not been used yet? if so, how to check it ? any DMV ?
Christoph Muthmann 181 Reputation points

2023-08-23T12:48:37.2966667+00:00

Samantha, I recommend Glenn Berry's SQL Server Diagnostic Queries: https://glennsqlperformance.com/resources/

Try this one:

SELECT f.[name] AS [File Name] , f.physical_name AS [Physical Name],

CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],

CAST((f.size/128.0) AS DECIMAL(15,2)) -

CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))

AS [Used Space in MB],

CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))

AS [Available Space In MB],

f.[file_id], fg.name AS [Filegroup Name],

f.is_percent_growth, f.growth, fg.is_default, fg.is_read_only, fg.is_autogrow_all_files

FROM sys.database_files AS f WITH (NOLOCK)

LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)

ON f.data_space_id = fg.data_space_id

ORDER BY f.[type], f.[file_id] OPTION (RECOMPILE);
Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2023-08-23T20:55:38.83+00:00

You mean to say, due to autogrowth the space has already been allocated and not been used yet?

I mean that in the data file in question (if the table is located on this file) already had the size of 90 GB before the table started to grow, and there was empty space. If this was because the file had autogrown to that size, or because someone had pre-allocated the it is not easy to tell.

if so, how to check it ? any DMV ?

Autogrowth events are captured by the default trace. (And I think manual growth is too.) However, with five rollover files, data ages out fast from this trace.
Sam 1,476 Reputation points

2023-08-24T08:14:15.5533333+00:00

Thank Sir.

Share via

SQL Server space related question

0 additional answers

Your answer