Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors

09/12/2015 1 Comment

Hello All,

After many performance issue investigations,  we have released at 10th of October 2015  following kb article for about “Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors”
https://support.microsoft.com/en-us/kb/3103194

In this article scope we make availability and  some flexiblity for database maintenance operations about  preventing “outdated update statistics” for DBAs , and now you are not depending just only SharePoint Daily Timer job which responsible update database statistics by using the proc_updatestatistics SQL procedure anymore.

Our TechNet article “Best practices for SQL Server in a SharePoint Server farm” has now been updated with the same guidance and cross referencing the new KB article.

Do not enable auto-create statistics on SharePoint content databases. Enabling auto-create statistics is not supported for SharePoint Server. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling auto-create statistics on a SharePoint database can significantly change the execution plan of a query. We recommend updating the SharePoint content database statistics daily using the FULLSCAN option from SQL Server. Although SharePoint does have a timer job to update statistics by calling proc_updatestatistics, we strongly recommend implementing a scheduled maintenance plan from SQL Server to ensure database statistics are reliably updated on a daily basis. For more information, see Outdated database statistics.

Best practices for SQL Server in a SharePoint Server farm
https://technet.microsoft.com/en-us/library/hh292622.aspx

Now ; to prevent  potential service outages, SQL Server maintenance plans can be implemented to keep SharePoint content database statistics updated by using the FULLSCAN option and it can be done manually by DBAs

When implementing the SQL Server maintenance plan to update the statistics on your SharePoint databases, it is not required to disable the job from SharePoint. However, because these maintenance tasks perform similar functions from both locations, it is permissible to disable the timer job from the SharePoint farm.

Advertisement

Filed under Sharepoint, SharePoint 2013 Tagged with , , , , , , , , ,

Cleaning orphan database from SharePoint Farm

09/11/2015 Leave a comment

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 .

Filed under Sharepoint Tagged with , , , , , , ,

How to trim Audit Logs in SharePoint 2007&2010

05/09/2012 Leave a comment

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 .

Filed under Sharepoint, Sharepoint 2010 Tagged with , , , , , , , , , , ,

Which version of SharePoint supports Microsoft SQL Server 2012

17/04/2012 Leave a comment

Only SharePoint 2010 SP1 (14.0.6029) and higher versions supports Microsoft SQL Server 2012 . Currently for MOSS2007 or WSS3.0 is not supported.
http://support.microsoft.com/kb/2460045

Filed under Sharepoint Tips & Tricks Tagged with , , , , , , , ,

“The specified database is not a valid synchronization database” issue

16/04/2012 Leave a comment

Good Day Everyone,

Let me guess , if you are reading this article possible you may in the middle of a restore or migration issue and not able to restore User Profile Service Application .
This warning means you can not restore old sycronization database (default name is Sync DB) .This is a by design issue and you do not allowed to do it. that’s why you are getting fallowing warning.  For resolution just create a new Syncronization database. Don’t worry about the content in it because SharePoint will rebuild it once you configured User Profile Service Application correctly.

 

Filed under Sharepoint Tips & Tricks Tagged with , , , ,

← Older posts