Add all MS-SQL databases

Hi Florian,

Can you clarify this a little bit? What exactly are you trying to accomplish? Add as a sensor? Which sensor do you want to use and/or what do you want to monitor?

You always can write custom scripts and it actually is possible to create a script that would give you all databases on a server - but in the end it depends on various factors of what you want to accomplish / monitor.

Regards

Florian Rossmark

www.it-admins.com

I will have a simple check if all databases are running. I took the "Microsoft SQL v2" sensor to add a single database but maybe it is possible to add all databases at the same time?

In this case I would simply supply PRTG h the credentials and do an Auto-Discover e.g. to add all SQL databases as single sensors.

Of course, if you want to monitor something else or more specific in a single sensor, you would need to create a SQL query that lists all SQL databases and e.g. their size or status directly form SQL.

It all depends a bit on what you want to do. But there are ways to accomplish that.

Regards

Florian Rossmark

www.it-admins.com

That's exactly what I want but when I start the Auto-Discover it finds no SQL databases. Which point I have to check during the Auto-Discover?

Hi,

@FlorianR Thanks for chiming in again!
@FlorianK What actual sensor are you using for this?

Kind regards,
Stephan Linke, Tech Support Team

When I'm using the Auto-Discovery for a new device I used "Automatic search (Standard)", "Automatic search (detail)" or "Create with template" but no discovery will show me the SQL databases.

Sorry, misunderstanding. You mentioned that you can only add one database at the time - what sensor are you actually using for that? Or do you mean database-servers?

Kind regards,
Stephan Linke, Tech Support Team

Ah sorry, my fault :) I'm using sensor "Microsoft SQL v2" and there I can only add one database at the time.

Alright. The problem with this sensor is that it's not a metascan sensor (i.e. it doesn't scan for available databases upon creation). How many databases are talking about and do you have a list of those?

Kind regards,
Stephan Linke, Tech Support Team

We have 20-30 databases per database server. Is it possible to add them in any other way automatically? Do you have a script for me?

If you add the database name as variable, it would be easier to clone the original sensor and only modify the placeholder in the sensor settings of the clones - that will definitely be faster (and a more generalistic approach) :)

Kind regards,
Stephan Linke, Tech Support Team

You could do this in various ways with a SQL script - it depends really on what you want to see...

sys.sysdatabases

would give you information about the databases configured on the specific SQL instance, if you want to go simple with the amount of databases you could use the SQL script below and put it in your PRTG installation - custom sensors directory:

USE master

SELECT 
-- how many databases do we have on this server?
(
	SELECT COUNT(Name) FROM sys.sysdatabases WHERE name NOT LIKE 'tempdb'
) AS TotalAmountOfDatabases

This simply give you the amount of databases.

You could go more granular and work with the statuses etc. or even the table [master].[sys].[databases] e.g.

If none of that convinces you, another alternative would be to create an custom advanced EXE sensor with e.g. VBScript using ADO to execute an SQL query and report back some XML with more detailed information per database.

The same would be possible with transact SQL code - using e.g. CURSOR / FETCH methods to process the rows that hold information about those databases and giving PRTG some custom table object back that it can process.

Hope those hints help you accomplishing this. Honestly thought PRTG was able to read out the MASTER table while doing auto-discover, but seems like I was wrong :-)

Regards

Florian Rossmark

www.it-admins.com

Thanks Florian! :) We're indeed not doing any scan against the master table, as the sensor is simply no metascan sensor :)

Kind regards,
Stephan LInke, Tech Support Team

Thanks Florian! I will take the query "select name, state from sys.databases". This query will give me an overview which database is online (state 0) and which one is offline (state 6). I've added this script to the custom sensors. After that I added a new sensor (MS SQL v2) and checked this script. Unfortunately it runs into an error. Can you please check it and give me some help how I can add it correctly?

Many thanks!

Thank you for this massive information. The script is working when I'm executing it on the SQL Server. Unfortunately I get this error under PRTG Must declare the scalar variable @Columns

Is there any bug in the script or doesn't it work with my PRTG config?

Hi Florian,

No - this is no bug - well - not directly. I wrote the script in SQL Management Studio and didn't actually run via PRTG. Checked this briefly in PRTG and changed the script so it would work. Please see https://www.paessler.com/manuals/prtg/microsoft_sql_v2_sensor for information about the issue - you will see that the semicolons caused the issue partly - but not only. I as well had to remove some line feeds. PRTG is interpreting them differently then SQL Management Studio.

Here is the adjusted version of the script I posted:

USE master

DECLARE @SQLColumns NVARCHAR(MAX)
DECLARE @SQLQuery NVARCHAR(MAX)

SET @SQLColumns = N''

SELECT @SQLColumns+=N''+QUOTENAME([Name])+'=(SELECT [State] FROM sys.databases WHERE [NAME] = '''+[Name]+'''),' FROM sys.databases ORDER BY [Name]

SET @SQLQuery = N'SELECT '+@SQLColumns+'TotalDatabases=(SELECT COUNT([Name]) FROM sys.databases)'

EXEC sp_executesql @SQLQuery

While implementing this in PRTG, I also remembered that you mentioned about 50 databases per SQL server. This would actually be another issue - weather you would work with columns or rows. A single sensor can only have 10 channels, meaning you are way over the limit.

A nice workaround would be processing the status a bit more combined and possibly giving you valid data in case something goes offline.

USE master

SELECT 
(SELECT COUNT([Name]) FROM sys.databases)
AS DatabasesTotal,
(SELECT COUNT([Name]) FROM sys.databases WHERE [state] = 0) 
AS DatabasesOnline,
(SELECT COUNT([Name]) FROM sys.databases WHERE [state] = 6) 
AS DatabasesOffline,
(SELECT (STUFF((SELECT ', ' + [Name] FROM sys.databases WHERE [state] = 6 FOR XML PATH('')), 1, 2, '')))
AS DatabasesOfflineName

This gives you a total of 4 columns: - total of databases on the server as numeric - total of databases with status online as numeric - total of databases with status offline as numeric - names of the databases offline as a combined string, comma separated

The last column with the names possibly could be used in the error-description / alert-description - see Use Data Table Value in Sensor Message e.g.

The statuses are explained in this Microsoft article - for reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017

Hope this helps you with your specific need - let us know how it goes..

Regards

Florian Rossmark

www.it-admins.com