Cleaning orphan database from SharePoint Farm

The issue:

You have patched your Sharepoint farm but if you run the configuration wizard and it is always failing on step 9 /10 on upgrade Operations and you have facing following error:

SharePoint Foundation Upgrade        SPWebApplicationSequence        ajy60        DEBUG        Web application [SPWebApplication Name=ContosoDB] has 1 IisSettings object        1a71389d-7618-d087-2401-036280754f67

10/17/2015 11:23:01.93        OWSTIMER (0x1920)        0x1614        SharePoint Foundation Upgrade        SPWebApplicationSequence        ajy61        DEBUG        Searching for sites with Server Comment = ContosoDB 1a71389d-7618-d087-2401-036280754f67

10/17/2015 11:23:02.36        OWSTIMER (0x1920)        0x1614        SharePoint Foundation Upgrade        SPWebApplicationSequence        ajy66        DEBUG        Adding a iis web site object with instance Id = [319138241]        1a71389d-7618-d087-2401-036280754f67

10/17/2015 11:23:02.36        OWSTIMER (0x1920)        0x1614        SharePoint Foundation Upgrade        SPHierarchyManager        ajyw5        ERROR        Attempt to register null pointer at:    at Microsoft.SharePoint.Upgrade.SPHierarchyManager.AddNextLevelObjects(Object current, IEnumerable nextObjects)     at Microsoft.SharePoint.Upgrade.SPWssWebApplicationSequence.AddNextLevelObjects()     at Microsoft.SharePoint.Upgrade.SPHierarchyManager.Grow(SPTree`1 root, Boolean bRecursing, SPDelegateManager delegateManager)     at Microsoft.SharePoint.Upgrade.SPHierarchyManager.Grow(SPTree`1 root, SPDelegateManager delegateManager)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively)     at

Then you have checked that ContosoDB which is not present in SQL server but why are you getting this error with that database . This is an orphan database record issue . SharePoint has still have information about that database the record about that database present in SharePoint configuration database (inside the Object table) but in real there is no database present in SQL server with that name.

Ok how can you verify this;
First find the related Web Application Id by powershell
$wa = Get-SPWebApplication -Identity http://contoso.com
$wa.Id

Then we need to check in SQL server by running some sql queries to get xml configuration of this web Application object:

SELECT TOP [Id],[ClassId],[ParentId],[Name],[Status],[Version],cast([Properties] as XML)
FROM [SharePoint_Config].[dbo].[Objects] where Id = ‘<Guid of the Web Application>’

And if you look in details for the related XML

OrphanRecordXml

You have verifed that if there is <fld type=”null” /> record in that xml.
Also we know that the deleted database’s id is 44c186d2-6581-4314-90bd-a00f3efe18e3

If you search for this ID in configuration database’s Object table for this content database;you will not find any record about it ;So it is an oprhan item.

Ok so far so good , I can hear your question that how we get rid off this with a supported way  ?
– Well you shouldnt change SharePoint databases manually , it is not supported by Microsoft .

Resolution is easy but It is a little bit tricky.
To remove that oprhan database we will run a single command.
We need the Web Application object in powershell
$wa = Get-SPWebApplication -Identity http://contoso.com
Then run this
$wa.ContentDatabases.Delete(“44c186d2-6581-4314-90bd-a00f3efe18e3”)

The tricky part when you run this you will get an error ! Upss did i something wrong , NO!

PS C:\Users\SPFarmAdmin> $wa.ContentDatabases.Delete(“44c186d2-6581-4314-90bd-a00f3efe18e3”)
Exception calling “Delete” with “1” argument(s): “Object reference not set to an instance of an object.”
At line:1 char:1
+ $wa.ContentDatabases.Delete(“44c186d2-6581-4314-90bd-a00f3efe18e3”)
+ CategoryInfo      : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : NullReferenceException

 Well after run this command , just ignore the error .Normally it fixes the configuration database and cleans the orphan record .
Not sure ??
Check yourself again the XML configuration of the Web Application from SQL server by following SQL again.
SELECT TOP [Id],[ClassId],[ParentId],[Name],[Status],[Version],cast([Properties] as XML)
FROM [SharePoint_Config].[dbo].[Objects] where Id = ‘<Guid of the Web Application>’

