Remove orphaned SAM database objects after uninstalling evaluation version

This article describes how to remove orphaned database objects that remain after you uninstall an evaluation version of SAM. Leaving such objects may result in compatibility errors when installing upgrading other SolarWinds Platform modules.

To uninstall an evaluation version, follow the steps in Uninstall SAM and then determine if orphaned database objects remain and remove them, as described in this article.

Note: These steps remove data from the SolarWinds database. If you need to preserve any data, back up the database first. See Back up the SolarWinds Orion database using SQL Server Management Studio.

To remove orphaned database objects after uninstalling an evaluation version of SAM:

1. Log into the SolarWinds database server.
2. Open SQL Server Management Studio.
3. Select the SolarWinds database.
4. Click "New query".
5. Paste the following query into the Query window:
6. Click Execute Query.

-- 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.

/*
Warning: This script removes ALL APM (SAM) structures and data from the database!

'FN','IF'..scalar valued functions
'TF'..table valued functions
'P'..procedure
'U'..TABLE
'V'..View
*/

DECLARE @table_object_id int
DECLARE @table_name varchar(max)
DECLARE @table_type varchar(max)
DECLARE @stmt nvarchar(max)

DECLARE table_cursor CURSOR FAST_FORWARD FOR
SELECT o.object_id, o.name, o.[type]
FROM sys.objects o
WHERE o.[type] IN ('FN','TF','V','P','U') AND o.name LIKE 'APM_%' AND o.name NOT LIKE '%APM_Hardware%'
ORDER BY CASE o.[type] WHEN 'U' THEN 'z' ELSE o.[type] END

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_object_id, @table_name, @table_type
WHILE @@FETCH_STATUS=0
BEGIN

SET @stmt = CASE @table_type
    WHEN 'V' THEN 'VIEW'
    WHEN 'P' THEN 'PROCEDURE'
    WHEN 'FN' THEN 'FUNCTION'
    WHEN 'TF' THEN 'FUNCTION'
    ELSE 'TABLE'
    END

    SET @stmt = 'DROP '+@stmt+' ['+@table_name+']'
    PRINT @stmt
    EXEC [dbo].sp_executesql @statement = @stmt

    FETCH NEXT FROM table_cursor INTO @table_object_id, @table_name, @table_type
END

CLOSE table_cursor
DEALLOCATE table_cursor

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[GetApmDbVersion]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
    DROP FUNCTION GetApmDbVersion
END

"APM_Hardware" tables store hardware health data polled by Orion.
Please ensure that there's no hardware health data before deleting these tables separately:
APM_HardwareCategory, APM_HardwareUnit, APM_HardwareItem_Daily, APM_HardwareItem_DailyData, APM_HardwareItem_Detail, APM_HardwareItem_Hourly, APM_HardwareItem_HourlyData, APM_HardwareItem_Thresholds

If you're polling and storing hardware health data, you do not have to delete the "APM_Hardware" tables above.