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!!