Restart List Item ID Numbering In SharePoint Server 2016

SharePoint uses consecutive IDs to number the list/library items. Whenever we delete a list item in between, the next created item will be numbered as ‘Deleted Item ID + 1’. The deleted Item’s ID is never reused. Each item is thus associated with a unique item ID and we cannot change it from the UI. Moreover, even if we delete all the list items from the list, the next list item iD will not start from ‘1’. Instead, it will be the consecutive number taken from the last created Item’s ID. This means SharePoint internally keeps a track of the list item IDs and predicts what should be the ID of the next item in the SQL table.

In this article, we will see, how to reset the list item ID numbering and force SharePoint to start it from ‘1’.We will cover two methods to implement this, which are-

Supported way – Making use of list templates
Unsupported way – Updating SQL Server table directly

Before getting into the implementation part, let’s see how SharePoint keeps track of the upcoming list item ID. I have created a list, “ID Check”, with the three Items.

Thus, what is the upcoming ID number of the next list item? We can use the script, given below, to get the details from the SQL table-

SELECT * FROM [ContentDB Name].[dbo].[AllListsAux] where ListID=’GUID of List'

Output-

SELECT * FROM [WSS_Content].[dbo].[AllListsAux] where ListID='1ee3cfd5-515f-498f-b3bf-7ef9220e86af'

Note- You can get the List GUID by right clicking ‘Audience targeting settings’ from List settings.

Link address will look, as given below. Within the curly braces, you have the list GUID.

Let’s go to the implementation part of resetting the list item ID.

Supported Way

Taking the list template without including the content and restoring it as a new list will help in resetting the list Item ID and numbering will start from 1. You can save the list as a template by going to the list settings.

Specify the template name and save.

Now, create a new list, based on this template.

When we create a new list item, it will start ID numbering from 1.

Unsupported way

Lets head back to the first list “ID Check”. Currently, there are three items. If we delete all the three items and create a fourth item, the ID will start from the next consecutive ID and not from ‘1’.

We cannot get around this, as it is a design limitation imposed to maintain ID concurency within SharePoint list. However, we can change this by running a SQL Query.

This is a totally unsupported way and Microsoft never recommends to touch SharePoint backend SQL database for any reason. In case of any issues, if you contact the support team at Microsoft, you are likely to get a cold response from them in resolving it.

Let's see, how you can reset the list item ID from the backend directly.

You can run the script, given below, to update the list item ID numbering-

UPDATE [ContentDB Name].dbo.AllListsAux set NextAvailableId=1 where ListID=’GUID of List'

Output-

UPDATE [WSS_Content].dbo.AllListsAux set NextAvailableId=1 where ListID='1ee3cfd5-515f-498f-b3bf-7ef9220e86af'

Now, let’s try to create a new list item.

Thus, Item 5, which was created after Item 4 has been assigned ID ‘1’ . Now, if we keep creating 3 more items, the next ID that will be assigned to the Item will be ‘4’ but an Item with such an ID already exists in the list. We will get the error, given below, in such a case:

Unless we delete the conflicting list item, we cannot move forward. This is the design limitation, imposed to enforce the concurrency within the list item ID. Interacting with SQL DB to change the data and settings can cause the unexpected results and is strictly not advised by Microsoft. This article only provides you an insight about what can be done behind the scenes.

Summary

Thus, we discussed two ways to reset the list item ID, so as to restart its numbering sequence. Reiterating once again - dealing with SharePoint’s SQL table has to be done at one’s own risk, as it is not supported by Microsoft.