Resetting a ID field of a list in SharePoint

When you are developing solutions you may have used to create plenty of items in the lists interact with you solution. But when you are trying to clear up and ready the contents of your solution for production environments you may have struggle with resetting Id field of list and libraries to restart numbering at 1 sometimes. In this scenario there’s no any list options or direct way to handle this case but there’s way.

This post going to describe the way which you can reset the Id field of a list. This can achieve by using the content database of the site which the list has created.

Follow this steps:

First you need to go the content database.(Go to Central Administration > Application Management. Under the Site Collections click View all site collections. Navigate to your site collection and get the Database Name)

Open the content database using MS SQL Server Management Studio.

Open New Query and run following query.

SELECT * FROM [Your_Content_Database_Name].[dbo].[AllListsAux] WHERE ListID='Your_List_GUID'

You can see a list called AllListsAux which is maintain ID details of all the lists of the site. You can see the NextAvailableId of your list there.

Now run the following query to update the NextAvailableId. This will reset the Id field of the list and will restart numbering at “1”.

UPDATE [Your_Content_Database_Name].[dbo].[AllListsAux] SET NextAvailableId=1 WHERE ListID='Your_List_GUID'

Check this Post see how to find the GUID of a list.

Note: You need to be careful when querying on and doing any modifications to content databases to avoid malfunctioning site contents. Also note that this not a recommended approach by Microsoft.