Home > SharePoint 2010 > Records stops in logging database, then came back the day after

Records stops in logging database, then came back the day after


Sharepoint has a great feature, the “Usage Data Collection“. This feature allows to write in database (Wss_Usage or Wss_Logging or whatever the name you set). Every single actions that happen on your Sharepoint Farm, from user’s visits, to the duration of timer job execution including Windows performance monitor values can be stored into this database. But sometimes, record stops for a day, and came back the day after.

From documentation, a job timer (named Microsoft SharePoint Foundation Usage Data Import) is in charge to write into a specific table of this logging database. To do this, a data table is provided by Day of month (up to 32) AND by recorded event type:

All these event are summarize in a SQL view dedicated to the event type:

SharePoint writes all these data using a stored procedure dbo.prc_Insert<UsageDefinition>.

Digging further we can see that when writing data, the size of the current partition (data table that store data for the day) is checked. It must be lower than the maximal storage limit (in bytes) divided by the number of retention days set for the event selection (Check line 31-32 of the procedure). This 2 parameters are extracts from the dbo.Configuration data table by the dbo.fn_GetConfigValue
function (In Functions/Scalar-valued Functions) of this same Logging database (not the Farm configuration).


So, to raise the daily limit we can:

  • Lower the number of retention days.
  • Raise the maximale total bytes limit.

Of course, we won’t work directly on the database, PowerShell allows to specify the retention period. For instance, for Page Requests Usage definition:

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

To update the total limit we have to be a bit smarter, the default size is 6000000000 bytes, about 5.6GB, let’s update is to 10GB =>10*1024*1024*10214=>10737418240

$definition= get-SPUsageDefinition -Identity "Page Requests" 
$definition.MaxTotalSizeInBytes=10737418240 
$definition.Update()
 

Configuration data table is not update immediatly. In fact, I could not find how the parameter is save in database, I could not find any relevant timer job (any info about that is welcome), but anyway, the day after, retention period is fairly saved 🙂 but not the max limit size:(

So, to update this parameter, we don’t have any other choice but updating the database :

update WSS_Logging.dbo.Configuration set ConfigValue=‘10737418240’
where ConfigName=‘Max Total Bytes – RequestUsage’

where RequestUsage is the event type you want to extend …

Now, our limit is raised:

Doing this database update can introduce questions about editor’s support. But from documentation, this logging database has a specific support status:

  • « Moreover, the logging database is the only SharePoint Server 2010 database for which you can customize reports by directly modifying the database.” from article Understanding the Logging Database
  • “The Usage and Health Data Collection database is the only SharePoint database that supports schema modifications” from article Database types and descriptions.

So I think that this operation preserve Microsoft support, but don’t tell them I told you!!

Advertisements
Categories: SharePoint 2010
  1. Gnana
    April 9, 2013 at 9:24 am

    update [WSS_UsageApplication].[dbo].[Configuration] set ConfigValue=’10737418240′ where ConfigName=’Max Total Bytes – RequestUsage’

    Not Updating

  2. May 5, 2016 at 5:50 pm

    This “critical” noise really bugs me every time I open the ULS logs or the event logs. Every time I fix it the problem returns a few days later. So I made a recommendation at uservoice which hopefully the SharePoint Product Group will see and take seriously someday. If you would also like to see this bug get fixed, click the link below and vote it up!

    https://sharepoint.uservoice.com/forums/330318-sharepoint-administration/suggestions/13744533-fix-critical-errors-noise-table-spdistributedca

    Fix “Critical Errors” noise “Table SPDistributedCacheCalls_Partition has … exceeded the max bytes”

    Another idea for someone with Premier agreement with Microsoft to consider would be opening a support case with the SharePoint Admin team and seeing if they can come up with a permanent fix for this or seeing if they can submit it as a bug to the SP Product group.

  3. Ron
    August 12, 2016 at 7:45 pm
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: