The size in and of itself is what you will need to appropriately determine when setting and growing. This is all part of properly sizing your database.
But you need to be careful with growing your files at such a small increment. If you are growing your files often, you are causing SQL Server to have to do this relatively expensive operation. Take, for instance, growing of the transaction log. When this happens all write activity will be impeded. Another thing about that is if you grow your transaction log at a small interval and it grows often, you will end up with a large amount of VLFs that are small in size.
Instant File Initialization is a great approach, as it bypasses the zero-initialization of the additional file (or file allocation). This shaves off a lot of the overhead and performance impact, but this is only possible with data files, not log files.
As you can see, there are a handful of things to consider depending on your environment and how much your database grows. Size your database appropriately and find the right growth increment.
Note, autogrowth should be reserved for emergency situations and it is recommended to manually grow your files if at all possible. This gives you full control over all of the points above, as well as allowing you to determine when this operation occurs.
As for your numbers, "space available" is just that: The free space that you have for additional data until growth will need to happen. One strategy is to get notified when you reach a certain threshold of used space so that you can schedule and kick off growth of the appropriate file(s).
Even though you fixed the immediate rounding issue, the overall algorithm to get per-object / index stats is incorrect. It does not properly handle LOB and row-overflow data. It also excludes: Indexed Views, FullText indexes, XML indexes, and a few other cases. Hence, you might not be seeing all of your data.
The following is an adaptation of the code I posted to an answer on StackOverflow ( sp_spaceused - How to measure the size in GB in all the tables in SQL ) that handles all of the cases that sp_spaceused handles. That S.O. question was only concerned with per-object stats, not per index, so I have adjusted the code to handle things at the index level.
;WITH agg AS
( -- Get info for Tables, Indexed Views, etc
SELECT ps.[object_id] AS [ObjectID],
ps.index_id AS [IndexID],
NULL AS [ParentIndexID],
NULL AS [PassThroughIndexName],
NULL AS [PassThroughIndexType],
SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
SUM(ps.used_page_count) AS [UsedPageCount],
SUM(ps.reserved_page_count) AS [ReservedPageCount],
SUM(ps.row_count) AS [RowCount],
SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
AS [LobAndRowOverflowUsedPageCount]
FROM sys.dm_db_partition_stats ps
GROUP BY ps.[object_id],
ps.[index_id]
UNION ALL
-- Get info for FullText indexes, XML indexes, Spatial indexes, etc
SELECT sit.[parent_id] AS [ObjectID],
sit.[object_id] AS [IndexID],
sit.[parent_minor_id] AS [ParentIndexID],
sit.[name] AS [PassThroughIndexName],
sit.[internal_type_desc] AS [PassThroughIndexType],
0 AS [InRowDataPageCount],
SUM(ps.used_page_count) AS [UsedPageCount],
SUM(ps.reserved_page_count) AS [ReservedPageCount],
0 AS [RowCount],
0 AS [LobAndRowOverflowUsedPageCount]
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables sit
ON sit.[object_id] = ps.[object_id]
WHERE sit.internal_type IN
(202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
GROUP BY sit.[parent_id],
sit.[object_id],
sit.[parent_minor_id],
sit.[name],
sit.[internal_type_desc]
), spaceused AS
(
SELECT agg.[ObjectID],
agg.[IndexID],
agg.[ParentIndexID],
agg.[PassThroughIndexName],
agg.[PassThroughIndexType],
OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
OBJECT_NAME(agg.[ObjectID]) AS [TableName],
SUM(CASE
WHEN (agg.IndexID < 2) THEN agg.[RowCount]
ELSE 0
END) AS [Rows],
SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
SUM(agg.LobAndRowOverflowUsedPageCount +
CASE
WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
ELSE 0
END) * 8 AS [DataKB],
SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
CASE
WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
ELSE 0
END) * 8 AS [IndexKB],
SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM agg
GROUP BY agg.[ObjectID],
agg.[IndexID],
agg.[ParentIndexID],
agg.[PassThroughIndexName],
agg.[PassThroughIndexType],
OBJECT_SCHEMA_NAME(agg.[ObjectID]),
OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
sp.TableName,
sp.IndexID,
CASE
WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName])
ELSE N'<Heap>'
END AS [IndexName],
sp.[PassThroughIndexName] AS [InternalTableName],
sp.[Rows],
sp.ReservedKB,
(sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
sp.DataKB,
(sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
sp.IndexKB,
(sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
sp.UsedKB AS [UsedKB],
(sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
sp.UnusedKB,
(sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
so.[type_desc] AS [ObjectType],
COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
sp.[PassThroughIndexType] AS [IndexSecondaryType],
SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
sp.ObjectID
--,sp.ParentIndexID
FROM spaceused sp
INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed
ON so.[object_id] = sp.ObjectID
LEFT JOIN sys.indexes si
ON si.[object_id] = sp.ObjectID
AND (si.[index_id] = sp.IndexID
OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N'' -- optional name filter
--ORDER BY ????
Best Answer
Properties dialog shows data and log file size. It's the same as
sp_spaceusedwithout any arguments. Usesp_spaceused tablereturns size of the table (i.e., part of data file) - it never takes into account transaction log - that's the missing part in the discrepancy you see.