What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

SQL Monitoring Query database space

Votes:

0

Hi All,

I am looking to monitor the Database data file space on all my databases using PRTG with the below query

SELECT 
    [FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.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; 

I have setup the channel to read data from columns 3,4,5 and 6

The problem I have is that I have 20 + database on the server alone and over 70 SQL servers so is there a way to have this dynamically create a sensor for each database on each server

database microsoftsql monitoring

Created on Jul 6, 2018 10:45:16 AM

Last change on Jul 6, 2018 12:09:18 PM by  Luciano Lingnau [Paessler]



1 Reply

Votes:

0

Hi there,

The script should help you to accomplish that:

Created on Jul 9, 2018 1:08:37 PM by  Dariusz Gorka [Paessler Support]




Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.