Using the Utility Control Point to track the log file growth

Recently we have moved a SQL Server to a different hardware. The existing Sql Server version is 2008 R2 and the server is
registered a to Utility Control Point server running separate Instance.

As usual in new hardware we keep all log file in separate drive with Raid 10 configuration.
To figure out the how large the log drive should in order to avoid run out disk space error we used
UCP for historical log file size data.

During this historical data period several process or job running to server may have increased the log file size
so the information we had was fair enough to justified the log file growth in future.

We can use the Utility control GUI tool to find out the largest log file size but if we have too many databases in
one server like in share environment it wouldn’t be a feasible in some cases.

But using the following query can find the historical log file size for databases to a specific or all instances.

I also have added the minimum processing date for log file when maximum size have reached for specific databases
in the query. The reason I add this date to the query that if any database existing log file size is quite smaller
compare to maximum size we can go back to that date to find out what was running on that date and this can help us
to figure out future log file size growth if that process run again so we don’t have to face run out of disk space issue.

use sysutility_mdw — Change with your UCP database name
go

WITH MaxTranUsages
(processing_time,database_name,dbfile_name,used_space_bytes,allocated_space_bytes,RowNumber) AS
(
select
processing_time,database_name,dbfile_name
,used_space_bytes
,allocated_space_bytes
,ROW_NUMBER() over (partition by database_name
order by allocated_space_bytes desc,processing_time) RowNumber

from sysutility_ucp_core.space_utilization_internal
where server_instance_name = ‘Your Server Instance Name’
— Ignore this line if all instance are use
and object_type = 7 — Use any or all object Type depending on Report

—- Object_type — 7 Log File
—- Object_type — 6 Data File
—- Object_type — 4 Database
)

select
database_name,CONVERT(VARCHAR(20), processing_time, 100) as processing_time,dbfile_name

,CAST((used_space_bytes/(1024*1024)) as numeric(10,2)) as used_space_Mbytes
,CAST((allocated_space_bytes/(1024*1024)) as numeric(10,2)) as allocated_space_Mbytes

,RowNumber
from MaxTranUsages
where RowNumber = 1
order by database_name