Red Gate forums :: View topic - Analysis - Data file used space
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Analysis - Data file used space

 Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Andrew Hodge



Joined: 19 Jul 2007
Posts: 90
Location: Kent, UK
PostPosted: Tue Apr 10, 2012 10:32 am    Post subject: Analysis - Data file used space Reply with quote
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
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 300
Location: Red Gate - Cambridge
PostPosted: Tue Apr 10, 2012 1:57 pm    Post subject: Reply with quote
Hi Andrew

I've raised this as an enhancement request (ref: SRP-6485).

Thank you for the feedback.

Regards
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
Back to top
View user's profile Send private message
EdCarden



Joined: 25 Nov 2008
Posts: 68
PostPosted: Fri Apr 13, 2012 5:10 pm    Post subject: Re: Analysis - Data file used space Reply with quote
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.

Code:
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],
       [Name],
       (TotalExtents*64)/1024.0 as [TotalMB],
        (UsedExtents*64)/1024.0 as [UsedMB]
       
from #data
union all
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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group