Blind spots in Usage file and Web Analytics Reports

In my previous post (Troubleshooting SharePoint 2010 Web Analytics), I referenced a problem that can occur when exceeding the daily partition size for the LoggingDB, which generates the ULS message “[Partition] has exceeded the max bytes”. Below, I wanted to provide some additional info on this particular issue and help identify some options if this occurs. As an aside, this post only applies if you are missing portions of Usage data - think blind spots on intermittent days or user activity regularly sparse for the afternoon/evening. If this fits your scenario - read on. But if Usage logs are outright missing, go check out my Troubleshooting post first.

Background on the problem:

The LoggingDB database has a default maximum size of ~6GB. However, SharePoint evenly splits this total size into fixed sized logical partitions – and the number of partitions is defined by the number of days to retain Usage data (by default 14 days). In this case, 14 partitions would be created to account for the 14 days of retention. If the retention were halved to 7 days, the LoggingDBwould be split into 7 corresponding partitions at twice the size. In other words, the partition size is generally defined as [max size for DB] / [number of retention days].

Going back to the default scenario, the “max size” for the LoggingDB is 6200000000 bytes (~6GB) and the retention period is 14 days. Using our formula, this would be [~6GB] / [14 days], which equates to 444858368 bytes (~425MB) per partition per day. Again, if the retention were halved to 7 days (which halves the number of partitions), the resulting partition size becomes [~6GB] / [7 days], or ~850MB per partition.

From my experience, when the partition size for any given day is exceeded, the usage logging for the remainder of the day is essentially thrown away because SharePoint won’t allow any more to be written to that day’s partition. The only clue that this is occurring (beyond truncated usage data) is an error such as the following that gets reported in the ULS:

04/08/2012 09:30:04.78    OWSTIMER.EXE (0x1E24)    0x2C98    SharePoint Foundation    Health    i0m6     High    Table RequestUsage_Partition12 has 444858368 bytes that has exceeded the max bytes 444858368

It’s also worth noting that the exact bytes reported (e.g. ‘444858368’ above) may slightly vary among farms. For example, you may instead see 445226812, 439123456, or something else in the ballpark. The exact number itself doesn't matter, but this error message intends to indicates that the reporting usage has exceeded the partition size for the given day.

What it means:

The error itself is easy to miss, which can lead to substantial gaps in the reporting data (your mileage may vary) if not identified. At this point, I can only advise to periodically check the ULS logs for this message. Down the road, I plan to explore if [Developing a Custom Health Rule] could be leveraged to identify the issue (If you've ever built Custom Health Rules, I'd be interested to hear about your experiences).

Overcoming this issue also poses a challenge, with workaround options including:

• Lower the retention

Because the partition size is generally defined as [max size] / [number of retention days], the first option is to lower the number of days to retain the data – the lower the retention, the lower the divisor and thus a bigger partition. For example, halving the retention from 14 to 7 days would halve the number of partitions, but double the partition size to ~850MB (e.g. [6200000000 bytes] / [7 days] = ~850GB partitions). Lowering it to 2 days would result in two ~3GB partitions… and so on.

• Recreate Update the LoggingDB with an increased size

The property MaxTotalSizeInBytes is exposed by OM code for the SPUsageDefinition object and can be updated with the example PowerShell snippet below. However, updating this value has no immediate impact because this size only applies when creating a LoggingDB. Therefore, you must create a new LoggingDB for the Usage Service Application. The gotcha: this effectively deletes all prior Usage data because the Usage Service Application can only have a single LoggingDB.

Here is an example snippet to update the "Page Requests" Usage Definition: $def=Get-SPUsageDefinition -Identity "page requests" $def.MaxTotalSizeInBytes=12400000000 $def.update()

Create a new Logging database and attach to the Usage Service Application using the following command: Get-spusageapplication | Set-SPUsageApplication -DatabaseServer <dbServer> -DatabaseName <newDBname>

 Updated (1/19/207)   I wrote this a long time and since then, you no longer have to recreate the LoggingDB. Instead, you can update the property with the Set-SPUsageDefinition PowerShell, such as the following. This PowerShell would invoke the correct underlying stored procedure against the Usage DB and update an applicable object in the Config DB as well.

