MOM is always complaining that it does not have enough space in the database, but this seems inconsistent with the actual space being used in SQL.  Increasing the SQL DB size sorts the problem, but MOM seems to warn long before it is supposed to.

For instance, we currently have the following alert:

Severity: Error
Status: New
Source: SQL Database: % Free Space: OnePoint SQL Free Space
Name: Operations Manager Database free data space fell below 30%
Description: SQL Database: % Free Space: OnePoint SQL Free Space value =29.55

Now in SQL Enterprise Manager, it seems as if 3,400 MB DB size, and the space available is 1,247.74 which by my calculation means 36.7 MB free.

So my question is, how does MOM work out the DB size (seems to be a Perfmon counter, but I can’t find it) and why does it think I only have 29%?

Contributed By: Ugo Corti
There is a SQL Store Procedure (sp_OnePointFreeSpace) that calculates the % Free Disk Space for the OnePoint database.  There is a SQL job called OnePoint-Check Free Data Space that calls this Store Procedure.

Here is the Store Procedure below.

CREATE PROCEDURE sp_OnePointFreeSpace
AS
DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(255)
DECLARE @DBSpaceinMB float
DECLARE @LogSpaceinMB float
DECLARE @DBFreeSpaceinMB float
DECLARE @LogFreeSpaceinMB  float
DECLARE @Results nvarchar(255)

-- Create an object.
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to create SQLDMO Server
object', 0, 1) WITH LOG
    RETURN
END

--Assuming trusted connection
EXEC @hr = sp_OASetProperty @object,'LoginSecure', 'True'
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to set LoginSecure', 0, 1)
WITH LOG
    GOTO ExitProc
END


EXEC @hr = sp_OASetProperty @object,'AutoReconnect', 'False'
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to set AutoReconnect', 0,
1) WITH LOG

    GOTO ExitProc
END


EXEC @hr = sp_OAMethod @object, 'Connect("","","")'
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to Connect', 0, 1) WITH
LOG
    GOTO ExitProc
END

EXEC @hr = sp_OAGetProperty @object,'Databases("OnePoint").size',
@DBSpaceinMB OUT
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to get OnePoint Database
size', 0, 1) WITH LOG
    GOTO ExitProc
END

EXEC @hr = sp_OAGetProperty
@object,'Databases("OnePoint").SpaceAvailableInMB', @DBFreeSpaceinMB OUT
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to get OnePoint Database
available space', 0, 1) WITH LOG
    GOTO ExitProc
END

EXEC @hr = sp_OAGetProperty
@object,'Databases("OnePoint").TransactionLog.Size', @LogSpaceinMB OUT
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to get OnePoint
Transalction Log size', 0, 1) WITH LOG
    GOTO ExitProc
END

EXEC @hr = sp_OAGetProperty
@object,'Databases("OnePoint").TransactionLog.SpaceAvailableInMB',
@LogFreeSpaceinMB OUT
IF @hr <> 0
BEGIN
    RAISERROR('sp_OnePointFreeSpace error: Unable to get OnePoint
Transaction Log available space', 0, 1) WITH LOG
    GOTO ExitProc
END

--IF ( (@DBFreeSpaceinMB - @LogFreeSpaceinMB) / (@DBSpaceinMB -
@LogSpaceinMB) * 100 ) <= 40
--BEGIN
    SET @Results = 'OnePoint Free Data Space Percentage: ' +
convert(char(20), (@DBFreeSpaceinMB - @LogFreeSpaceinMB) / (@DBSpaceinMB -
@LogSpaceinMB) * 100)
    RAISERROR(@Results, 0, 1) WITH LOG
--END
RETURN

ExitProc:

-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
    SET @Results = 'sp_OnePointFreeSpace error: Unable to destroy
SQLDMO.SQLServer object'
    RAISERROR(@Results, 0, 1) WITH LOG
END


GO

Contributed By: Hal Sclater
I turns out that the SQL database was corrupt and was reporting the wrong size.  We ran the DBCC utility and it sorted it out.  Reported fine now.