Endless DB upgrade & PSConfig Issue

Issue description

===========

Upgrading one of the Content Databases is taking more than 24 hours. Having the DB attached to the farm make the Psconfig to run endlessly blocking the farm upgrade.

Analysis :

• We tried to initiate upgrade-spcontentdatabase command and found that there are nothing much getting logged on the uls logs with normal logging level

• Parallelly checked the SQL level activity using the script while Upgrade command is running on SharePoint server

select der.blocking_session_id as blking_spid
,der.session_id as spid
,der.status
--,des.deadlock_priority
,der.wait_type
,der.wait_time
,der.cpu_time as cpu_tm
,der.writes
,der.reads
,der.logical_reads as L_reads
,der.Command
,der.row_count
,der.percent_complete as '%complete'
,db_name(der.database_id) as dbname
,SUBSTRING(SQLText.text, statement_start_offset/2 + 1,2147483647) as query
,object_name(SQLText.objectid,der.database_id) as object_name
,der.total_elapsed_time
,des.host_name as c_host
,dec.client_net_address as c_ip
,des.program_name
,des.login_name
,dec.auth_scheme
,dec.net_packet_size as packetsize
,dec.net_transport
,der.plan_handle
,SQLPlan.query_plan
,deqmg.used_memory_kb
from sys.dm_exec_requests der join sys.dm_exec_sessions des
on (der.session_id = des.session_id) join sys.dm_exec_connections dec
on (des.session_id = dec.session_id) left outer join sys.dm_exec_query_memory_grants deqmg
on (der.session_id = deqmg.session_id)
cross apply sys.dm_exec_sql_text(der.sql_handle) as SQLText
cross apply sys.dm_exec_query_plan(der.plan_handle) as SQLPlan
where der.session_id > 50
and der.session_id <> @@SPID
order by der.total_elapsed_time desc

• We found that the function fn_convertversiontonumber is being executed for a very long time.

• This function works on the siteversions table and found that we have more than 50K items in the site version table which is really huge

• Executed the following Query to check if there are any orphaned items in the siteversion table, we could see that there are around 7.5K items

Select * FROM SiteVersions
LEFT OUTER JOIN
AllWebs
ON SiteVersions.SiteId = AllWebs.SiteId
AND SiteVersions.WebId = AllWebs.Id
WHERE SiteVersions.WebId != 0x
AND AllWebs.Id IS NULL;

• As it’s a test farm and test DB , removed it from SQL directly using

 DELETE SiteVersions
FROM   SiteVersions
 
       LEFT OUTER JOIN
       AllWebs
       ON SiteVersions.SiteId = AllWebs.SiteId
          AND SiteVersions.WebId = AllWebs.Id
WHERE  SiteVersions.WebId != 0x
       AND AllWebs.Id IS NULL;

• Executed below sql commands to get the count of subsites present into sitecollection :

1st :
 SELECT COUNT(*) FROM AllSites WITH (NOLOCK)
 
 2nd :
 SELECT COUNT(*) FROM AllWebs WITH (NOLOCK)
 
 3rd :
 SELECT SiteId, COUNT(DISTINCT Id) AS WebCount FROM AllWebs WITH (NOLOCK) GROUP BY SiteId ORDER BY WebCount DESC, SiteId
 
 4th :
 SELECT SiteId, COUNT(DISTINCT Id) AS WebCount FROM AllWebs WITH (NOLOCK) WHERE DeleteTransactionId != 0x GROUP BY SiteId ORDER BY WebCount DESC, SiteId
 
 5th:
 SELECT SiteId, COUNT(DISTINCT WebId) AS WebCount, COUNT(DISTINCT Id) AS SiteVersionCount FROM SiteVersions WITH (NOLOCK) WHERE WebId != 0x GROUP BY SiteId ORDER BY WebCount DESC, SiteId
 

• We came to know that site has 14099 subsites within it .. its huge number ..

RCA :

• We see that one of the site collections has 14K+ subsites in it.

• This has 42K + siteversion table entries too.

• The software boundaries and limits documentation indicates that we can have maximum of 2000 webs per site collection. Post that we may experience this issue.

When having more than   2,000 subsites, the performance of actions such as executing PSConfig when   adding a new server to an existing farm, or after installing SharePoint   updates may drastically decrease.
Executing the stsadm -o checklocalupgradestatus operation,   or the daily execution of the Product   Version Job timer job may take many hours to complete.
Browsing the Review database status page   (<your_SharePoint_CentralAdmin_URL>/_admin/UpgradeStatus.aspx) on the   Central Administration web site may result in a timeout.

• Also we can only delete items from site version table only when they are orphans. Query will help us identify if there are webs present in the site map table where the web information is Not in the webs table. That means when a web is deleted, if the reference is removed from web table and not removed from siteversion table, such entries can only be removed.

Suggested Resolution :

Recommendation is to move some of the subsites to a different DB. This can be done in 2 ways ,

1) move the subsite as a site collection on new DB or

2)create new site collection in new DB and move the subsite as subsite there.

In both the cases the subsite URL will change.

More over as if you have sites customized, please test and make sure that the customizations are taken care while we move. This may need developer support as well.