在 SharePoint Server 中檢視記錄資料庫的資料View data in the logging database in SharePoint Server

摘要: 了解 SharePoint 記錄資料庫如何檢視監視資訊、使用自訂 SQL 檢視,以及匯出至 Excel。Summary: Learn about the SharePoint logging database, how to view monitoring information, use custom SQL views, and export into Excel.

注意

因為 SharePoint Server 2016 在 Internet Information Services (IIS) 中以網站形式運作,所以管理員與使用者均依賴瀏覽器提供的協助工具功能。SharePoint Server 2016 支援所支援瀏覽器的協助工具功能。如需詳細資訊,請參閱下列資源: > 規劃瀏覽器支援> SharePoint 產品中的協助工具功能> 快速鍵> 觸控Because SharePoint Server 2016 runs as websites in Internet Information Services (IIS), administrators and users depend on the accessibility features that browsers provide. SharePoint Server 2016 supports the accessibility features of supported browsers. For more information, see the following resources: > Plan browser support> Accessibility features in SharePoint Products> Keyboard shortcuts> Touch

SharePoint 記錄資料庫簡介Introduction to the SharePoint logging database

若要監視執行 SharePoint Server 2016 的伺服器以及在伺服器上執行的服務,您可以直接存取各種記錄檔,例如 Windows Server事件記錄檔、SharePoint 統一登入服務 (ULS) 記錄檔 (又稱為追蹤記錄檔) 或使用狀況資料記錄檔。您也可以移至 SharePoint Server 2016SharePoint 管理中心網站以檢閱各種報表。SharePoint Health Analyzer 報表包含伺服器或服務的規則;管理 (診斷) 報表包含搜尋相關資訊;Web Analytics 報表則包含 Web Analytics 評量。如需報表的詳細資訊,請參閱在 SharePoint Server 2016 中檢視報表與記錄檔To monitor servers that are running SharePoint Server 2016 and the services that run on the servers, you can directly access various logs such as the Windows Server event logs, SharePoint Unified Logging Service (ULS) logs (also known as trace logs), or usage data logs. You can also go to SharePoint Server 2016the SharePoint Central Administration website to review various reports. SharePoint Health Analyzer reports contain 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 View reports and logs in SharePoint Server 2016.

所有之前提到的監視方法都有限制。例如,Windows Server事件記錄檔、SharePoint ULS 記錄檔以及使用狀況資料記錄檔不會儲存在單一位置。您必須移至不同的位置來尋找相關的記錄檔。同樣地,雖然管理中心的報表包含最常用的評量和監視資訊,但是您無法將更多的監視資訊新增至這些報表,因為這些報表是預先定義的且無法變更。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 frequently 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 changed.

您可以使用 SharePoint Server 2016 中的記錄資料庫來提高監視效率。記錄資料庫是涵蓋整個伺服器陣列的存放庫,儲存了伺服器陣列中每一部伺服器的 SharePoint Server 2016 監視資訊。記錄資料庫提供的選項,可讓您可在單一位置檢視和自訂各種監視資訊。再者,記錄資料庫是唯一可以直接修改資料庫來自訂報表的 SharePoint Server 2016 資料庫。You can increase monitoring efficiency by using the logging database in SharePoint Server 2016. The logging database is a farm-wide repository of SharePoint Server 2016 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 Server 2016 database for which you can customize reports by directly changing the database.

注意

如需 SharePoint Server 2016 資料庫的詳細資訊,請參閱SharePoint Server 中的資料庫類型和描述For more information about SharePoint Server 2016 databases, see Database types and descriptions in SharePoint Server.

注意

資料庫的預設名稱是 SharePoint_Logging 。您可以使用 Microsoft PowerShell 來變更預設名稱和 SharePoint 資料庫伺服器位置。如需記錄資料庫名稱與資料庫伺服器位置的詳細資訊,請參閱 在 SharePoint Server 中設定 Usage and Health Data CollectionThe default name of the database is SharePoint_Logging. You can change the default name and the SharePoint database server location by using Microsoft PowerShell. For more information about the logging database name and database server location, see Configure usage and health data collection in SharePoint Server.

