SQL SERVER-ERRORLOG

標籤：

預設路徑：XX:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

每次重啟服務更新一次，預設是儲存6個，當然也可以用命令來重新整理錯誤記錄檔（註冊表也可以哦）

EXEC sp_cycle_errorlog --重建新的日誌
DBCC ERRORLOG

 

exec xp_instance_regwrite N‘HKEY_LOCAL_MACHINE‘, N‘Software/Microsoft/MSSQLServer/MSSQLServer‘,N‘NumErrorLogs‘, REG_DWORD, 20

查看日誌存檔列表

EXEC xp_enumerrorlogs

 

直接通過ssms查看當前日誌詳細_可以添加參數查詢：

EXEC SP_READERRORLOG

1. 存檔編號（0~99）

2. 日誌類型（1為SQL Server日誌，2為SQL Server Agent日誌）

3. 查詢包含的字串

4. 查詢包含的字串

5. LogDate開始時間

6. LogDate結束時間

7. 結果排序，按LogDate排序（Desc、Asc）

 

 

可以修改啟動參數路徑：重啟生效。

error日誌有時候資料庫起不來找不到原因（可以從裡面定位到錯誤的關鍵） ：

2016-05-15 09:44:32.06 Server      Microsoft SQL Server 2014 - 12.0.2269.0 (X64) --版本
    Jun 10 2015 03:35:45  --鏡像建立時間
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: )--企業版64位

2016-05-15 09:44:32.06 Server      UTC adjustment: 8:00--東八區（北京香港時間）
2016-05-15 09:44:32.06 Server      (c) Microsoft Corporation.--微軟
2016-05-15 09:44:32.06 Server      All rights reserved.--著作權
2016-05-15 09:44:32.06 Server      Server process ID is 12716.--伺服器處理序id號
2016-05-15 09:44:32.06 Server      System Manufacturer: ‘LENOVO‘, System Model: ‘3626FY4‘.--伺服器系統製造商（我的筆記本）以及型號
2016-05-15 09:44:32.06 Server      Authentication mode is MIXED.--資料庫是混合模式登陸方式
2016-05-15 09:44:32.06 Server      Logging SQL Server messages in file ‘D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG‘.---這個就是我們看到的開機記錄參數，可以修改為其他有效路徑
2016-05-15 09:44:32.06 Server      The service account is ‘DS_02\Administrator‘. This is an informational message; no user action is required.服務啟動賬戶  在執行個體屬性裡可以看的到 這個賬戶也可以修改哦
2016-05-15 09:44:32.06 Server      Registry startup parameters: 註冊表啟動參數：執行個體和註冊表可以修改
     -d D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2016-05-15 09:44:32.07 Server      Command Line Startup Parameters:執行個體啟動
     -s "MSSQLSERVER"