if you do not see the orphan   <fld type=”null” /> , then you good to go 🙂 You can run the configuration wizard again .

How to trim Audit Logs in SharePoint 2007&2010

Auditing enables administrators to keep a reliable log of what is happening with important content on a site collection. Administrators are able to retrieve the entire history of actions taken by a particular user and can also retrieve the entire history of actions taken during a particular date range.In SharePoint Content Database we have a table named  AuditData. This table  stores audit logs when “Auditing” enabled in Site or List Libraries. But once you enabled “Auditing” this table size will growth continously and  it will consume your storage space in your SQL Server quickly.At that point you’ll need to delete older audit logs which is stored in your content database

For SharePoint 2007 we have a STSADM command for clearing audit data for maintanence purporse (but it is depreciated in SharePoint 2010)

So clearing old auditlogs you may fallow

1)      Open a Command Prompt as Administrator Privilegdes in your one of SharePoint Server
2)      Change path to
cd c:\program files\common files\microsoft shared\web server extensions\12\Bin
3)  Run fallowing command change it as your content database name
stsadm –o trimauditlog –date 20120822 –databasename MyContentDatabaseName
Important: The audit entries before given date are permanently deleted after this operation has run

This operation is not done automatically by SharePoint 2007 (it is by design) .This responsibility has assigned to System Administrators for maintenance and shoud be done manually by periodically.For more information about trimauditlog you can check: http://technet.microsoft.com/en-us/library/cc706879.aspx

For SharePoint 2010  we have a dedicated TimerJob for doing this operation .Default schedule is set by monthly.

1)      Go to your Central Administration -> Monitoring -> Review Job definitions

2)      You can see in picture every site has own Audit Log Trimming Job. Select correct job for your actual site
3)      Click “Run Now” button.

I would like to inform you about someting when you run this timer job it will use the value of retention (for example 3 )  which you set in Site Settings-> Site Collection Audit Settings .
Even you set the “Automatically trim the audit log for this site” yes and set retention for 3 days (like in example) . the logs will not be deleted from Content Database until “Audit Log Trimming” timer job is run.After timer job runs the logs until retention value ( 3 days in example) will be deleted.

What if you set “Automatically trim the audit log for this site” as No . How could you clear old logs ?

There is another way to do it by using PowerShell . you can able to give here a date as parameter like stsadm command.

1)      Run SharePoint 2010 Powershell Console by administrator priviledges .
2)      Type fallowing commands:
$site = Get-SPSite http://yoursitecollectionURL
$date = Get-Date “22/08/2012”
#(You need to check date format , type $date and press enter)
$date
#Result like:  22 August 2012 00:00:00
#(and check the date is correct because it can be changed by regional settings. if date is in correct format )
$site.Audit.DeleteEntries($date)

You can fallow whats happening in background by tracing ULS logs in real time. And you can learn how many records are deleted.

//See you next article .

Performance issues on Sharepoint : Detect over 2000 items for Moss2007 and over 5000 items for SPS2010 in folders

Hi All,

As you know a performance problem is not an easy task because it can caused by anything in your system, slow queries, swap file configuration,hardware bottlenecks, long compile times,badly coded custom applications, other services etc and etc and etc . It could be anything.

In this article i am going to tell about just one issue to detect which can cause mostly sharepoint performance problems in other tens of thousands possibilities 🙂
Lets have a look Sharepoint Boundries article on technet about this thresholds.
http://technet.microsoft.com/en-us/library/cc262787.aspx
http://technet.microsoft.com/en-us/library/cc262787(v=office.12).aspx

For SharePoint 2010 :

List view threshold: 5000 item per view
Specifies the maximum number of list or library items that a database operation, such as a query, can process at the same time outside the daily time window set by the administrator during which queries are unrestricted.

For Sharepoint 2007 it is defined as 2000 item per view.

So what does this rules means. Simply i could say you have one million items in your list  but you have to limits your queries by max 2000 (5000 for sharepoint 2010) items to return becuase of performace issues it is by design of SharePoint Database Architecture. In SharePoint 2010 you can store 30,000,000 item on list and it is far far away than the item per view threshold but if you over this threshold that is a problem.

