0

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.