Broken Limitations where Website performance issues are caused by bad Limitations "1=1", issue with PubSub in NPM

Broken limitations are a PubSub issue where often the best practice is to do a full PubSub clean up and can affect various aspects of the Orion Platform including:

• Web Performance 

  • Slowness

  • Errors Unexpected Website Errors)

• Alerts

• Views 

• Resources

• Accounts.

Broken limitations occur when the "WhereClause" in the Limitations table contains "1=1"

This article combines the following in two parts:

• How to check if there are broken limitations

• Adding the spaces in the broken limitations 

• Deleting limitations that are still bad

• Deleting the snapshots with the bad or broken limitations

  • This should not damage or affect the database negatively, but it is always the best practice to do a backup of the database before making changes  

Unexpected SQL Error - Invalid column name 'N1'
Bad or broken limitations can be created in the limitations table in the database, which poorly affects the website performance.
This happens when the software is not able to complete creating a limitation correctly.

​​​​​​** Note: Please make sure you have a good Backup the Orion Database **

Part 1

Check the limitations table in the database for Limitations where the WhereClause is 1=1 by running this query to see if any of this type are broken:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT * FROM Limitations WHERE WhereClause = '1=1'

If you get any results, run this query to fix them:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

DELETE FROM Limitations WHERE WhereClause = '1=1'

For example, there are some broken limitations (FB50203, NS223073):

The breaking point is in the one space in the WhereClause.
This will work: "(( Interfaces.Caption"
This will NOT work: "((Interfaces.Caption"

Fix the issue by running this SQL script in either the Database Manager or SQL Server Management Studio
Note:  Create a database backup before running the following script.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

Update Limitations set WhereClause = REPLACE(REPLACE(REPLACE(CAST(WhereClause as varchar(max)), '( (', ' ( ( '), '((', ' ( ( '),'))',' ) ) ')

Part 2

​​​After the limitations have been removed follow the Resolution steps in the article.

'Clear Information Services Subscriptions in Orion Products'

1. Stop all Orion Services (Start and stop Orion services ) on all Orion servers including Additional Polling Engines and Additional Web Server.

2. Open the SolarWinds Orion Database Manager.

3. Click "Add default Server".

4. Right-click the Orion Database, and select New Query.

Enter the following queries:       

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

DELETE FROM Limitations WHERE WhereClause = '1=1'
DELETE FROM LimitationSnapShots
DELETE FROM ContainerMemberSnapshots
DELETE FROM PendingNotifications
DELETE FROM SubscriptionTags
DELETE FROM Subscriptions WHERE EndpointAddress NOT LIKE 'http%'

5. Click Execute query.    
6. Start all Orion services for all Orion Servers.

Check these tables once more over the next few days. If something keeps failing, create a ticket with our support team for investigation.

Related Issue: Subscription failures after the re-installation of an Additional Polling Engine