Monitor error, SqlDatabaseSampler

TeunvdBiggelaar · November 13, 2023 12:36PM

Hi all,

Last week i've updated to version 13.0.39 and I noticed that a few servers are getting a "monitoring error" on the Overview page.

When i click on the message this is the error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Image: https://us.v-cdn.net/6029854/uploads/editor/r6/1wjq7ub0wrhu.png

When I deepdive on the server i get all the metrics and it appears green.

Did anyone experience this before?

Signe · November 13, 2023 3:20PM

Hi!
I´m having the same problem after updating to 13.0.39:

Image: https://us.v-cdn.net/6029854/uploads/editor/un/1v54s6q56c7v.png

Hoping for a fix asap as I really like the Index-tab.

Alex B · November 13, 2023 3:50PM

Hi all,

What version of SQL Server are these instances you are seeing the problem with?

Kind regards,
Alex

Signe · November 14, 2023 7:22AM

Hi!
For me it is SQL Server 2019 CU22, but I have other instances with the same version that shows correctly.
Cheers, Signe

TeunvdBiggelaar · November 14, 2023 8:31AM

Alex B said:

Hi all,

What version of SQL Server are these instances you are seeing the problem with?

Kind regards,
Alex

Hi Alex. It happens to one of the servers running on 2019, others seem to be fine.

Microsoft SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64) Aug 18 2023 14:05:15 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Alex B · November 14, 2023 11:36AM

Hi all,

Do you get the error if you run this query against the target SQL instance that you see the error for in SQL Monitor?

SELECT  db.name ,
        db.state_desc ,
        db.is_auto_create_stats_on ,
        db.is_auto_shrink_on ,
        db.collation_name ,
        db.compatibility_level ,
        db.create_date ,
        db.page_verify_option_desc ,
        db.recovery_model_desc ,
        db.database_id ,
        db.source_database_id ,
        db.is_in_standby ,
        db.is_read_only,
		ISNULL((SELECT 1 FROM [msdb].[dbo].[log_shipping_secondary_databases] WHERE ls.[secondary_database] = db.name),0) AS is_log_shipping_secondary,
        SUSER_SNAME(owner_sid) [owner]
FROM    sys.databases db
 LEFT OUTER JOIN [msdb].[dbo].[log_shipping_secondary_databases] AS ls
        ON ls.[secondary_database] = db.name
ORDER BY db.name

And if so, what is returned by the subquery here:

SELECT 1 FROM [msdb].[dbo].[log_shipping_secondary_databases] WHERE [secondary_database] = db.name

And how have you configured things to get this result?

Kind regards,
Alex

Signe · November 14, 2023 12:08PM

Alex B said:

Hi all,

Do you get the error if you run this query against the target SQL instance that you see the error for in SQL Monitor?...

I get the same error if I run the first query and this when I run the second:

Msg 4104, Level 16, State 1, Line 3

The multi-part identifier "ls.secondary_database" could not be bound.

Msg 4104, Level 16, State 1, Line 3

The multi-part identifier "db.name" could not be bound.

It is a named instance with logshipping, so it has a couple of databases in restoring mode. The default instance on the cluster has been uninstalled, maybe there are some remains of this that could somehow be the culprit?
Cheers, Signe

Alex B · November 14, 2023 12:12PM

Hi @Signe,

I forgot to take out the "ls." when I took that subquery out of the main one above it, apologies for that. I've edited it above but it is here again corrected now:

SELECT 1 FROM [msdb].[dbo].[log_shipping_secondary_databases] WHERE [secondary_database] = db.name

Are any of the databases in restoring mode that are a part of log shipping the secondary for multiple different primaries? We're trying to understand how the subquery could return more than one entry unless the seoncdary_database was in there multiple times, but that should only occur if it is the secondary for multiple primaries which, though seems possible to set, doesn't make sense.

Kind regards,
Alex

Signe · November 14, 2023 12:25PM

Alex B said:
Hi @Signe,

I forgot to take out the "ls." when I took that subquery out of the main one above it, apologies for that. I've edited it above but it is here again corrected now:
SELECT 1 FROM [msdb].[dbo].[log_shipping_secondary_databases] WHERE [secondary_database] = db.name
Are any of the databases in restoring mode that are a part of log shipping the secondary for multiple different primaries? We're trying to understand how the subquery could return more than one entry unless the seoncdary_database was in there multiple times, but that should only occur if it is the secondary for multiple primaries which, though seems possible to set, doesn't make sense.

