Troubleshooting: Restoring Visibility to Deleted Items in Service Manager Console

Table of Contents

This article outlines a comprehensive approach to resolving an issue where deleted items are not visible within the System Center Service Manager (SCSM) console. Users encountering this problem will typically observe a specific error message within the console and a corresponding SQL Server error 8623 in the event logs. Understanding the root cause and implementing the provided resolutions can significantly improve the manageability of your SCSM environment and ensure proper data hygiene.

The core of this problem often lies in the complexity of the underlying database queries. When the system attempts to load the “Deleted Items” view, it executes a highly intricate SQL query. This query can become unmanageable for the SQL Server query processor, especially in environments with a large number of derived class types stemming from “Configuration Items.” This guide offers practical solutions ranging from PowerShell scripts to console view configurations and management pack optimization to restore functionality.

Symptom: An Error Occurred While Loading Items¶

When navigating to the Administration section and then selecting Deleted Items in the System Center Service Manager console, users are met with a disconcerting error message. Instead of a list of items, a pop-up displays the following technical details, indicating a failure in data retrieval:

Message: An error occurred while loading the items.
Microsoft.EnterpriseManagement.UI.ViewFramework.AdvancedListSupportException: The Full adapter threw an exception. See the FullUpdate property to see the exception.
at Microsoft.EnterpriseManagement.UI.ViewFramework.AdvancedListSupportAdapter.DoAction(DataQueryBase query, IList1 dataSources, IDictionary2 parameters, IList1 inputs, String outputCollectionName) at Microsoft.EnterpriseManagement.UI.DataModel.QueryQueue.StartExecuteQuery(Object sender, ConsoleJobEventArgs e) at Microsoft.EnterpriseManagement.ServiceManager.UI.Console.ConsoleJobExceptionHandler.ExecuteJob(IComponent component, EventHandler1 job, Object sender, ConsoleJobEventArgs args)

This console error is not an isolated incident; it is typically accompanied by a critical SQL Server error logged in the event viewer. The specific error, Msg 8623, Level 16, State 1, Line 3, signifies a severe problem within the SQL query processor:

• Msg 8623, Level 16, State 1, Line 3
  The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

This SQL error clearly indicates that the query being executed by Service Manager is too complex for SQL Server to process efficiently, leading to resource exhaustion. The inability to generate a query plan effectively halts the retrieval of deleted item data, manifesting as the console error described above.

Unraveling the Cause: Excessive Class Types and SQL Query Complexity¶

The root cause of this persistent issue lies within the architecture of how Service Manager handles and queries “Deleted Items.” When the “Deleted Items” view is accessed, SCSM generates an SQL query designed to retrieve all objects marked for deletion. In environments where there are numerous class types derived from Configuration Items, this generated SQL query becomes exceptionally complex, featuring a multitude of UNION ALL statements. Each UNION ALL combines results from different tables or views, corresponding to distinct class types.

SQL Server’s query optimizer is responsible for finding the most efficient way to execute a query. However, when the number of UNION ALL statements grows excessively, typically exceeding 600, the query processor struggles to construct an optimal execution plan. This struggle consumes an exorbitant amount of internal resources, eventually leading to the Msg 8623 error where the processor “runs out of internal resources.” It’s akin to a librarian trying to organize an overwhelming number of books without enough shelf space or cataloging time.

To accurately diagnose this situation and determine if your environment is affected by this specific cause, you can execute a simple SQL query against your Service Manager database. This query will count the number of views derived from “Configuration Item,” which directly correlates to the complexity of the “Deleted Items” query.

-- Views derived from Configuration Item
use ServiceManager
go
select v.name
from sys.all_columns c
inner join sys.all_views v on c.object_id=v.object_id
where c.name like 'ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B'
order by 1

Run this query against your ServiceManager database. If the number of rows returned by this query significantly exceeds a few hundred (the threshold being around 600), it strongly indicates that your environment is indeed suffering from this issue, necessitating the application of one of the provided resolutions.

Resolution 1: Leveraging PowerShell for Item Management¶

One effective way to bypass the console’s query limitations is to manage deleted items directly using PowerShell scripts. This method provides granular control over individual or multiple deleted items without invoking the problematic consolidated view query. Before proceeding, it is imperative to back up your Service Manager databases to ensure data integrity in case of unforeseen issues.

