Joined: 25 Nov 2008
|Posted: Fri Apr 13, 2012 5:10 pm Post subject: Re: Analysis - Data file used space
|Andrew Hodge wrote:
|Just looking through the analysis section on monitor 3 and have noticed that there isnt a data file space used (equivelent to the log space used).
The data size just returns the size of the mdf files which isnt very useful.
I take it we could develop this within the custom reports but think this should probably be included in the base reports
Out of curiosity why are you wanting an analysis of %File Used on the Data file?
BTW - You can write a custom alert/mertic in version 3.x to catch a change in the percent used value of your database file. The below T-SQL will give you the values for the data and log files which you can then assign to an Alert and then monitor it for when the Percent Used value exceeds some designated value.
|create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)
create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
insert #data exec('DBCC showfilestats with no_infomsgs')
insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')
WITH CTE_X AS
select 'DATA' as [Type],
(TotalExtents*64)/1024.0 as [TotalMB],
(UsedExtents*64)/1024.0 as [UsedMB]
select 'LOG', db_name() + ' LOG', LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()
select t.type, t.name, t.totalMB, t.UsedMB, convert(numeric(5,2),(t.usedMB/t.totalMB)*100) AS [PercentUsed]
from CTE_X t
drop table #data
drop table #log