在單一位置儲存各種監視資訊可協助您有效率地監視 SharePoint Server 2016 伺服器與服務。更重要的是,您可以根據記錄資料庫內部的資料表來建立自己的報表,以符合獨特的監視、報表以及疑難排解需求。本文說明如何使用現有的資料表與檢視來建立報表。您可能會希望撰寫自己的提供者以建立新的資料表。A single place that stores various monitoring information helps you efficiently monitor SharePoint Server 2016 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 by using existing tables and views. You might want to write your own providers to create new tables.

注意

SQL 檢視為虛擬資料表。資料表與 SQL 檢視之間的差異在於您可以修改資料表內部的資料,但是您無法修改 SQL 檢視內部的資料。A SQL view is a virtual table. One difference between a table and a SQL view is that you can modify the data inside a table but you cannot modify the data inside a SQL view.

您可以使用 SharePoint Server 2016管理中心來設定匯入記錄資料庫中的記錄。如需如何設定記錄類別、記錄層級以及追蹤 (ULS) 記錄路徑的詳細資訊,請參閱在 SharePoint Server 中設定診斷記錄。如需如何啟用寫入記錄資料庫之內容的詳細資訊,請參閱在 SharePoint Server 中設定 Usage and Health Data Collection。如需如何指定記錄資料庫伺服器、名稱和資料庫驗證資訊、要寫入記錄資料庫的事件,以及記錄寫入記錄資料庫的頻率 (亦即與使用狀況資料庫匯入相關的計時器工作) 的詳細資訊,請參閱在 SharePoint Server 2016 中設定 SharePoint Health Analyzer 計時器工作You use SharePoint Server 2016Central 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 in SharePoint Server. For more information about how to enable what is written into the logging database, see Configure usage and health data collection in SharePoint Server. 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 in SharePoint Server 2016.

本文中的程序使用 SQL Server Management Studio 來存取記錄資料庫。Management Studio 是 SQL Server 2008 的元件。The procedures in this article use SQL Server Management Studio to access the logging database. Management Studio is a component of SQL Server 2008.

注意

若要存取記錄資料庫,您必須擁有 Windows 驗證 (建議) 或 SQL 驗證。資料庫驗證資訊是設定於 SharePoint Server 2016管理中心。如需驗證方法的詳細資訊,請參閱在 SharePoint Server 中設定 Usage and Health Data CollectionTo access the logging database, you have to have either Windows authentication (recommended) or SQL authentication. The database authentication information is configured in SharePoint Server 2016Central Administration. For more information about authentication methods, see Configure usage and health data collection in SharePoint Server.

SharePoint 記錄資料庫中預先定義的 SQL 檢視Pre-defined SQL views in the SharePoint logging database

本節說明 SharePoint Server 2016 記錄資料庫內部的磁碟分割表格與 SQL 檢視,並包括下列三個程序:This section describes partition tables and SQL views inside the SharePoint Server 2016 logging database, and includes the following three procedures:

  • 使用 SQL Server Management Studio 存取記錄資料庫To access the logging database by using SQL Server Management Studio

  • 從預設檢視來檢視記錄資訊To view the logging information from default views

  • 使用 Excel 匯出和檢視記錄資料To export to and view the logging data by using Excel

記錄資料庫會針對每個類別的每日資料使用不同的磁碟分割表格。例如,第一天的計時器工作使用狀況資料會寫入 dbo.TimerJobUsage_Partition0 資料表,而 32 天後的資料則會寫入 dbo.TimerJobUsageUsage_Partition31 資料表。一天之內的記錄會寫入一個磁碟分割表格。這表示就每個記錄類別而言,每個磁碟分割表格會儲存特定一天的記錄。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.

注意

分割區資料表與特定日期之間的對應,視記錄資料庫保留期間與將記錄寫入記錄資料庫的開始日期而定。您可以觀察每個資料表內的時間資訊來取得對應。例如,如果保留期間為 14 天,而今日的記錄是寫入分割區資料表 2,則明天的記錄將寫入分割區資料表 3,依此類推。14 天之後,將刪除所有記錄,並從分割區資料表 0 開始將新記錄寫入這些分割區資料表。The mapping between one partition table and the exact date depends on the logging database retention period and the starting date to write logs into the logging database. You can get the mapping by observing time information inside each table. For example, if the retention period is 14 days and today's logs are written to partition table 2, tomorrow's logs will be written into partition table 3 and so on. After 14 days, all logs are deleted and new logs are written to these partition tables starting from partition table 0.