The following PowerShell scripts simulate the functionality of the “Deleted Items” view, allowing you to either remove permanently or restore deleted configuration items. It is crucial to note that RemoveDeletedItem.ps1 is designed for processing a single deleted item, while RestoreDeletedItems.ps1 enables the restoration of multiple items simultaneously, offering flexibility in your management tasks.

Prerequisites for PowerShell Scripts¶

Before running these scripts, ensure the following:
* Service Manager PowerShell Module (SMlets): The scripts are designed to use SMlets cmdlets, although the Import-Module SMlets line is commented out. If SMlets are not already loaded or auto-loaded in your PowerShell session, you may need to uncomment and run this line or ensure the module is available. SMlets are a community-driven set of cmdlets that greatly enhance SCSM administration via PowerShell.
* Permissions: The user executing the script must have administrative privileges on the Service Manager management server and appropriate permissions on the SQL Server instance hosting the Service Manager database.
* SQL Server Connectivity: The scripts connect to the SQL Server database specified in the registry (HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database). Ensure the management server can connect to this SQL instance.

RemoveDeletedItem.ps1¶

This script first lists all deleted items by executing an embedded SQL query, then prompts you to select a single item for permanent removal using an interactive Out-GridView. Removing a configuration item permanently deletes its history and relationships, so exercise extreme caution.

