Understanding the Logging Database (SharePoint Foundation 2010)

Applies to: SharePoint Foundation 2010

This article introduces the Microsoft SharePoint 2010 Products logging database and describes how to view monitoring information inside SharePoint Foundation 2010 logging database (by default, named WSS_Logging). This article also describes how to use custom views in the logging database, and how to export monitoring information into Microsoft Excel.

In this article:

• Introduction to the logging database

• Pre-defined SQL views in the logging database

• Custom SQL views in the logging database

Introduction to the logging database

To monitor SharePoint Foundation 2010 servers and the services that run on the servers, you can directly access various logs such as the Windows Server event logs, SharePoint Universal Logging System (ULS) logs (also known as trace logs), or usage data logs. You can also go to SharePoint Foundation 2010 Central Administration to review various reports. Health analyzer reports contain SharePoint Foundation 2010 rules for servers or services. Administration (diagnostics) reports contain search-related information. Web Analytics reports contain web analytics metrics. For more information about reports, see Viewing reports and logs (SharePoint Foundation 2010).

All the monitoring methods mentioned previously have limits. For example, Windows Server event logs, SharePoint ULS logs, and usage data logs are not stored in one single place. You have to go to different places to find related logs. Similarly, although reports from Central Administration contain the most commonly used metrics and monitoring information, if you want to add more monitoring information to those reports, you cannot do it because these reports are pre-defined and cannot be modified.

You can increase monitoring efficiency by using the logging database in SharePoint Foundation 2010. The logging database is a farm-wide repository of SharePoint Foundation 2010 monitoring information from every server in the farm. The logging database provides the option to view and customize various monitoring information in one place. Moreover, the logging database is the only SharePoint Foundation 2010 database for which you can customize reports by directly modifying the database.

