alchemistmatt (4/18/2013)
Looks like I could slow the growth rate by decreasing the sampling frequency of the file stats. The other thing I'll need to do is delete old data after a while; e.g. delete data over 1 year old.
I changed the dba_CheckFiles job to run every 8 hours instead of every hour. In addition, I tweaked the Sql in rpt_HealthReport to properly compare the latest stats vs. the stats from 24 hours ago:
-- Find the FileStatsDateStamp that corresponds to 24 hours before the most recent entry in FileStatsHistory
-- Note that we use 1470 instead of 1440 to allow for the entry from 24 hours ago to be slightly more than 24 hours old
SELECT @MaxFileStatsDateStamp = MAX(FileStatsDateStamp) FROM [dba].dbo.FileStatsHistory
SELECT @MinFileStatsDateStamp = MIN(FileStatsDateStamp) FROM [dba].dbo.FileStatsHistory WHERE FileStatsDateStamp >= DateAdd(minute, -1470, @MaxFileStatsDateStamp)
IF @MinFileStatsDateStamp IS NOT NULL
BEGIN
-- Update the stats in #FILESTATS to reflect the change over the last 24 hours
-- (instead of the change since the Sql Server service last started)
--
...
END