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 query on prtg gives another result than on sql management studio

Votes:

0

I created an sql query that works on my server when using SQL management studio.

Easy query that returns % free space in the LOG db:

#select
#
#( (SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,'SpaceUsed') AS INT))/128.0)/(sum(size)/128.0)  )*100 as Log_Pct_Free
#from sys.database_files  where type=1 group by type

I added this query on C:\Program Files (x86)\PRTG Remote Probe\Custom Sensors\sql\mssql

and created an microsoft sql sensor with option process data table on, and alert at 5% free space.

the free space would for example be 70% on the actual server but PRTG will always return a value between 98 and 99%.

Could I know how the sensor executes the query to try and find the issue?

mssql mysql-v2-sensor prtg query sql

Created on Jan 4, 2019 1:28:59 PM

Last change on Jan 7, 2019 7:40:35 PM by Stephan Linke [Paessler Support]



3 Replies

Accepted Answer

Votes:

1

Nice little script..

You only have one issue there - on which database do you actually execute it?

I tried this in SSMS (SQL Server Management Studio) and every time I switch the DB I execute it on I get a different result, relative to the currently selected DB.

In other words, the function SYS.DATABASE_FILE (https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-2017) will only give you information about the files in your current database, as it is defined.

There is a theoretical way around this to switch throughout all your databases.

I want to add as well that in theory you risk that if you have more then 1x logfiles you might get multiple rows.

To give you a solution - just adjust the database in the PRTG sensor to the database the script should run on. Try the script on the same database in SSMS and compare the results again.

Here is you script in a more pretty version (due to this forum):

select
((SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,'SpaceUsed') AS INT))/128.0)/(sum(size)/128.0))*100 as Log_Pct_Free
from sys.database_files where type=1 group by type

Regards

Florian Rossmark

www.it-admins.com

Created on Jan 7, 2019 2:47:10 PM



Votes:

0

Thanks for assisting, Flo! :)

PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 7, 2019 7:41:34 PM by Stephan Linke [Paessler Support]



Votes:

0

I presumed that this was already done by entering the database parameter in the microsoft sql sensor. I'll try the query again by adding my own parameter to the script.

Created on Jan 8, 2019 7:20:26 AM




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.