Sql-server – Can SQL Server 2014 server reported drive space properties change overnight when database growth is set to Unlimited

sql server 2014windows-server

Our SQL Server 2014 server is running on Windows Server 2012 R2. The drive space properties reported by the OS shows that we had 17 GB free one day, and the next day it shows we have only 718 MB free. No particular queries nor SQL Jobs were ran during this time. The database maxsize is set to Unlimited (growth is set to 10%), and executing this query:

SELECT (
           SELECT SUM(CAST(df.size AS FLOAT))
           FROM   sys.database_files AS df
           WHERE  df.type IN (0, 2, 4)
       )                   AS [DbSize],
       SUM(a.total_pages)  AS [SpaceUsed],
       (
           SELECT SUM(CAST(df.size AS FLOAT))
           FROM   sys.database_files AS df
           WHERE  df.type IN (1, 3)
       )                   AS [LogSize]
FROM   sys.partitions p
       JOIN sys.allocation_units a
            ON  p.partition_id = a.container_id
       LEFT JOIN sys.internal_tables it
            ON  p.object_id = it.object_id

…to get the DB size vs space used, shows we are actually 12 GB below the current database size.

So is it possible that Windows suddenly updated the reported space available, taking into account the unused portion of space?

Best Answer

I ran the following query accessing the Default Trace log to find when the database did an Autogrow:

DECLARE @path NVARCHAR(260);
SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1
SELECT 
   DatabaseName,
   HostName,
   ApplicationName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
ORDER BY StartTime DESC

This showed the database autogrew by 10% during the time the change in drive space was noticed. So the database simply did what it was supposed to do: grow by 10% when it was needed.

Related Question