I was recently helping a customer investigate “blind spots” in their Web Analytics reports. I use the term “blind spot” because there was data present in the reports, however there were intermittent days or user activity which was either missing or occasionally sparse. As ever, first thing we need to establish is the fault and what better than ruling out the obvious; find a correlation trace from the ULS logs for the Microsoft SharePoint Foundation Usage Data Processing timer job. Open your ULS viewer of choice and load in a few days worth (if the logs are on the large size, have a look at the time the timer job is set to execute and just load in the relevant times that cover the period around it). The ULS category you want to be filtering on is “Usage Infrastructure” OR “Timer Job job-usage-log-file-processing”, that is to say both of them with an OR statement. Most likely, you will quickly see a message stating the logging partition size is too large, for example:
Table RequestUsage_Partition1 has 443277312 bytes that has exceeded the max bytes 442857142
So from this we can establish the RequestUsage definition is the failing component. The default logging database size for RequestUsage definition is ~6GB with 14 day retention; partition sizes are calculated by database size divided by days retention; 6GB / 14 days = 428 MB per partition (roughly). Using the example ULS message above, it now becomes pretty obvious where our problem lies! Before proceeding, we thought it best to take a belt-and-braces approach and see if we could give ourselves two chunks of evidence to support our suspicions. Next task is to confirm the data collection is actually being processed (and yes, opening the SharePoint databases is taken at entirely your own risk!). From SQL Management Studio, connect to your SharePoint instance that hosts the Usage and Health Service Application and open a new query window. Execute the following query:
SELECT TOP 100 * FROM [SP2013_SRV_UsageAndHealth].[dbo].[RequestUsage] WHERE PartitionId = 'RequestUsage_Partition1'
You will want to replace the PartitionId with whatever ID was shown in the ULS message. If you have recent entries, it’s safe to say your data collection is processing. Now, depending on the environment and requirements you are working to, the fix may vary. The customer was happy for me to take point and work through a solution for them, so here we go…
Increase RequestUsage Partition Size
In order to get the RequestUsage processing again, the size of the partitions must be increased to a size that would not be exceeded beyond the maximum allowed value. Although this process does not introduce any data loss, it does reduce the retention period – be very aware and clear of your customers’ requirements. Using some pretty simple maths (with our nice formula from the example previously), if we halved the retention period (6GB / 7 days) we would have a partition size of approximately 877MB, plenty.
Set-SPUsageDefinition -Identity "Page Requests" -DaysRetained 7
Those pesky ULS messages will now have disappeared.
Increase Logging Database Size
Those that enjoy a good equation may be wondering why on Earth I didn’t just change the Logging database size, instead of reducing the retention period. Well, you’d be right, this is an option… though it will result in data loss while maintaining the retention period, you have been warned! To check the accurate size of the Logging database rather than my crude estimation at 6GB, execute the following SQL query:
SELECT * FROM [SP_SRV_UsageAndHealth].[dbo].[Configuration] WITH (nolock) WHERE ConfigName LIKE 'Max Total Bytes - RequestUsage'
This will show the exact size; default is 6200000000 bytes. So to adjust this value, we hop back to our SharePoint Management Shell (with elevated privileges of course) and execute the following cmdlet:
$SPUsageDefinition = Get-SPUsageDefinition -Identity "Page Requests"
$SPUsageDefinition.MaxTotalSizeInBytes = 12884901888
$SPUsageDefinition.Update()
I chose 12884901888 bytes for no other reason that to make it exactly 12GB. Now disable both the Usage and Health collections (Central Administration > Monitoring > Configure usage and health data collection) and apply the changes. From SQL Management Studio, edit the database name to ensure you don’t overwrite it. Next, create a new Logging database and attach it to the Usage Service Application by executing the following cmdlet from Management Shell:
Get-SPUsageApplication | Set-SPUsageApplication -DatabaseServer "SharePointSQL" -DatabaseName "SP_SRV_UsageAndHealth"
Of course, replace the DatabaseServer and DatabaseName with the variables to suit your environment. Now, you need to edit the user mapping for the Crawl account on the new database and grant the SPReadOnly database role. Re-enable both the Usage and Health collections and apply the changes. Lastly, start the Microsoft SharePoint Foundation Usage Data Processing timer job and check for successful completion (phew!).
You will want to monitor usage and health data over the next few days as usage reports only show the previous day collections. Also, if you want to double-check your configuration has taken before switching Usage and Health collections back on, you can execute this SQL query:
SELECT * FROM [SP_SRV_UsageAndHealth].[dbo].[Configuration] WITH (nolock) WHERE ConfigName LIKE 'Max Total Bytes - RequestUsage'
PowerShell Notes
Finally, you all know by now that I love PowerShell, so here are some nice cmdlets to add to your SharePoint utility belt…
- Manually start the Usage Data Processing timer job:
Get-SPTimerJob | Where-Object { $_.name -eq "job-usage-log-file-processing" } | Start-SPTimerJob- Get detailed information about the Usage and Health Service Application:
Get-SPUsageApplication | fl
- Get detailed information about the Usage Definitions:
Get-SPUsageDefinition | fl
