November 21, 2019 at 12:00 am
Comments posted to this topic are about the item Finding Free Space
November 21, 2019 at 8:52 am
Interesting question, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
November 21, 2019 at 4:15 pm
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
November 21, 2019 at 4:35 pm
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".
November 21, 2019 at 9:29 pm
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).
November 22, 2019 at 3:40 pm
Apologies, poorly worded question. This should have been amount of used space, not free space.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply