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.