Hi, is it possible to add all running databases at the same time to a server? I actually only can add one database at the same time.
Greetings Florian
Add all MS-SQL databases
Votes:
0
Best Answer
Accepted Answer
Votes:
8
Hi Florian,
As mentioned, you would need to transform the rows in to columns to have PRTG process them. See the SQL script below that will do this for you.
USE master DECLARE @Columns NVARCHAR(MAX) DECLARE @SQLQuery NVARCHAR(MAX) SET @Columns = N''; SELECT @Columns+=N''+QUOTENAME([Name])+'=(SELECT [State] FROM sys.databases WHERE [NAME] = '''+[Name]+'''),' FROM sys.databases ORDER BY [Name]; SET @SQLQuery = N' SELECT '+@Columns+'TotalDatabases=(SELECT COUNT([Name]) FROM sys.databases) '; EXEC sp_executesql @SQLQuery
This actually will take the rows you get from sys.databases and use the NAME column to create a new set of columns. It then executes a query per NAME to gather the status of the specific database from sys.databases and executes this generated query giving you a one-row result and the status per column.
- master=0
- model=0
- tempdb=0
etc...
additionally you have a final column that shows the TotalDatabases found on the SQL server.
There is a theoretical flaw that the amount of databases would not change, but a database was added and one was removed - giving you a similar amount of columns and TotalDatabases.
Further is a flaw that if something is added you might need to re-create the sensor, since PRTG has a fixed amount of channels (as of now). You could bypass this by adding dummy-channels int the SET @SQLQuery.
And then there is the issue that the name of each column is the database name, this could end up in some kind of change/mix-up if e.g. a database was removed or added. That's partly why I added the TotalDatabases - but since the whole script will return a dynamic result, there is a risk in it. You don't compare the list against a fixed name-list of databases, what would be possible with a dummy database that holds a compare list etc..
If you would like to go more fancy, you could as well transform the script and use a PRTG parameter to inject the needed SQL database names - this would bypass the initial name query from sys.databases to create the columns, but would mean you need to transform the whole script quite a bit. This is just an idea - it is not how the current SQL query works, but it is possible.
For the most part, this should help you getting the status per database in a single SQL sensor.
Feel free to use the contact form on my web-site - it might be beneficial to directly communicate to speed the solution finding process up. @PRTG team - if this should happen and we find another solution, I would post it here as well - just trying to avoid the lengthy process through the moderated forum..
Let us know if this actually helped you - I am curious - I like the idea of it, for that reason I came up with this script.
Regards
Florian Rossmark
17 Replies
Votes:
0
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
Votes:
0
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?
Votes:
0
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
Votes:
0
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?
Votes:
0
Hi,
@FlorianR Thanks for chiming in again!
@FlorianK What actual sensor are you using for this?
Kind regards,
Stephan Linke, Tech Support Team
Votes:
0
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.
Votes:
0
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
Votes:
0
Ah sorry, my fault :) I'm using sensor "Microsoft SQL v2" and there I can only add one database at the time.
Votes:
0
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
Votes:
0
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?
Votes:
0
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
Votes:
1
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
Votes:
0
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
Votes:
0
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!
Accepted Answer
Votes:
8
Hi Florian,
As mentioned, you would need to transform the rows in to columns to have PRTG process them. See the SQL script below that will do this for you.
USE master DECLARE @Columns NVARCHAR(MAX) DECLARE @SQLQuery NVARCHAR(MAX) SET @Columns = N''; SELECT @Columns+=N''+QUOTENAME([Name])+'=(SELECT [State] FROM sys.databases WHERE [NAME] = '''+[Name]+'''),' FROM sys.databases ORDER BY [Name]; SET @SQLQuery = N' SELECT '+@Columns+'TotalDatabases=(SELECT COUNT([Name]) FROM sys.databases) '; EXEC sp_executesql @SQLQuery
This actually will take the rows you get from sys.databases and use the NAME column to create a new set of columns. It then executes a query per NAME to gather the status of the specific database from sys.databases and executes this generated query giving you a one-row result and the status per column.
- master=0
- model=0
- tempdb=0
etc...
additionally you have a final column that shows the TotalDatabases found on the SQL server.
There is a theoretical flaw that the amount of databases would not change, but a database was added and one was removed - giving you a similar amount of columns and TotalDatabases.
Further is a flaw that if something is added you might need to re-create the sensor, since PRTG has a fixed amount of channels (as of now). You could bypass this by adding dummy-channels int the SET @SQLQuery.
And then there is the issue that the name of each column is the database name, this could end up in some kind of change/mix-up if e.g. a database was removed or added. That's partly why I added the TotalDatabases - but since the whole script will return a dynamic result, there is a risk in it. You don't compare the list against a fixed name-list of databases, what would be possible with a dummy database that holds a compare list etc..
If you would like to go more fancy, you could as well transform the script and use a PRTG parameter to inject the needed SQL database names - this would bypass the initial name query from sys.databases to create the columns, but would mean you need to transform the whole script quite a bit. This is just an idea - it is not how the current SQL query works, but it is possible.
For the most part, this should help you getting the status per database in a single SQL sensor.
Feel free to use the contact form on my web-site - it might be beneficial to directly communicate to speed the solution finding process up. @PRTG team - if this should happen and we find another solution, I would post it here as well - just trying to avoid the lengthy process through the moderated forum..
Let us know if this actually helped you - I am curious - I like the idea of it, for that reason I came up with this script.
Regards
Florian Rossmark
Votes:
0
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?
Votes:
3
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
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.
Add comment