#Import-Module SMlets
$sqlInstance=(Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Database").DatabaseServerName
$smDbName=(Get-ItemProperty -Path "HKLM:\SOFTWARE\\Microsoft\\System Center\\2010\\Common\\Database").DatabaseName
#-------------------------------------------------
$SqlQuery="--Objects in DeletedItems view 
create table #ResultTable_DeletedItems (Name nvarchar(max) null, Class nvarchar(max) null, Path  nvarchar(max) null, TypeName nvarchar(256) null, FullName nvarchar(max) null, BaseManagedEntityId uniqueidentifier not null)
declare @viewName sysname, @sql nvarchar(max),@ParmDefinition nvarchar(max),@retval bigint
declare c cursor read_only forward_only for
select v.name
from sys.all_columns c
inner join sys.all_views v on c.object_id=v.object_id
where c.name like 'ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B'
order by 1
open c
while 1=1
begin
fetch c into @viewName
if @@FETCH_STATUS<>;0 break
set @sql='
insert #ResultTable_DeletedItems
select mtv.DisplayName as Name, LTValue as Class, Path, mt.TypeName, bme.FullName, bme.BaseManagedEntityId
from ' + @viewName + ' mtv inner join BaseManagedEntity bme on mtv.BaseManagedEntityId=bme.BaseManagedEntityId inner join ManagedType mt on bme.BaseManagedTypeId=mt.ManagedTypeId 
inner join LocalizedText lt on mt.ManagedTypeId=lt.LTStringId and LanguageCode=''ENU'' and LTStringType=1
where mtv.ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B = ''47101E64-237F-12C8-E3F5-EC5A665412FB''
'
exec sp_executesql @sql
end
close c
deallocate c
select * from  #ResultTable_DeletedItems
drop table #ResultTable_DeletedItems"
$cnt=0
Write-Host "Please wait while Deleted Items are listed. $(Get-Date)"
"************************************************"
$ConnectionString="Server=$sqlInstance;Database=$smDbName;Integrated Security=True"
$SqlConnection = new-object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $SqlQuery
$dt = New-Object System.Data.DataTable
$da = New-Object System.Data.SqlClient.SqlDataAdapter  -ArgumentList $SqlCommand
[void]$da.fill($dt)
$rows=$dt.Rows
#-------------------------------------------------

$selectedDeletedItem= ($rows | Out-GridView  -Title 'Select a single Deleted Item to REMOVE' -OutputMode Single)

if ($selectedDeletedItem)
{
    $Confirm = Read-Host "Deleted configuration items that are removed lose all history and relationships. Do you want to remove the selected configuration item? Type YES to confirm."
    if ($Confirm -ne "YES") {Write-Host "Exiting..."; exit}

    $itemRestored= Get-SCSMObject -Id $selectedDeletedItem.BaseManagedEntityId
    $itemRestored | Remove-SCSMObject -Force

    Write-Host "Selected item below has been REMOVED:"
    $selectedDeletedItem
}
else
{
    Write-Host "No item selected to remove."
}

RestoreDeletedItems.ps1¶

Similar to the RemoveDeletedItem.ps1 script, this PowerShell script first lists all deleted items. However, it allows you to select one or more items using the Out-GridView for restoration. Restoring an item changes its ObjectStatus back to Active, making it visible again within the Service Manager console.

#Import-Module SMlets
$sqlInstance=(Get-ItemProperty -Path "HKLM:\SOFTWARE\\Microsoft\\System Center\\2010\\Common\\Database").DatabaseServerName 
$smDbName=(Get-ItemProperty -Path "HKLM:\SOFTWARE\\Microsoft\\System Center\\2010\\Common\\Database").DatabaseName
#-------------------------------------------------
$SqlQuery="--Objects in DeletedItems view 
create table #ResultTable_DeletedItems (Name nvarchar(max) null, Class nvarchar(max) null, Path  nvarchar(max) null, TypeName nvarchar(256) null, FullName nvarchar(max) null, BaseManagedEntityId uniqueidentifier not null)
declare @viewName sysname, @sql nvarchar(max),@ParmDefinition nvarchar(max),@retval bigint
declare c cursor read_only forward_only for
select v.name
from sys.all_columns c
inner join sys.all_views v on c.object_id=v.object_id
where c.name like 'ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B'
order by 1
open c
while 1=1
begin
fetch c into @viewName
if @@FETCH_STATUS<>;0 break
set @sql='
insert #ResultTable_DeletedItems
select mtv.DisplayName as Name, LTValue as Class, Path, mt.TypeName, bme.FullName, bme.BaseManagedEntityId
from ' + @viewName + ' mtv inner join BaseManagedEntity bme on mtv.BaseManagedEntityId=bme.BaseManagedEntityId inner join ManagedType mt on bme.BaseManagedTypeId=mt.ManagedTypeId 
inner join LocalizedText lt on mt.ManagedTypeId=lt.LTStringId and LanguageCode=''ENU'' and LTStringType=1
where mtv.ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B = ''47101E64-237F-12C8-E3F5-EC5A665412FB''
'
exec sp_executesql @sql
end
close c
deallocate c
select * from  #ResultTable_DeletedItems
drop table #ResultTable_DeletedItems"
$cnt=0
Write-Host "Please wait while Deleted Items are listed. $(Get-Date)"
"************************************************"
$ConnectionString="Server=$sqlInstance;Database=$smDbName;Integrated Security=True"
$SqlConnection = new-object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.ConnectionString = $ConnectionString
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $SqlQuery
$dt = New-Object System.Data.DataTable
$da = New-Object System.Data.SqlClient.SqlDataAdapter  -ArgumentList $SqlCommand
[void]$da.fill($dt)
$rows=$dt.Rows
#-------------------------------------------------

$selectedDeletedItems= ($rows | Out-GridView  -Title 'Select Deleted Item(s) to RESTORE' -passthru )

if ($selectedDeletedItems)
{

    $Confirm = Read-Host "This will restore the selected configuration items. Do you want to continue? Type Y to confirm." 
    if ($Confirm -ne "Y") {Write-Host "Exiting..."; exit}

    $Active = Get-SCSMEnumeration System.ConfigItem.ObjectStatusEnum.Active$

    foreach($selectedDeletedItem in $selectedDeletedItems)
    {
        $itemRestored= Get-SCSMObject -Id $selectedDeletedItem.BaseManagedEntityId
        $itemRestored | set-scsmobject -property ObjectStatus -value $Active  
    }
    Write-Host "Selected item(s) below has been restored:"
    $selectedDeletedItems
}
else
{
    Write-Host "No item selected to restore."
}

Resolution 2: Creating Specialized Console Views¶

Another highly effective workaround involves creating targeted console views that circumvent the complexity of the default “Deleted Items” view. Instead of trying to load all deleted items across all derived Configuration Item classes simultaneously, you can create views that focus on specific, manageable subsets. This approach allows the SCSM console to build simpler SQL queries, avoiding the UNION ALL explosion that causes the SQL Server error.

Follow these steps to create specialized console views:

1. Navigate to Configuration Items: Open your Service Manager console and go to the Configuration Items wunderbar.
2. Initiate New View Creation: In the Tasks pane, locate and click on Create View. This will open the Create View Wizard.
3. Specify View Criteria (Class Type):
   • In the Criteria section of the wizard, focus on the Class filter.
   • Instead of selecting the generic Configuration Item class, choose a more specific derived class type. Examples include Windows Computer, Printer, Software Item, or any other specific Configuration Item type relevant to your environment. You can refer to the Class column returned by the RestoreDeletedItems.ps1 script (from Resolution 1) for a list of available and problematic class types.
   • This step is crucial because it drastically reduces the number of tables involved in the query, thereby simplifying it significantly.
4. Refine Criteria for Deleted Status:
   • Add another criterion: [Configuration Item].Object Status equals Pending Delete. This filter ensures that the view only displays items that are marked as deleted within the specified class.
5. Configure Display Columns:
   • Proceed to the Display section of the wizard.
   • Ensure that at least the Name and Path columns are selected for display. These columns provide essential identification details.
   • You can also select other columns that are specific and relevant to the particular class type you chose (e.g., IP Address for a Windows Computer view, or Manufacturer for a Printer view).
6. Complete View Creation: Give your view a descriptive name (e.g., “Deleted Windows Computers,” “Deleted Printers”) and optionally add a description. Click OK to save and create the view.

Once these specialized views are created, you can access them within the Configuration Items section. Each view will efficiently load and display only the deleted items belonging to its specific class type. From these granular views, you can then remove items permanently or restore them to an active status, effectively managing your deleted items without encountering the SQL Server error.

Resolution 3: Optimizing Management Packs for Performance¶

A proactive and long-term solution to mitigate this issue involves optimizing your Service Manager environment by carefully reviewing and potentially deleting unused management packs. Management packs (MPs) define classes, workflows, forms, and other components within SCSM. Each class defined, particularly those derived from Configuration Items, contributes to the complexity of the “Deleted Items” query. By removing unnecessary management packs, you directly reduce the total number of derived class types, thereby simplifying the underlying SQL queries and preventing the UNION ALL statement overload.

Important Considerations Before Deletion:

• Backup: Always perform a full backup of your Service Manager databases and management packs before deleting any MPs. This allows for recovery if an unintended deletion occurs.
• Test Environment: Rigorously test the impact of deleting management packs in a non-production (test) environment first. Deleting a management pack can have cascading effects on other system functionalities, views, or workflows that might depend on its definitions.
• Dependency Check: SCSM provides a dependency viewer for management packs. Use it to understand what other components or MPs rely on the MP you intend to delete.

To identify which management packs contribute most to the number of derived Configuration Item class types, run the following SQL query against your ServiceManager database:

-- Order the management packs by the number of derived configuration items class types
select MP.MPName, count(1) as TotalConfigurationItemClassTypes from ManagedType MT
inner join ManagementPack as MP on MT.ManagementPackId = MP.ManagementPackId
where ManagedTypeTableName in (select TABLE_NAME from INFORMATION_SCHEMA.columns where column_name like '%AssetStatus_B6E7674B_684A_040D_30B8_D1B42CCB3BC6%') and ManagedTypeTableName is not null
group by MP.MPName
order by count(1) desc

This query will list management packs in descending order based on the number of Configuration Item class types they define. This gives you a clear indication of which MPs are contributing most to the problem. Focus on MPs with high TotalConfigurationItemClassTypes counts that are no longer actively used or were imported for temporary purposes.

Steps for Deleting Management Packs:

1. Identify Unused MPs: Review the output of the SQL query and identify management packs that are not essential for your current SCSM operations. This might include MPs from old integrations, features no longer in use, or experimental MPs.
2. Check Dependencies: In the Service Manager console, navigate to Administration > Management Packs. Select a candidate MP and click View Dependencies in the Tasks pane. Ensure no critical components rely on it.
3. Delete MP (in Test Environment First): Right-click the identified MP and select Delete. Follow the prompts. Observe your test environment for any adverse effects on workflows, views, or data.
4. Repeat for Production (Carefully): Once you are confident in your test environment, replicate the deletion process in your production environment during a scheduled maintenance window.

Regularly auditing and cleaning up your management pack inventory is a vital maintenance practice for keeping your Service Manager environment performant and manageable.

Understanding Service Manager Architecture and Performance¶

To further grasp the implications of these resolutions, it’s beneficial to briefly consider the underlying architecture of Service Manager. SCSM relies heavily on a robust SQL Server database to store all configuration items, incidents, service requests, and other operational data. Management Packs serve as the blueprints, defining the schema and behavior of these objects. When you create a class (e.g., “Windows Computer”) within a management pack, it typically results in the creation of corresponding tables or views in the database.

The “Deleted Items” view, in its attempt to provide a comprehensive list of all deleted configuration items, must query all these different class tables. If each class has its own representation in the database, pulling all of them together necessitates numerous UNION ALL operations. This process can be visualized as gathering information from many disparate sources and combining them into one large report. The more sources there are, the more complex and resource-intensive the report generation becomes.

```mermaid
graph TD
A[User Clicks ‘Deleted Items’ View] → B{SCSM Console Initiates Query};
B → C[Generate SQL Query with UNION ALL for each Derived CI Class];
C → D{SQL Server Query Processor};
D – Large Number of Classes (>600) → E[Resource Exhaustion: Msg 8623];
E → F[Query Plan Failure];
F → G[Console Error: “An error occurred while loading the items”];
D – Small/Optimized Number of Classes → H[Successful Query Plan];
H → I[Display Deleted Items in Console];

subgraph Resolutions
    J[Resolution 1: PowerShell Scripts] --> K[Direct Item Management (Remove/Restore) - Bypasses Console View Query];
    L[Resolution 2: Specialized Console Views] --> M[Query Specific CI Classes Individually - Simplifies Queries];
    N[Resolution 3: Delete Unused Management Packs] --> O[Reduces Total Derived CI Classes - Prevents Query Complexity];
end

G --> J;
G --> L;
G --> N;

```

This diagram illustrates the flow from a user action to the error and how each resolution tackles the problem at different stages of the process.

More Information and Critical Warnings¶

While troubleshooting and resolving issues related to deleted items, it’s crucial to be aware of certain critical best practices and warnings to prevent further system instability:

• Management Server Objects: When dealing with computer objects, never remove computer objects that represent your Service Manager management servers. These objects are fundamental to the operation of SCSM workflows and processes. Deleting a management server’s computer object can lead to severe operational failures, causing all Service Manager workflows to cease functioning. Should this occur, your only recourse might be to restore the entire Service Manager database from a previous, healthy backup, leading to potential data loss depending on the backup’s age.
• Database Backup Importance: Always prioritize database backups before making any significant changes to your Service Manager environment, especially when modifying management packs or executing direct database operations. This safeguard is your primary protection against irreversible data loss or system corruption.
• Thorough Testing: Any resolution involving script execution or management pack changes should ideally be tested in a development or staging environment that mirrors your production setup. This practice helps identify unforeseen consequences without impacting live operations.
• Regular Maintenance: Implement a schedule for regular maintenance activities within Service Manager. This includes reviewing and consolidating management packs, archiving old data, and ensuring database health through standard SQL Server maintenance tasks. Proactive maintenance can prevent many performance-related issues, including the one discussed here.

By adhering to these guidelines, you can ensure the stability and reliability of your Service Manager installation while effectively managing your deleted items. This comprehensive approach not only fixes the immediate problem but also contributes to the long-term health of your SCSM environment.

Conclusion and Engagement¶

The inability to view and manage deleted items in Service Manager can significantly hinder administrative tasks and data governance. By understanding the underlying cause—an overly complex SQL query due to a high number of derived Configuration Item classes—and applying the resolutions detailed in this article, you can restore full visibility and control over your deleted assets. Whether through targeted PowerShell scripts, specialized console views, or strategic management pack optimization, these methods offer robust solutions to maintain a healthy and responsive Service Manager environment.

We hope this guide has provided you with the necessary tools and knowledge to effectively troubleshoot and resolve the “Deleted Items” visibility issue. What are your experiences with managing deleted items in Service Manager? Have you encountered similar SQL query performance challenges, or do you have alternative solutions that have worked for you? Please share your insights and questions in the comments below. Your contributions can help others in the Service Manager community!