您可以使用 Management Studio 中預先定義的 SQL 檢視,在單一位置檢視所有的監視資訊。每個預先定義的檢視會從特定記錄類別的所有 32 個磁碟分割表格中收集資料。例如,您可以檢視從 dbo.TimerJobUsage_Partition0dbo.TimerJobUsageUsage_Partition31 的 32 個資料表中的監視資訊。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.

若要存取預先定義的檢視,您必須存取 SharePoint Server 2016 記錄資料庫,然後從記錄資料庫檢視監視資訊。To access the pre-defined views, you must access the SharePoint Server 2016 logging database. Then from the logging database, you view the monitoring information.

使用 Management Studio來存取記錄資料庫To access the logging database by using Management Studio

  1. 確認執行此程序的使用者帳戶具備 db_owner 固定資料庫角色。Verify that the user account that is performing this procedure has the db_owner fixed database role.

  2. 在工作列上,按一下 [開始]*,指向 [所有程式],按一下 [Microsoft SQL Server 2008]*** 或是已安裝的最新 Microsoft SQL Server 版本,然後按一下 [SQL Server Management Studio]*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.

    注意

    如果您的伺服器上沒有 Management Studio,請重新安裝 SQL Server 2008,然後新增 Management Studio 元件。如需詳細資訊,請參閱<安裝 SQL Server>。If you do not have Management Studio on the server, reinstall SQL Server 2008 and add the Management Studio component. For more information, see SQL Server Install.

  3. 在 [連接至伺服器]**** 對話方塊中,選擇 [資料庫引擎]**** 。接著,指定伺服器名稱,例如,「ServerName\SharePoint」。選取您透過 SharePoint Server 2016 管理中心設定的驗證類型 ([Windows 驗證]**** 或 [SQL Server 驗證]*)。如果是 [SQL Server 驗證],請指定資料庫管理員的認證。設定資訊之後,請按一下 [連接]***。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 thisProduct_2nd_CurrentVer Central Administration. If it is SQL Server Authentication, specify the credentials for the database administrator. After the information is set, click Connect.

  4. 按一下 [檢視]*,然後按一下 [物件總管],以切換到 [物件總管]*** 檢視。展開 [資料庫]*,即可查看具有預設名稱 *SharePoint_Logging 或是具有在管理中心設定之名稱的記錄資料庫。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 SharePoint_Logging or a name that you configured in CentralAdmin_2nd.

  5. 或者,您也可以展開記錄資料庫來查看資料表和檢視。Optionally, expand the logging database to see tables and views.

    從預設檢視來檢視記錄資訊To view the logging information from default views

  6. 確認執行此程序的使用者帳戶具備 db_owner 固定資料庫角色。Verify that the user account that is performing this procedure has the db_owner fixed database role.

  7. 在 Management Studio 中,使用上述程序移至記錄資料庫節點。In Management Studio, go to the logging database node by using the previous procedure.

  8. 展開資料庫的 [檢視]**** 節點以查看預設檢視。以滑鼠右鍵按一下檢視 (例如 dbo.RequestUsage),然後選擇 [選取前 1000 個資料列]*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.

    [選取前 1000 個資料列]**** 操作為下列 T-SQL 查詢指令碼: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 [SharePoint_Logging].[dbo].[RequestUsage]
    

    結果視窗就會顯示資料表類別 [要求使用狀況]**** 的前 1000 個資料列。The top 1000 rows of the table category Request Usage appear in the result window.

  9. 您可以在 SQL 編輯器視窗中修改 T-SQL 查詢。例如,如果資料表中有超過 1000 個資料列,您可能想要檢視前 5000 個資料列。若要這樣做,您可以將 "SELECT TOP 1000" 取代為 "SELECT TOP 5000" 來變更指令碼,然後按一下 [執行]*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.

如果您想要使用 Management Studio 以外的工具來檢視記錄,您可以擷取檢視中的監視資訊,然後另存為文字檔案或 CSV 檔案。下列程序將使用 Excel 做為範例。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, Excel is used as an example.

