CODEWITHSUNDEEP
sql-serverzabbix
sam2112
sam2112

Reputation: 19

How to monitor database file size using Zabbix

I need to be able to monitor database file space in Zabbix. I have tried out many of the templates for monitoring SQL Server and they don't seem to do what I need them to do. Essentially, I need to know when a database file or log file (.mdf or .ldf) for a particular database is within a certain percentage of being full.

Essentially I'm looking to:

  1. Discover all data and log files within each database on a server
  2. Create two items for each file, space used and maximum space (taking into account autogrowth)
  3. Create a trigger for each file that will alert me when a data or log file is within a certain percentage of being full (80% full warning, 90% critical as an example)

Using ODBC is not an option due to the size of our environment and network restrictions. I believe I need some type of discovery script using PowerShell, then another script to fetch the values for the items, but I'm not sure.

Upvotes: 1

Views: 2096

Answers (2)

NoWar
NoWar

Reputation: 37633

By https://postgres.ai/

You

How to get monitoring postgree database size with zabbix?

Postgres.AI

Zabbix is a widely used open-source monitoring system. While it doesn't have Postgres-specific features for monitoring database size directly, you can leverage its capabilities to monitor the size of your Postgres database.

You can achieve this by creating a custom Zabbix item that executes a SQL query to fetch the database size and then processes the output.

For instance, you can use the following query to retrieve the size of your database:

select pg_database_size('your_database_name')

This query will return the size of the database in bytes. You can then configure Zabbix to execute this query periodically and collect the data.

Remember to replace 'your_database_name' with the actual name of your database.

Let me know if you have any other questions.

UPDATE:

I just configured Zabbix to display my Postgres database size using this manual.

enter image description here

enter image description here

enter image description here

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

No idea what Zabbix is (I'll take a peek later), but in SQL Server, you have sp_MSforeachdb

Here we create a #Temp table to collect the results from each database on the server

Example

Use Master;
Create table #Temp (DBName varchar(150),FileType varchar(50),MBytes bigint,MBytesMax bigint)

EXEC sp_MSforeachdb '
 Insert Into #Temp
 Select DBName    = ''?''
       ,FileType  = case when physical_name like ''%.mdf'' then ''Database'' else ''Log'' end
       ,MBytes    = try_convert(bigint,size) * 8 / 1024   
       ,MBytesMax = try_convert(bigint,max_size) * 8 / 1024   
  From  [?].sys.database_files 
'

Select *
      ,Pct = convert(decimal(10,1),(MBytes *100.0) / nullif(MBytesMax,0))
 From  #Temp

Returns

DBName  FileType    MBytes  MBytesMax   Pct
master  Database    4       0           NULL
master  Log         1       0           NULL
tempdb  Database    816     0           NULL
tempdb  Log         894     0           NULL
msdb    Database    201     0           NULL
msdb    Log         19      2097152     0.0
xxxxxxx Database    761     4096        18.6
xxxxxxx Log         1       2097152     0.0
yyyyyyy Database    533     4096        13.0
yyyyyyy Log         1       2097152     0.0
zzzzzzz Database    1641    4096        40.1
zzzzzzz Log         1       2097152     0.0

Upvotes: 0

Related Questions