Support Migration Notice: To update migrated JIRA cases click here to open a new case use www.vmware.com/go/sr | vFabric Hyperic 5.7.0 is Now Available

Hyperic HQ

SQL Server Unallocated Database Space Metric

Details

  • Type: New Feature New Feature
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: 4.6
  • Component/s: None
  • Case Links:
    none
  • Regression:
    No

Description

On-site with a customer, I was asked if we support the following:

SQL Server database size monitoring. Right now, we collect "Data File Size" but that's not very useful. SQL Server DBAs really want to know how much of the max database file size is allocated/used. There is no way to get this from perflib but the stored procedure listed below returns the percentage of unallocated database space. It would be really nice if this metric was available in Hyperic.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
IF OBJECT_ID('tempdb..#tTables') IS NOT NULL DROP TABLE #tTables
create table #tTables
(
numID INTEGER IDENTITY(1,1),
strTableName sysname,
db_sizes bigint,
unalloc_sizes bigint
)

EXEC sp_MSforeachdb
'USE [?]
declare @dbname sysname
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @dbmaxsize dec(15,0)

SET @dbname = db_name()

select @bytesperpage = low from master.dbo.spt_values where number = 1
and type = ''E''

select @pagesperMB = 1048576 / @bytesperpage

select @dbmaxsize = min(maxsize) from dbo.sysfiles where (status & 64 = 0)

if @dbmaxsize <> -1
begin
select @dbmaxsize = sum(convert(dec(15),maxsize)) from dbo.sysfiles
where (status & 64 = 0)
end

insert into #tTables( strTableName, db_sizes, unalloc_sizes)
select
db_name(),
database_size = (case @dbmaxsize
when -1 then -1
else
cast( ((@dbmaxsize / @pagesperMB)*1024) as bigint )
end),
unallocated_space = cast((((@dbmaxsize -
(select sum(convert(dec(15),reserved) )
from sysindexes
where indid in (0, 1, 255))) / @pagesperMB )*1024) as bigint )
where db_name() not in ("master","model","msdb","tempdb","northwind","pub")'

select strTableName "DBName",
(case db_sizes when -1 then 100 else CAST(ROUND(100.0*unalloc_sizes/db_sizes, 0) AS INT) end) "DatabaseFreePct" from #tTables

drop table #tTables

Activity

Hide
Ryan Morgan added a comment -

Make issue viewable by Hyperic Members group only. James, please refrain from using customer names in HHQ tickets, this is the public open source bug system.

Show
Ryan Morgan added a comment - Make issue viewable by Hyperic Members group only. James, please refrain from using customer names in HHQ tickets, this is the public open source bug system.
Hide
James Williams added a comment -

I attached a patch file and the actual source files I used to generate this metric for SQL Server 2008 on HQ 4.2.0.

Please note that the original SQL file listed in the feature request comment does NOT return a good value for free db space.

James

Show
James Williams added a comment - I attached a patch file and the actual source files I used to generate this metric for SQL Server 2008 on HQ 4.2.0. Please note that the original SQL file listed in the feature request comment does NOT return a good value for free db space. James
Hide
Jason Konicki added a comment -

Attaching the compiled plugin to the ticket for future reference.

Show
Jason Konicki added a comment - Attaching the compiled plugin to the ticket for future reference.

People

Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved:
    Last comment:
    3 years, 15 weeks, 6 days ago