Then what we can do :
There are so many articles that define what could you do about resolve this problem if you have a this kind of performance issues.
Here some good resources:
White Paper: Working with large lists in Office SharePoint® Server 2007
http://office.microsoft.com/download/afile.aspx?AssetID=AM102377231033
Designing large lists and maximizing list performance (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc262813.aspx
http://blogs.technet.com/b/speschka/archive/2009/10/27/working-with-large-lists-in-sharepoint-2010-list-throttling.aspx
http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=162

Basically you can have two options. (I am not tell how could you do that it is out of scope for this article)
1) Limit any query which is send to SQL Server from Sharepoint must return lower count of results than threshold.
You may do it by Sharepoint Gui or by use some code technics for your custom codes.Even if you have set the view limits or row limits in your code or SharePoint gui still some open points to query this lists by not any limitation for example Windows Explorer view or some queries which created by workflow actions or not managable 3rd party applications .
So second step is folderize .
2) Use the Folders . Make and keep your list content in folderize hiearchy.

Ok. Now i assume that you learn item per view threshold and how to fix or optimize your large lists. But imagine that you have so many lists and subsites in your site content database.How could you find these lists ?

Unfortunately there is not any reporting tool exists in oob. You can use Gui’s but it will take so much time. So running some sqls for report purpose will help us.
By the way don’t forget that you never change any data in SharePoint Database it is not permitted and makes your system out of support.

Sharepoint has use 3 active tables for most of your queries has read from them when you working ,browsing ,updateing or deleting list operations.
These are AllLists , AllUserData ,AllDocs tables. Very simply AllLists tables has store your lists information, AllUserData has store your list items metadata ,and AllDocs has store that if you have attached any document for lists.

Fallowing Query will return us in your item counts grouped  by list

select webs.Title,webs.FullUrl, tp_Title,tp_ServerTemplate,UserDataCount,DocsCount
from AllLists left outer join
(select tp_ListId, COUNT(*) as UserDataCount from AllUserData
group by tp_ListId ) E On E.tp_ListId = AllLists.tp_ID
left outer join
(select ListId, COUNT(*) as DocsCount from dbo.Docs
group by ListId ) P ON AllLists.tp_ID = P.ListId
left outer join Webs ON AllLists.tp_WebId = Webs.id
order by DocsCount desc 

  

Title :is Web Title:
FullUrl : is Web Url
tp_Title : is List Title
tp_ServerTemplate : is list type id . You can find list type id definition in this article
http://blog.bugrapostaci.com/2010/09/22/sharepoint-list-type-numbers/
UserDataCount : record count of stored in AllUserData table for the list. (Deleted items not included)
DocsCount : record count of stored in AllDocs table for the list (Deleted items not included)

As you see on the picture for the first row the list  has contains 39793 record in AllUserData and 118724 data in AllDocs tables.But it does not give us any information about folder counts .
So if you run fallowing query

select tp_listid,webs.Title,webs.FullUrl, tp_Title,tp_ServerTemplate,UserDataCount,DocsCount, 
	(select COUNT(*) from (
	select COUNT(*) as c from Docs A
	where A.listId = AllLists.tp_ID
	Group by A.Dirname having count(*) > 2000 ) Q) as OverLoadDocsFolders,
	(select COUNT(*) from (
	select COUNT(*) as c from UserData B
	where B.tp_listId = AllLists.tp_ID
	Group by B.tp_dirname having count(*) > 2000 ) Z) as OverLoadUserDataFolders
from AllLists left outer join 
(select tp_ListId, COUNT(*) as UserDataCount  from AllUserData 
group by tp_ListId ) E On E.tp_ListId = AllLists.tp_ID
left outer join 
(select ListId, COUNT(*) as DocsCount  from dbo.Docs
group by ListId ) P ON AllLists.tp_ID = P.ListId
left outer join Webs ON AllLists.tp_WebId = Webs.id
order by DocsCount desc 

You can also add fallowing expression to your SQL for just query  lists which contains over size folders
where OverLoadDocsFolders > 0 or OverLoadUserDataFolders >0

