Reset List Item ID value numbering at 1

Reset List Item ID value numbering at 1

One of the most common problem we face in SharePoint Lists is there is no direct option to reset the value of ListItem ID. In development environment we used to insert and delete lot of test records and during this time ID value automatically increased since ID column is a incrementer type. Finally when we move the site to staging and production environments using backup and restore the ListItem ID value will not start with 1.

But we do not have a OOB option to reset this value. But in the backend content database we can reset this value for any list. The following query will be used to reset value.

UPDATE <Content DB>.dbo.AllListsAux set NextAvailableId=1 where ListID='<GUID>'

Here you can see a table called AllListsAux. This list maintans Item count and Id details for all lists.

Please note we have to be very careful while doing any opertation in backend.

 

 

Or

 

 

Save the list as a template and recreate the list. ID columns will always keep incrementing and will never be reused.