$pageReq = Get-SPUsageDefinition | ?{$_.Name -eq "Page Requests"}
Set-SPUsageDefinition $pageReq -MaxTotalSizeInBytes 100000000000

Updated (5/10/2012): Once the new database has been created, you can confirm the setting has truly taken by running the following SQL Query (be sure to replace the database name in the following query with the name provided in the PowerShell above)

SELECT * FROM [WSS_UsageApplication].[dbo].[Configuration] WITH (nolock) WHERE ConfigName LIKE 'Max Total Bytes - RequestUsage'

Comments

• Anonymous
  May 16, 2012
  It collaborates and gets interaction with several ideas and options plenty. This text has taught me several new ideas and enhanced my information plenty. We might realize such blogs terribly rare. You created this idea simple to know it. Thanks for your article.

• Anonymous
  May 16, 2012
  I'm fascinated with your article. This article is very facilitating a good quality. It is very informative and superb. I like this article. It is certainly very useful for me to invest. Thank u very much for providing us such good information through your article.

• Anonymous
  October 17, 2013
  Right on...exactly what I'm looking for..Thanks

• Anonymous
  January 30, 2014
  This is a really rare analytic report of the issue. Good job on that on and Thank you :)

• Anonymous
  March 27, 2014
  You don't really need to create a new DB to update "MaxTotalSizeInBytes" for a Usage Definition. Instead you can use: Set-SPUsageDefinition "Page Requests" -MaxTotalSizeInBytes 21474836480

(21474836480 = 20GB)

Unlike $def.Update(), the PowerShell command internally calls a stored procedure in the database that updates the value

• Anonymous
  June 13, 2014
  Hi Wassim - there is nuance to what I described. "The property MaxTotalSizeInBytes is exposed by OM code for the SPUsageDefinition object and can be updated with the example PowerShell snippet below. However, updating this value has no immediate impact because this size only applies when creating a LoggingDB. Therefore, you must create a new LoggingDB for the Usage Service Application." So I completely agree that the value will get updated immediately by PowerShell... but this value is ONLY used when creating the new LoggingDB. In other words, updating this value has NO IMPACT until a new database gets created. *Admittedly, I wrote this article two years ago... at the time, I checked source code to confirm what I've noted above - it's only used at the time the logging DB gets created. It is entirely possible that a code change was put in that changes this behavior, but offhand I'm not aware of one.

• Anonymous
  July 28, 2014
  After following the directions in this article about 6 months ago, SP1 Installed in June 2014, we are now seeing the same Event ID 8319 showing up. I guess this is not a permanent fix.

• Anonymous
  October 02, 2014
  Thanks bspender for the efforts in writing this article. :)  i have tried this solution it works for a day or two but it comes back after few days. Is there any permanent solution to this issue

• Anonymous
  October 02, 2014
  The comment has been removed

• Anonymous
  October 08, 2014
  Hi, I'm afraid to leave my feedback seeing no response for the last couple of months :-). Seeing the same issue in my SharePoint 2013 farm as the above two commentators. Re-created the DBs few times already...in months time the error comes back ...really need suggestions on how to fix this. Appreciate any feedback from anyone. Many thanks in advanced!

• Anonymous
  January 04, 2015
  The comment has been removed

• Anonymous
  February 13, 2015
  Can some one suggest how to identify the usage log values and then fix a partition size that will be able to accommodate the usage logs?

• Anonymous
  February 26, 2015
  I'm having this issue too and was wondering, does this have any impact on the servers other then giving off these alerts?  My farm is still fast, and having no issues that I can see. Thanks!

• Anonymous
  February 22, 2016
  alstechtips.blogspot.com/.../sharepoint-2013-table.html kerseub.wordpress.com/.../records-stops-in-logging-database-then-came-back-the-day-after