OverLoadDocsFolders : the folder count of contains over 2000 items for the list.
OverLoadUserDataFolders : the folder count of contains over 2000 items for the list
Which means the first and sixth record’s list are not have an over loaded folder. They are folderized well.
The other lists are have at least one overloaded folders.

If you want to get more details about over size folders of a list you can query with fallowing SQL

select DirName,COUNT(*)from Docs where Docs.ListId=‘<List Guid Here>’
group by docs.DirName havingCOUNT(*)>2000

select tp_DirName,COUNT(*)from UserData where tp_ListId=‘<List Guid Here>’
group by tp_DirName havingCOUNT(*)>2000

(By the way 2000 is for MOSS 2007 if you have SharePoint 2010 you may replace all 2000 to 5000 .)

SharePoint 2010 restoring content database manually from sharepoint backup folder

Hello Everyone ,

In this article i am going to tell you how could you extract and restore a specific content database from backed up Sharepoint Full-Farm backup.
First lets have look our target content database which we want to restore . In my example the database name is “WSS_Content_5050” .

1) Open your Sharepoint Full Farm Backup folder. If you don’t know the backup folder  you can find  from Central Administration web site.

2) As you noticed when we order items by size in Sharepoint Full-Backup folder ( “spdbr0000”) you can see the biggest files . That files as usually  SQL backup files of sharepoint databases but which file is our content database ?

3) If you order items by Type you can find the file which we are looking for  named “spbackup.xml” .this file our backups index file in xml format.

4) than open this xml file in any editor and search your content database name in this file .

5) as you notices that “0000012A.Bak file . This is our content database’s backup file.
6) Copy this file to your SQL Server and now you can restore this file (if it is not corrupted)   via Sql Server Management Studio .

Sharepoint 2010 Misconception with RBS

After Sharepoint 2010 sp1 is released , some new features and improvements has been spreading in several blogs.And one of the most interested the new content database limits. In SP1 , by defining specific requirements for large data storage in SharePoint, Microsoft is able to increase the supported limits for data storage in SharePoint.
Sotware boundaries and limits and the primary changes are:

  1. For a SharePoint content database up to 200 GB there are no special requirements and this limit is included for consistency.
  2. For a SharePoint content database up to 4 TB you need to additionally plan for the following two requirements:
    • Requires disk sub-system performance of 0.25 IOPS per GB, 2 IOPS per GB is recommended for optimal performance.
    • Requires the customer to have plans for high availability, disaster recovery, future capacity, and performance testing.
    • And you need to review additional considerations in the TechNet Boundaries and Limits article.
  3. For a SharePoint content database over 4TB specifically for a Document Archive scenario you are required to additionally plan for the following:
    • SharePoint sites must be based on Document Center or Records Center site templates and must be an archive scenario where less than 5% of content is actively read from each month and less than 1% of content is actively written to.
    • Do not use alerts, workflows, link fix-ups, or item level security on any SharePoint objects in the content database. Note: document archive content databases can be the recipient of documents as a result of Content Routing workflow.
  4. Other specific limits changes being made at the same time:
    • A new limit of 60million items in any one SharePoint content database
    • The specific 5 TB limit per SQL Server instance has been removed.  Instead you should work with a SQL Server professional to plan for database storage.

 Also these new extended limitation is not our artile issue if you need more info see:
http://technet.microsoft.com/en-us/library/cc262787.aspx

And also RBS has some improvements:
The Microsoft SQL Server FILESTREAM RBS provider is now supported allowing for iSCSI connections to lower cost NAS storage. The SQL Server RBS provider is one option for RBS use with SharePoint and there are a number of ISV’s who also have RBS providers.

What about RBS misconception: Misconception is that RBS can provide extra database size limit ? Answer is “No”:

First of all Microsoft Says :
“We are clarifying that Remote Blob Storage (RBS) does not offer a way to increase the SharePoint content database size limits. The content database supported size limits apply to the sum of data stored in SQL Server plus data stored outside of SQL Server using an RBS provider”

Even if you use RBS or not , Sharepoint boundries is strict and you should not able to change it. And this is not only just Content DB size limit. it is also valid for all Sharepoint Limitation,  including Maximum document size limit of 2 GB for one file.

See you in next article…