2016-05-15 09:44:32.70 Server      SQL Server detected 1 sockets with 2 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.—cpu 4核
2016-05-15 09:44:32.70 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.--優先順序
2016-05-15 09:44:32.70 Server      Detected 3891 MB of RAM. This is an informational message; no user action is required.--檢測記憶體
2016-05-15 09:44:32.70 Server      Using conventional memory in the memory manager.--使用記憶體
2016-05-15 09:44:32.71 Server      錯誤: 8310，嚴重性: 16，狀態: 1。--在操作中指定的屬性不出現在對象上
2016-05-15 09:44:32.71 Server      Cannot create (or open) named file mapping object ‘Global\SQL_110_MEMOBJ_24_MSSQLSERVER_0‘. SQL Server performance counters are disabled.--效能計數器不能正常開啟
2016-05-15 09:44:32.71 Server      錯誤: 3409，嚴重性: 16，狀態: 1。
2016-05-15 09:44:32.71 Server      Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.--效能計數器失敗要求重新安裝sqlctr
2016-05-15 09:44:32.72 Server      Default collation: Chinese_PRC_CI_AS (簡體中文 2052)--不區分大小寫
2016-05-15 09:44:32.76 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.--計數器報錯
2016-05-15 09:44:32.77 Server      Query Store settings initialized with enabled = 1,
2016-05-15 09:44:32.78 Server      The maximum number of dedicated administrator connections for this instance is ‘1‘
2016-05-15 09:44:32.78 Server      This instance of SQL Server last reported using a process ID of 15236 at 2016/5/15 9:44:23 (local) 2016/5/15 1:44:23 (UTC). This is an informational message only; no user action is required.
2016-05-15 09:44:32.78 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2016-05-15 09:44:32.79 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2016-05-15 09:44:32.81 spid8s      Starting up database ‘master‘.---下面開始啟動系統庫
2016-05-15 09:44:32.95 Server      CLR version v4.0.30319 loaded.
2016-05-15 09:44:33.02 spid8s      Resource governor reconfiguration succeeded.
2016-05-15 09:44:33.02 spid8s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2016-05-15 09:44:33.02 spid8s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
<{39B75A24-0837-4CEC-AFDF-B960027AE07E}>RsFxNso initialized. InstanceId = 00000003
<{50080099-5EC4-4EAF-A2A2-63C3DA97F8EB}>FsAgent is initialized
<{09C4480B-DBA4-49B7-956F-68A8B8B2445D}>FsAgent is up and running
2016-05-15 09:44:33.03 spid8s      FILESTREAM: effective level = 2 (remote access enabled), configured level = 2, file system access share name = ‘MSSQLSERVER‘.--流
2016-05-15 09:44:33.09 spid8s      SQL Trace ID 1 was started by login "sa".  ---通過sa loginame
2016-05-15 09:44:33.12 spid8s      Server name is ‘DS_02‘. This is an informational message only. No user action is required.
2016-05-15 09:44:33.17 spid14s     A self-generated certificate was successfully loaded for encryption.----加密認證通過
2016-05-15 09:44:33.17 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.---netfranmework 4.0
2016-05-15 09:44:33.18 spid14s     Server is listening on [ ‘any‘ <ipv6> 1433].—連接埠 預設號
2016-05-15 09:44:33.18 spid14s     Server is listening on [ ‘any‘ <ipv4> 1433].
2016-05-15 09:44:33.18 spid14s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].--具名執行個體
2016-05-15 09:44:33.18 spid14s     Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2016-05-15 09:44:33.18 Server      Server is listening on [ ::1 <ipv6> 1434].
2016-05-15 09:44:33.19 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2016-05-15 09:44:33.19 Server      Dedicated admin connection support was established for listening locally on port 1434.
2016-05-15 09:44:33.19 spid14s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2016-05-15 09:44:33.19 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2016-05-15 09:44:33.20 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) ----域控[ MSSQLSvc/DS_02.vcredit.com ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2016-05-15 09:44:33.20 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/DS_02.vcredit.com:1433 ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2016-05-15 09:44:33.54 Logon       錯誤: 18456，嚴重性: 14，狀態: 38。--我把報表伺服器分離了 所以報錯
2016-05-15 09:44:33.54 Logon       Login failed for user ‘NT SERVICE\ReportServer‘. 原因: 無法開啟明確指定的資料庫“ReportServer”。 [用戶端: 2.0.1.2]
2016-05-15 09:44:34.56 spid15s     A new instance of the full-text filter daemon host process has been successfully started.
2016-05-15 09:44:34.66 spid17s     Starting up database ‘msdb‘.---------------開始並行啟動系統庫和使用者庫
2016-05-15 09:44:34.66 spid18s     Starting up database ‘ReportServer‘.
2016-05-15 09:44:34.66 spid20s     Starting up database ‘ReportServerTempDB‘.
2016-05-15 09:44:34.66 spid11s     Starting up database ‘mssqlsystemresource‘.
2016-05-15 09:44:34.67 spid21s     Starting up database ‘Sys‘.
2016-05-15 09:44:34.67 spid22s     Starting up database ‘TEST‘.
2016-05-15 09:44:34.68 spid11s     The resource database build version is 12.00.2000. This is an informational message only. No user action is required.
2016-05-15 09:44:34.73 spid11s     Starting up database ‘model‘.
2016-05-15 09:44:34.78 spid11s     Clearing tempdb database.--臨時庫 清理了哦
2016-05-15 09:44:34.90 spid11s     Starting up database ‘tempdb‘.
2016-05-15 09:44:34.92 Server      Software Usage Metrics is disabled.
2016-05-15 09:44:35.03 spid27s     The Service Broker endpoint is in disabled or stopped state.
2016-05-15 09:44:35.03 spid27s     The Database Mirroring endpoint is in disabled or stopped state.
2016-05-15 09:44:35.05 spid27s     Service Broker manager has started.
2016-05-15 09:44:35.05 spid8s      Recovery is complete. This is an informational message only. No user action is required.
2016-05-15 09:44:35.11 spid51      Configuration option ‘Agent XPs‘ changed from 0 to 1. Run the RECONFIGURE statement to install.--代理
2016-05-15 09:44:35.25 spid51      Attempting to load library ‘xpsqlbot.dll‘ into memory. This is an informational message only. No user action is required.--寫進記憶體
2016-05-15 09:44:35.25 spid51      Using ‘xpsqlbot.dll‘ version ‘2014.120.2000‘ to execute extended stored procedure ‘xp_qv‘. This is an informational message only; no user action is required.
2016-05-15 09:44:35.28 spid51      Attempting to load library ‘xpstar.dll‘ into memory. This is an informational message only. No user action is required.
2016-05-15 09:44:35.30 spid51      Using ‘xpstar.dll‘ version ‘2014.120.2000‘ to execute extended stored procedure ‘xp_sqlagent_notify‘. This is an informational message only; no user action is required.

SQL SERVER-ERRORLOG