--==============================================================
--CODE TO PERSIST THE DATA (Daily In This Case)
--==============================================================
--==============================================================
--Script Author:  Timothy Ford aka SQLAgentMan
-- http://thesqlagentman.com
-- Can be used for personal use; credit must be given to author
-- Code can not be used for commercial use, repackaged, resold without permission
--==============================================================

/*
AUTHORS NOTE:
This runs via an SSIS process I've instituted to collect metadata from all instances I monitor.
If you're interested in doing so I suggest reading Rodney Landrum's DBA Toolbox book
from Red Gate Press.

The code here only runs locally against non-persisted data - just the temp table

Use Ctl+Shift+M to replace the template parameter for threshold with a value of your choosing.  Default is 85%
*/

EXEC iDBA.dbo.usp_database_file_snapshot;

--==============================================================
-- Relies upon previously creating the iDBA.dbo.usp_database_file_snapshot stored procedure
--==============================================================
SELECT [Server], [DatabaseName], [Name], [Filename], 
	[FileType], [Size_In_Mb] AS [File Size (Mb)], [Available_Space_In_Mb] AS [Free Space (Mb)], 
	[Max_File_Size_In_Mb] AS [Max File Size (Mb)], ([Size_In_Mb] - [Available_Space_In_Mb]) AS [Space Used (Mb)],
	CAST([Growth_Increments] AS varchar(10)) + ' ' + [Growth_Units] AS [Growth Increment],  
	CAST((([Size_In_Mb] - [Available_Space_In_Mb])*1.0) / [Size_In_Mb] AS decimal(5,2)) AS [% Consumed],
	[date_stamp] 
FROM [tempdb].[dbo].[RESULTS_FILE_HISTORY] 
WHERE (([Size_In_Mb] - [Available_Space_In_Mb])*1.0) / [Size_In_Mb] >= <low_threshold,decimal(3,2),0.85>
ORDER BY [Server], [DatabaseName];