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.

Hope you like this post. :)

Comments

  1. pramod naikJune 3, 2012 at 3:40 AM

    Good one..

    ReplyDelete
    Replies
    1. AnonymousNovember 1, 2012 at 7:44 AM

      ohhhh yeahhh thats the way how its done....

      Delete
  2. Ganesh ShelarJuly 23, 2012 at 3:50 AM

    It is not recommended to touch SharePoint DB directly. If you touch, MS will not support. Is there any other way to do this(exclusing list template)?

    ReplyDelete
    Replies
    1. Gowri Sankar PokuriJuly 28, 2012 at 11:32 AM

      Yes, you are correct. Unfortunately we do not have any other option to reset ID. Due to this it is always recommended to create lists using list definition so that we can recreate similar list very easily and quickly.

      Delete
  3. Mahmoud FarahatFebruary 28, 2013 at 7:50 AM

    is it safe ????

    ReplyDelete
  4. Gowri Sankar PokuriFebruary 28, 2013 at 3:54 PM

    yes, it is safe. but we have to make sure that list does not contain any data.

    ReplyDelete
  5. AnonymousNovember 24, 2013 at 5:23 AM

    it is useful post thanks

    ReplyDelete
  6. YosiheyNovember 5, 2014 at 1:29 PM

    If I use Sharepoint Online, how works this solution?

    ReplyDelete

Post a Comment

Popular posts from this blog

Switch from Classic to Claims Authentication in SharePoint 2010

SharePoint 2013 tutorials, SharePoint 2010 articles, SharePoint interview questions: Switch from Classic to Claims Authentication in Sh... : In this article we will discuss how to switch from Classic Mode Authentication to Claims Authentication. $webApp = Get-SPWebApplication “http://web application URL/” $webApp.UseClaimsAuthentication = 1 $webApp.Update() $webApp.ProvisionGlobally() $webApp.MigrateUsers($True)
Read more

How to query list data using web service

In SharePoint, to query list data we can also use web services provided by SharePoint other than SharePoint API. We can call web services from two places one is JavaScript and another one is .Net client application. Here I will show you both methods. .Net client application To query list data we will use a web service called lists.asmx . 1. Create .net project either windows/console. 2. Add the web reference and the URL looks like http://<servername:portno>/_vti_bin/lists.asmx 3. Give the web reference name as ListsSvc . 3. Add the following code snippet to get list data. ListsSvc. Lists listService = new  ListsSvc. Lists (); // If the current user is having access you can use below code listService.Credentials = CredentialCache .DefaultCredentials; // In case if you want to pass specific user credentials listService.Credentials = new NetworkCredential ( "<username>" , "<password>" , "<domain>" ); string v...
Read more