Reduce the Size of Usage and health database of SharePoint 2013 as SQL CPU load is high

Reduce the Size of Usage and health database of SharePoint 2013 as SQL CPU load is high

******************************************************************************************************************************

SharePoint Usage logging database grows very fast and it cause the storage problem most of the time in sharepoint server 2010/2013.

To reduce the size of the logging database or to purge the old data from the logging database we can these steps. 
To find the Logging Database Name go to following path:-
Login to Central Administration -> Monitoring -> Configure Usage and health data collection->
 

The Logging database stores the following things:-

• ULS Logs from 14 Hive\LOGS
  
• Raw Resource Usage Data
  

 This data is used by Web Analytics in Sharepoint. Two timer jobs Microsoft SharePoint Foundation Usage Data Import and Microsoft SharePoint Foundation Usage Data Processing are used for importing and processing the raw data in the web analytics database.
By Default the system retains 14 days of Usage data in this database. By following PowerShell command we can check and change the value of retention days of this database. Get-spusagedefinition and Set-SPUsageDefinition  
Try  Get-SPUsageDefinition

 Now you need to find which table is taking most of the space inside the WSS logging Database. 

You Can check the same from the SQL Server
Login to Sharepoint Management Studio -> Select your logging Database (Right Click) -> Reports- > Standard Reports -> Disk Usage by Top Tables.
 

 Say RequestUsage* is taking most of the space inside WSS Logging database so  you can bring down the retention period of  Page Request Event.
Or if you want you can bring down the retention period for all the Events for any number of days from 14 to 1. 
Use the following command to do so.

Set-SPUsageDefinition -Identity"Page Requests" -DaysRetained 3

Run the same Power Shell command again to cross check "Get-SPUsageDefinition" and check the Retention period is changed or not.
 

After that we need to run the two timer jobs to clean the old data 'Microsoft SharePoint Foundation Usage Data Import' and 'Microsoft SharePoint Foundation Usage Data Processing'.
Go to Sharepoint Central Administration -> Monitoring -> Configure Usage and health data collection-> Log Collection Schedule.
And it will take you to the timer jobs.
 

 Now Click on both the Job Definitions one by one and hit 'Run Now' to run the timer jobs


Once the timer jobs is run you can check and confirm database has released the space.

Error while unprovisioning of Sync DB $syncDB.Unprovision()

My User profile synchronization service is stop. it wad hang in startup and then i stop / unprovision using $syncDB.Unprovision()

when i run following command

$upa.ResetSynchronizationDatabase()
i am getting following error
Exception calling "ResetSynchronizationDatabase" with "0" argument(s): "Cannot
open database "Sync DB" requested by the login. The login failed.
Login failed for user 'domain\farmaccount'."
At line:1 char:34
+ $upa.ResetSynchronizationDatabase <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Resolution :

If sync database status is set to single, then change it to multi user. Also make sure, farm account have dbowner access on it.

If issue exist, try to execute same commands using new powershell window session.

SharePoint 2013 – User profile – ResetSynchronizationDatabase : Operation is not valid due to the current state of the object

SharePoint – User profile – ResetSynchronizationDatabase : Operation is not valid due to the current state of the object

Issue

I was trying to reset the SharePoint UPS sync database and came across below error message:

PS C:\> $upa.ResetSynchronizationDatabase()
 Exception calling "ResetSynchronizationDatabase" with "0" argument(s):
 "Operation is not valid due to the current state of the object."
 At line:1 char:1
 + $upa.ResetSynchronizationDatabase()
 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
 + FullyQualifiedErrorId : InvalidOperationException

Event ID: 3, 5586 

 .Net SqlClient Data Provider: 4060 :  : System.Data.SqlClient.SqlException: Cannot open database "SP_xxxx" requested by the login. The login failed.

Login failed for user 'xxxxxxx'.

.Net SqlClient Data Provider: 924 : UpdateRequestStatusForCompletedActionWorkflows : System.Data.SqlClient.SqlException: Database 'SP_XXX is already open and can only have one user at a time.

Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Database 'SP_xxxx is already open and can only have one user at a time.

Resolution

Sync Database is in SQL availability group or set to single user.



Remove Sync DB from SQL availability group and set DB to multi user and make sure User Profile Sync account(farm account) have DBOwner access of sync db.