Kind regards,
Alex

No worries

The query returns:

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "db.name" could not be bound.

There´s only one primary logshipping to the instance, 5 databases with transaction logshipping and one with merge replication (yes, it sucks, but I hope to get rid of it rather soon...

Image: https://us.v-cdn.net/6029854/uploads/editor/jz/oh7mul35ilca.png

Cheers//Signe

TeunvdBiggelaar · November 14, 2023 12:30PM

Alex B said:

Hi all,

Do you get the error if you run this query against the target SQL instance that you see the error for in SQL Monitor?

SELECT  db.name ,
        db.state_desc ,
        db.is_auto_create_stats_on ,
        db.is_auto_shrink_on ,
        db.collation_name ,
        db.compatibility_level ,
        db.create_date ,
        db.page_verify_option_desc ,
        db.recovery_model_desc ,
        db.database_id ,
        db.source_database_id ,
        db.is_in_standby ,
        db.is_read_only,
		ISNULL((SELECT 1 FROM [msdb].[dbo].[log_shipping_secondary_databases] WHERE ls.[secondary_database] = db.name),0) AS is_log_shipping_secondary,
        SUSER_SNAME(owner_sid) [owner]
FROM    sys.databases db
 LEFT OUTER JOIN [msdb].[dbo].[log_shipping_secondary_databases] AS ls
        ON ls.[secondary_database] = db.name
ORDER BY db.name

And if so, what is returned by the subquery here:

SELECT 1 FROM [msdb].[dbo].[log_shipping_secondary_databases] WHERE [secondary_database] = db.name

And how have you configured things to get this result?

Kind regards,
Alex

Hi Alex,

The first query returned the same error as in SQL Monitor.
The 2nd query doesn't work on it's own because its missing the db.name reference from the other part of the query, and in my case I have 3 logshipped databases on that host which are in a (Standby / Read-Only) state therefor.

Thanks, Teun

Alex B · November 14, 2023 3:34PM

Hi all,

Apologies for the continued failure on the subquery - too much haste!

We've identified the problem and it is due to there being more than one log shipped database on the same instance. The fix is in progress and should be available in the next release, likely tomorrow UK time.

I will update here further when that is available.

Kind regards,
Alex

SEarle86 · November 15, 2023 10:42AM

Hi,
We have upgraded to 13.0.39.27122 and seeing the same issue:

Image: https://us.v-cdn.net/6029854/uploads/editor/id/krsr45ie0hc3.png

However, the server has no log shipping databases.

I will wait for the expected release to see if that helps

Alex B · November 15, 2023 3:29PM

Hi all,

SQL Monitor version 13.0.40 is now available with a fix for this issue. You can download it here: https://download.red-gate.com/checkforupdates/SQLMonitorWeb/SQLMonitorWeb_13.0.40.27471.exe

Kind regards,
Alex

TeunvdBiggelaar · November 16, 2023 9:47AM

Hi Alex. I installed it on our development environment and i can confirm it solved the issue.

One small remark, since a few releases the finish button stays greyed out if you patch the basemonitor server. Maybe something for a next release as well

?

Image: https://us.v-cdn.net/6029854/uploads/editor/zc/um7d087uuvdg.png

Keep up the good work!

Alex B · November 16, 2023 11:15AM

Hi @TeunvdBiggelaar,

Great to hear that fixed the SqlDatabaseSampler issue!

For the other, does this only happen on the Base Monitor only installer or do you also see this happening when you run the main installer for web and BM?

Kind regards,
Alex

TeunvdBiggelaar · November 16, 2023 12:08PM

Only for the Basemonitor server.

Signe · November 16, 2023 3:23PM

Alex B said:

Hi all,

SQL Monitor version 13.0.40 is now available with a fix for this issue. You can download it here: https://download.red-gate.com/checkforupdates/SQLMonitorWeb/SQLMonitorWeb_13.0.40.27471.exe

Kind regards,
Alex

Finally got around to install 13.0.40 and it works like a charm! Thanks alot!
Cheers//Signe

TeunvdBiggelaar · November 17, 2023 7:45AM

What i also noticed is that the new version consumes way less CPU and Memory on the Basemonitor server!

Alex B · November 21, 2023 3:46PM

Hi @TeunvdBiggelaar,

The team has merged a fix for the Base Monitor only installer "Finish" button remaining greyed out. This will go out in the next release!

Kind regards,
Alex

Monitor error, SqlDatabaseSampler

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk