Report to predict when a drive will run out of space.

This is one already, but it can only run on one server at a time.  Imagine if you have 50 production servers?   I have worked at some places that have hundreds.  It would take a long time to run it on each server.  Can you imagine trying to pick from a drop down that has that many servers?

How about a report that will take a tag so you can run against all servers that you tagged as PRODUCTION?  Below is a sample query.

set rowcount 0


declare @TagToLookFor varchar(100); set @TagToLookFor = 'PROD RiskQuality';
declare @SvrList table( SvrName varchar(300), SvrID int )


insert into @SvrList( SvrName, SvrID )
select
mm.InstanceName
,mm.[SQLServerID]
from SQLdmRepository.dbo.Tags as tt with(nolock)
inner join SQLdmRepository.dbo.ServerTags as st with(nolock) on st.TagId = tt.Id
inner join SQLdmRepository.dbo.MonitoredSQLServers as mm with(nolock) on mm.SQLServerID = st.SQLServerId
where tt.Name = @TagToLookFor
--select * from @SvrList


--declare @ServerID int;
declare @UTCStart DateTime;
declare @UTCEnd DateTime;
declare @UTCOffset int; set @UTCOffset = 240;
declare @Interval tinyint; set @Interval = 2; --2=day
declare @DatabaseXML nvarchar(4000); set @DatabaseXML=N'<Databases><Database DatabaseName="AlertReference" /><Database DatabaseName="BCBSM_AlertDB" /><Database DatabaseName="BCBSNE_AlertDB" /><Database DatabaseName="DataLoaderBCBSM" /><Database DatabaseName="DataLoaderBCBSM_02282020" /><Database DatabaseName="DataLoaderBCBSM_03062020" /><Database DatabaseName="DataLoaderBCBSM_03212020" /><Database DatabaseName="DataLoaderBCBSM_04172020" /><Database DatabaseName="DataLoaderBCBSM_12262019" /><Database DatabaseName="DataLoaderBCBSM_Old" /></Databases>'
declare @DriveName nvarchar(256); set @DriveName = 'C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z';

select @UTCStart = dateadd(day,-30,getUTCdate())
select @UTCEnd = getUTCdate()

SET NOCOUNT ON;
declare @xmlDoc int
set ansi_warnings off

declare @Databases table(
DatabaseName nvarchar(255)
)

-- Prepare XML document
exec sp_xml_preparedocument @xmlDoc output, @DatabaseXML

-- Extract the server IDs from the SML doc.
insert into @Databases
select
DatabaseName
from openxml(@xmlDoc, '//Database', 1)
with (DatabaseName nvarchar(255))

exec sp_xml_removedocument @xmlDoc

if (select isnull(object_id('tempdb..#DS'), 0)) <> 0
begin
drop table #DS
end

SELECT * INTO #DS FROM
(
select
InstanceName
,[DatabaseName]
,dbo.fn_RoundDateTime(@Interval, max(dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime]))) as [LastCollectioninInterval]
,(sum(DataFileSizeInKilobytes * TimeDeltaInSeconds) / nullif(sum(TimeDeltaInSeconds),0)) / 1024 as DataFileSizeMb
,(sum((DataSizeInKilobytes + IndexSizeInKilobytes + TextSizeInKilobytes)* TimeDeltaInSeconds) / nullif(sum(TimeDeltaInSeconds),0)) / 1024 as TotalSizeMb
,(sum((DataFileSizeInKilobytes + DataExpansionInKilobytes) * TimeDeltaInSeconds) / nullif(sum(TimeDeltaInSeconds),0)) / 1024 as AvailableDataSizeMb
,0 as ForecastFileSize
,0 as ForecastDBSize
from [SQLServerDatabaseNames] names with(nolock)
inner join SQLdmRepository.dbo.MonitoredSQLServers ss with(nolock) on ss.SQLServerID = names.SQLServerID
left join [DatabaseSize] ds with(nolock) on names.[DatabaseID] = ds.[DatabaseID]
-- Filter for SQL Server
where names.[SQLServerID] in ( select SvrID from @SvrList)
-- Filter databases
and names.[DatabaseName] collate database_default in (select DatabaseName collate database_default from @Databases)
and dbo.fn_RoundDateTime(@Interval, ds.[UTCCollectionDateTime]) BETWEEN @UTCStart and @UTCEnd
group by InstanceName, [DatabaseName]
-- Always group by year at the least
,datepart(yy, dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime]))
-- Group by all intervals greater than or equal to the selected interval
,case when @Interval <= 3 then datepart(mm,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) end
,case when @Interval <= 2 then datepart(dd,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) end
,case when @Interval <= 1 then datepart(hh,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) end
,case when @Interval = 0 then datepart(mi,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[UTCCollectionDateTime])) end

UNION

SELECT
InstanceName
,[DatabaseName]
,dbo.fn_RoundDateTime(@Interval, max(dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime]))) AS [LastCollectioninInterval]
,(sum(TotalDataFileSizeInKilobytes * TotalTimeDeltaInSeconds) / nullif(sum(TotalTimeDeltaInSeconds),0)) / 1024 as DataFileSizeMb
,(sum((TotalDataSizeInKilobytes + TotalIndexSizeInKilobytes + TotalTextSizeInKilobytes)* TotalTimeDeltaInSeconds) / nullif(sum(TotalTimeDeltaInSeconds),0)) / 1024 as TotalSizeMb
,(sum((TotalDataFileSizeInKilobytes + TotalDataExpansionInKilobytes) * TotalTimeDeltaInSeconds) / nullif(sum(TotalTimeDeltaInSeconds),0)) / 1024 as AvailableDataSizeMb
,0 as ForecastFileSize
,0 as ForecastDBSize
from [SQLServerDatabaseNames] names with(nolock)
inner join SQLdmRepository.dbo.MonitoredSQLServers ss with(nolock) on ss.SQLServerID = names.SQLServerID
left join [DatabaseSizeAggregation] ds with(nolock) on names.[DatabaseID] = ds.[DatabaseID]
-- Filter for SQL Server
where names.[SQLServerID] in ( select SvrID from @SvrList)
-- Filter databases
and names.[DatabaseName] collate database_default in (select DatabaseName collate database_default from @Databases)
and dbo.fn_RoundDateTime(@Interval, ds.[MaxUTCCollectionDateTime]) BETWEEN @UTCStart and @UTCEnd
group by InstanceName, [DatabaseName]
-- Always group by year at the least
,datepart(yy, dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime]))
-- Group by all intervals greater than or equal to the selected interval
,case when @Interval <= 3 then datepart(mm,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) end
,case when @Interval <= 2 then datepart(dd,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) end
,case when @Interval <= 1 then datepart(hh,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) end
,case when @Interval = 0 then datepart(mi,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) else datepart(yy,dateadd(mi, @UTCOffset, ds.[MaxUTCCollectionDateTime])) end

) AS DSAggregation
order by InstanceName, DatabaseName ,[LastCollectioninInterval];


SELECT
InstanceName
,[DatabaseName]
,[LastCollectioninInterval]
,[DataFileSizeMb]
,[TotalSizeMb]
,[AvailableDataSizeMb]
,[ForecastFileSize]
,[ForecastDBSize]
FROM #DS
ORDER BY InstanceName, DatabaseName ,[LastCollectioninInterval] asc;

BEGIN
drop table #DS
END

Parents
No Data
Reply Children
No Data