使用 Excel 匯出和檢視記錄資料To export and view the logging data by using Excel

  1. 確認執行此程序的使用者帳戶具備 db_owner 固定資料庫角色。Verify that the user account that is performing this procedure has the db_owner fixed database role.

  2. 在 Management Studio 中,移至記錄資料庫節點。In Management Studio, go to the logging database node.

  3. 展開 [檢視]**** 節點,在您要從中擷取資料的檢視上按一下滑鼠右鍵,然後按一下 [選取前 1000 個資料列]*Expand the *Views node, right-click the view from which you want to extract data, and then click Select Top 1000 Rows.

  4. 在結果視窗中按一下滑鼠右鍵,然後按一下 [全選]*。接著按一下滑鼠右鍵,然後按一下 [儲存結果]In the result window, right-click, and then click **Select All. Then right-click and then click **Save Results As…*.

  5. 在 [儲存方格結果]**** 視窗中,指定您要儲存檔案的資料夾,將 [存檔類型]**** 指定為 CSV (逗號分隔),然後指定適當的檔案名稱。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.

  6. 在 Excel 中,按兩下 CSV 檔案加以開啟。Open the CSV file by double-clicking it in Excel.

SharePoint 記錄資料庫中的自訂 SQL 檢視Custom SQL views in the SharePoint logging database

在 SharePoint Server 2016 中的記錄資料庫可讓您以兩種方式建立自訂報表。您可以結合現有資料表中的相關資訊來產生新檢視,或者您可以在記錄資料庫內部撰寫提供者以產生新的磁碟分割表格。本節中的範例僅會顯示第一種方式。The logging database in SharePoint Server 2016 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.

在使用狀況資料表和 ULSTraceLog 資料表中, CorrelationId 是疑難排解的重要參數。這是因為每個錯誤訊息都包含唯一的 CorrelationIdCorrelationId 是一個 GUID,可用來連結有關某要求的所有相關資訊。下列程序顯示如何使用 CorrelationId 來建立連結多個記錄類別的自訂檢視。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.

建立使用現有資料表的自訂 SQL 檢視To create a custom SQL view that uses existing tables

  1. 確認執行此程序的使用者帳戶具備 db_owner 固定資料庫角色。Verify that the user account that is performing this procedure has the db_owner fixed database role.

  2. 在 Management Studio 中,移至記錄資料庫節點。In Management Studio, go to the logging database node.

  3. 在記錄資料庫中,展開 [檢視]**** 節點。選擇您要收集資訊的其中一個檢視。以滑鼠右鍵按一下該檢視,然後按一下 [新增檢視]*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.

  4. 在 [加入資料表]**** 視窗中,選擇要加入的資料表。例如,如果您想要取得某天功能使用狀況網站要求的資訊,可以加入 dbo.FeatureUsage_Partion1dbo.RequestUsage_Partion1In the Add Table window, choose the tables to add. For example, if you want to obtain information about feature usage site requests for a single day, you can add dbo.FeatureUsage_Partion1 and dbo.RequestUsage_Partion1.

    T-SQL 查詢會自動在這兩個資料表中內部聯結唯一索引鍵 PartionIdThe 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
    
  5. 以滑鼠右鍵按一下兩個資料表之間的連結,然後選取 [移除]**** 以中斷兩個資料表的聯結。Disjoin the two tables by right-clicking the link between these tables and selecting removing.

  6. 若要使用 CorrelationId 內部聯結這兩個資料表,請按一下其中一個資料表中的 CorrelationId 資料行,並將指標移至另一個資料表中的 CorrelationId 資料行。也可以直接從 [查詢編輯器] 修改 SQL 查詢。To inner join the two tables using CorrelationId, click the CorrelationId column in one table and move the pointer 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 
    
  7. 從每個資料表中選擇要在新的檢視中顯示的資料欄。Choose the columns in each table that you want to show in the new view.

  8. 以滑鼠右鍵按一下並選擇 [執行 SQL]*。結果會顯示在結果視窗中。Right-click and choose *Execute SQL. The results appear in the result window.

另請參閱See also

概念Concepts

在 SharePoint Server 2016 中檢視報表與記錄檔View reports and logs in SharePoint Server 2016

SharePoint Server 2016 監視概觀Overview of monitoring in SharePoint Server