With the new Sharepoint Logging database we now have the possibility of storing ULS trace logs in tables in this database. The responsible for this process is a Sharepoint Job that periodically moves ULS logs from the files in every Sharepoint Server to the database (follow my previous article on this topic for more info). But how are the logs actually stored in this database? Recently I have been involved in a project for extending this logging platform and I had the opportunity to review how this database works.
If we open the database, the first thing we will notice is the presence of various tables labeled ULSTraceLog_PartitionX where X goes from 0 to 31 (If you don't see those tables make sure the timer job is enabled on your Sharepoint Farm).
These are the tables where the ULS trace information is stored. The Diagnostic Data Provider: Trace Log timer job periodically reads the content of the logs files and for each log calls a stored procedure in the logging database to populate the ULS trace tables. This stored procedure is called prc_InsertULSTraceLog and is the responsible for creating the log in the right partition table.
If we take a look at this stored procedure we will understand how it works. The first thing we will notice is the multiple calls it does to a function called fn_GetConfigValue. This function is used to return configuration values stored in a table on the same database called Configuration and if we open this table we will notice several configuration settings:
There are similar settings for each table type and it is important to notice that this configuration is not accessible from anywhere in Central Administration (or at least I couldn't find it!). These are the settings that we can find:
The first thing the prc_InsertULSTraceLog procedure does is find the retention period. If the logs are older than this period or future logs (the log date is passed as a parameter to this stored procedure) then the procedure ends without performing any action. After this it gets the current partition, to do so it calls the function fn_GetPartitionId passing the log time. This function returns a number from 0 to 31 based on the current date and is used to determine the partition for the log date.
Before inserting the log in the appropriate partition table we see that there is a call to the [prc_LogAndRollOverPartition] stored procedure. What this procedure does is check if the partition table of the ULS Log is different than the current one and if it is, it checks if its filled with old records and if so it deletes all items. It also responsible for clearing partition tables older than the retention period. This is a very important call that not only applies to the ULS log tables. All usage tables share this same structure and behaviour and what we can see from this is that the Logging database takes care of clearing old items and doesn't grow without control.
The retention period together with the Max Bytes settings ensures that the size of this database is always under control. The retention period is used to limit the number of days of data stored over time, the maximum size is compared every time a new log is inserted and if the size of the Log tables has reached the maximum, no further logs are populated.
Finally once this comprobations have taken place, the new log is populated in the appropriate partition of the Logging database. We can then use the ULSTraceLog view that presents a consolidated view of all partition tables to query the results !
Created on 02/06/2011