A single place that stores various monitoring information helps you efficiently monitor SharePoint Foundation 2010 servers and services. More importantly, you can create your own reports for unique monitoring, reporting and troubleshooting requirement based on tables inside the logging database. This article describes how to create reports using existing tables and views. You might want to write your own providers to create new tables. For more information about writing new providers to create new tables and views inside the logging database, see SharePoint 2010 (https://go.microsoft.com/fwlink/p/?LinkId=224502).

You use SharePoint Foundation 2010 Central Administration to configure logs imported into the logging database. For more information about how to configure log categories, log levels, and trace (ULS) log path, see Configure diagnostic logging (SharePoint Foundation 2010). For more information about how to enable what is written into the logging database, see Configure usage and health data collection (SharePoint Foundation 2010). For more information about how to specify the logging database server, name and database authentication information, the events to be written to the logging database, and the frequency that logs are written to the logging database (that is, timer jobs related to usage database importing), see Configure SharePoint Health Analyzer timer jobs (SharePoint Foundation 2010).

The procedures in this article use SQL Server Management Studio to access the logging database. Management Studio is a component of Microsoft SQL Server 2008.

Pre-defined SQL views in the logging database

This section describes partition tables and SQL views inside the SharePoint Foundation 2010 logging database, and includes the following three procedures:

• To access the logging database by using SQL Server Management Studio

• To view the logging information from default views

• To export to and view the logging data using Excel

The logging database uses a separate partition table for the daily data for each category. For example, the timer job usage data for the first day is written to the dbo.TimerJobUsage_Partition0 table and the data for 32 days later is written to the dbo.TimerJobUsageUsage_Partition31 table. Logs within one day are written to one partition table. That means, for each log category, each partition table stores one particular day’s logs.

You can use the pre-defined SQL views in Management Studio to view all monitoring information in one place. Each pre-defined view collects the data from all 32 partition tables for the specific log category. For example, you can view the monitoring information in 32 tables from dbo.TimerJobUsage_Partition0 to dbo.TimerJobUsageUsage_Partition31.

To access the pre-defined views, you must access the SharePoint Foundation 2010 logging database. Then from the logging database, you view the monitoring information.

To access the logging database by using Management Studio

1. On the taskbar, click Start, point to All Programs, click Microsoft SQL Server 2008 or the latest Microsoft SQL Server version that is installed, and then click SQL Server Management Studio.

   Note

   If you do not have Management Studio on your server, reinstall SQL Server 2008 and add the Management Studio component. For more information, see SQL Server Install.

2. In the Connect to Server dialog box, choose Database Engine. Then specify the server name, for example, ServerName\SharePoint. Select the authentication type (Windows Authentication or SQL Server Authentication) that you configured through SharePoint Foundation 2010 Central Administration. If it is SQL Server Authentication, specify the credentials for the database administrator. After the information is set, click Connect.

3. Switch to the Object Explorer view by clicking View, and then clicking Object Explorer. Expand Databases to see the logging database that has default name WSS_Logging or a name that you configured from SharePoint Foundation 2010 Central Administration.

4. Optionally, expand the logging database to see tables and views.

To view the logging information from default views

1. In Management Studio, go to the logging database node by using the previous procedure.

2. Expand the Views node of the database to see the default views. Right-click the view, for example dbo.RequestUsage, and choose Select Top 1000 Rows.

   The operation Select Top 1000 Rows is the following T-SQL query script:

   /****** Script for SelectTopNRows command from SSMS  ******/
   SELECT TOP 1000 [PartitionId]
         ,[RowId]
         ,[LogTime]
         ,[MachineName]
         ,[FarmId]
         ,[SiteSubscriptionId]
         ,[UserLogin]
         ,[CorrelationId]
         ,[WebApplicationId]
         ,[ServerUrl]
         ,[SiteId]
         ,[SiteUrl]
         ,[WebId]
         ,[WebUrl]
         ,[DocumentPath]
         ,[ContentTypeId]
         ,[QueryString]
         ,[BytesConsumed]
         ,[HttpStatus]
         ,[SessionId]
         ,[ReferrerUrl]
         ,[ReferrerQueryString]
         ,[Browser]
         ,[UserAgent]
         ,[UserAddress]
         ,[RequestCount]
         ,[QueryCount]
         ,[QueryDurationSum]
         ,[ServiceCallCount]
         ,[ServiceCallDurationSum]
         ,[OperationCount]
         ,[Duration]
         ,[RequestType]
         ,[Title]
         ,[RowCreatedTime]
     FROM [WSS_Logging].[dbo].[RequestUsage]
   

   The top 1000 rows of the table category Request Usage appear in the result window.

3. You can modify the T-SQL query in the SQL editor window. For example, if there are more than 1000 rows in the tables, you might want to view the top 5000 rows. To do that, change the script by replacing “SELECT TOP 1000” with ”SELECT TOP 5000”, and then click Execute.

If you want to view logs by using tools other than Management Studio, you can extract the monitoring information from the views and save as a text file or a CSV file. In the following procedure, Microsoft Excel is used as an example.

To export and view the logging data by using Excel

1. In Management Studio, go to the logging database node.

2. Expand the Views node, right-click the view from which you want to extract data, and then click Select Top 1000 Rows.

3. In the result window, right-click, and then click Select All. Then right-click and then click Save Results As….

4. In the Save Grid Results window, specify the folder in which you want to save the file, specify the Save as type as CSV(Comma delimited), and then specify an appropriate file name.

5. Open the CSV file by double-clicking it in Excel.

Custom SQL views in the logging database

The logging database in SharePoint Foundation 2010 enables you to create custom reports in two ways. You can generate new views by combining related information from existing tables, or you can write providers to generate new partition tables inside the logging database. The examples in this section only show the first way.

In usage tables and the ULSTraceLog tables, the CorrelationId is an important parameter for troubleshooting. This is because every error message contains a unique CorrelationId. CorrelationId is a GUID that links all the related information with respect to a request. The following procedure shows how to make a custom view that links multiple log categories by using the CorrelationId.

To create a custom SQL view that uses existing tables

1. In Management Studio, go to the logging database node.

2. In the logging database, expand the Views node. Choose one of the views for which you want to collect information. Right-click the view, and then click New View….

3. In the Add Table window, choose the tables to add. For example, if you want to get information about feature usage site requests for a single day, you can add dbo.FeatureUsage_Partion1 and dbo.RequestUsage_Partion1.

   The T-SQL query automatically inner joins the unique key PartionId in these two tables.

   SELECT     
   FROM   dbo.FeatureUsage_Partition1 INNER JOIN
       dbo.RequestUsage_Partition1 ON dbo.FeatureUsage_Partition1.PartitionId = dbo.RequestUsage_Partition1.PartitionId
   

4. Disjoin the two tables by right-clicking the link between these tables and selecting removing.

5. To inner join the two tables using CorrelationId, click the CorrelationId column in one table and move the cursor to the CorrelationId column in another table. Or you can modify the SQL query directly from the Query Editor.

   SELECT  
   FROM   dbo.FeatureUsage_Partition1 INNER JOIN
       dbo.RequestUsage_Partition1 ON dbo.FeatureUsage_Partition1.CorrelationId = dbo.RequestUsage_Partition1.CorrelationId 
   

6. Choose the columns in each table that you want to show in the new view.

7. Right-click and choose Execute SQL. The results appear in the result window.