Finding Free Space

Question

Finding Free Space

Steve Jones - SSC Editor

SSC Guru

Points: 736222
More actions
November 21, 2019 at 12:00 am

#3693979

Comments posted to this topic are about the item Finding Free Space

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72626 More actions · Answer 1

Interesting question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

marsigme SSCrazy Points: 2848 More actions · Answer 2

The question ask for the amount of free space, correct? Wouldn't that equate to a formula of Capacity - SpaceUsed yields FreeSpace? Really didn't think any of the answers were complete since I thought you needed to use the size from sys.database_files and the SpaceUsed from FileProperty to formulate the answer as shown in this sample from the sys.database_files doc:

SELECT 
    name, 
    size/128.0 FileSizeInMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS EmptySpaceInMB 
FROM sys.database_files;

Code sample extracted from https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver15

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

marsigme wrote:

The question ask for the amount of free space, correct? ... Really didn't think any of the answers were complete since ... you needed to use the size from sys.database_files and the SpaceUsed from FileProperty to formulate the answer ...

+1.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

George Vobr SSCrazy Eights Points: 9997 More actions · Answer 4

Today I will not answer, I tried to calculate the free space only from sys.database_files as Free_space = max_size - size,

but it is necessary to treat the value max_size -1 when the file will grow until the disk is full.

A simple calculation is in the above post from marsigme. A good calculation is also here: SQL SERVER - How Much Free Space I Have in My Database?

This article uses the system table dbo.sysfiles instead of the sys.database_files system view. Therefore, I prefer to add a reference: Mapping System Tables to System Views (Transact-SQL).

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 5

Apologies, poorly worded question. This should have been amount of used space, not free space.