You would never want to change the auto-incrementing value range in SCSM 2012…would you? Well, I came across an instance where I needed to (which I will explain in detail in future blog posts).

Essentially, I was migrating from a previous SCSM 2012 SP1 environment (that was originally migrated from 2010) to a new SCSM 2012 SP1 environment. However, I needed the Incident Request (IR) reporting to be available from the old environment. Therefore, I wanted to ensure that our IR values did not duplicate those in the past.

In the ServiceManager database, a table called AutoIncrementAvailableRange stores the next available number for the classes. This is the table that we need to alter in order to choose a defined range.

First, we need to join some tables by running the following query:

select

MT.TypeName,

MT.ManagedTypeID,

MTP.ManagedTypePropertyName,

MTP.ManagedTypePropertyID,

AIAR.FirstAvailableValue

from ManagedType as MT, ManagedTypeProperty as MTP, AutoIncrementAvailableRange as AIAR

where MT.ManagedTypeId = AIAR.ManagedTypeId and MTP.ManagedTypePropertyId = AIAR.ManagedTypePropertyId

You should now see a result that consists of the TypeName, ManagedTypeID, ManagedTypePropertyName, and ManagedTypePropertyID.

Now (the scary part), we are ready to change the WorkItem ID. Do NOT run this unless you are ready!

For example, if we want to change the first available value to 40000, note the ManagedTypeId and the ManagedTypePropertyID for System.WorkItem and run the following:

update AutoIncrementAvailableRange

set FirstAvailableValue = 40000

where ManagedTypeId = ‘F59821E2-0364-ED2C-19E3-752EFBB1ECE9’ and ManagedTypePropertyId = ‘28B1C58F-AEFA-A449-7496-4805186BD94F’

Now, your next IR number should be IR40001.