This article applies as of PRTG 22

I have a Microsoft SQL Server 2008 used for SharePoint, and I need to monitor the database size. How can I do this without using WMI, as UNC paths are not allowed and mapping drives for all databases is impractical?

Monitoring a Microsoft SQL database

PRTG comes with a Microsoft SQL v2 sensor. It executes a defined query, shows you several types of query execution times, the number of addressed rows, and defined values of the monitored Microsoft SQL database.

If you want to monitor the current file size, used space, and free space (in bytes and percent), you need to provide the query below for the Microsoft SQL v2 sensor.

Follow the steps below to set up your Microsoft SQL v2 sensor:

Requirements

• Microsoft SQL sensors in a PRTG installation generally need to be able to work against the actual target host.
• Make sure that your database's autogrowth feature is disabled. Otherwise, you need to configure the limits accordingly in your PRTG settings.

The Microsoft SQL query

-- ___ ___ _____ ___
--| _ \ _ \_   _/ __|
--|  _/   / | || (_ |
--|_| |_|_\ |_| \___|
--    NETWORK MONITOR
---------------------
-- [SQL] Database Size monitoring
---------------------
-- Description: This query will show the current size information for the given database
---------------------
-- Code courtesy of Tri Effendi SS of stack exchange
-- http://dba.stackexchange.com/a/7921
-- Original post: http://dba.stackexchange.com/a/7921
SELECT 
    [DESCRIPTION] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024
    ,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024
    ,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
WHERE A.type_desc LIKE 'ROWS'
order by A.TYPE desc, A.NAME;   

Version history

Sensor setup

1. Save the query above as PRTG-DatabaseSize.sql in %Program Files (x86)%\PRTG Network Monitor\Custom Sensors\SQL\mssql\.
2. In your PRTG installation, go to the Microsoft SQL device that you want to monitor and add a new Microsoft SQL v2 sensor.
3. In the sensor settings, configure the sensor in the following way:

Save your settings and start monitoring. If you encounter any bugs, feel free to share them.

Note: The query is provided as is and may or may not work with your installation. We cannot provide in-depth technical support for custom scripts.

More

• How to set up the SQL v2 sensors in PRTG? Is there a guide?
• How can I monitor strings from an SQL database and show a sensor status depending on it?
• How can I monitor error tables in SQL databases?
• PRTG Manual: Monitoring Databases