How to resolve the 5000 item limit on a list.

Hi Bharath_001,

Thanks for the query.

A list can have up to 30 million items. And 5,000 is indeed an important number. It is the list view threshold which specifies the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked. To give you time to make alternative plans, you may be warned on the List Settings page when your list has exceeded 3,000 items. The warning contains a help link to this topic.

Why is it such a magic number? 

To minimize database contention SQL Server, the back-end database for SharePoint, often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it's more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.

Note: The actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site's configuration.

When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.

As Webbrewers3 mentioned in the reply below, as there are limitations, you may consider using multiple lists and avoiding exceeding the limit.

Meanwhile, here is an important article for your reference about working with the list view threashold:

Manage large lists and libraries in SharePoint (there are many workarounds inside).

Regards,
Tina

[Updated by Tina Zhou MSFT,  09:01 AM, Dec 9 , 2018 (UTC)]