(转)SQLServer查询数据库各种历史记录

          原文地址https://www.cnblogs.com/seusoftware/p/4826958.html

 

在SQL Server数据库中，从登陆开始，然后做了什么操作，以及数据库里发生了什么，大多都是有记录可循的，但是也有一些确实无从查起。

一. 数据库启动记录

1. 最近一次启动SQL Server的时间

select sqlserver_start_time from sys.dm_os_sys_info;

--也可参考系统进程创建的时间，比服务启动时间略晚(秒级)
select login_time from sysprocesses where spid = 1 select login_time from sys.dm_exec_sessions where session_id = 1 --也可参考tempdb数据库创建的时间，比服务启动时间略晚(秒级) select create_date from sys.databases where database_id=2

 

2. 最近几次启动SQL Server的时间

--参考error log，系统默认保留6个归档，共7个文件
exec xp_readerrorlog 0,1, N'SQL Server is starting' exec xp_readerrorlog 1,1, N'SQL Server is starting' exec xp_readerrorlog 2,1, N'SQL Server is starting' exec xp_readerrorlog 3,1, N'SQL Server is starting' exec xp_readerrorlog 4,1, N'SQL Server is starting' exec xp_readerrorlog 5,1, N'SQL Server is starting' exec xp_readerrorlog 6,1, N'SQL Server is starting' --之前关键字用N'Server process ID is'并不严谨，改为N'SQL Server is starting'

 

3. 历史上更多次启动SQL Server的时间

查看windows event log，SQL语句无法直接读取event log，如果想用命令行，可以试试VBS，Powershell。

Event Viewer/Windows logs下Application 或者 System 事件里都有服务启动的记录。

 

二. 登录数据库记录

1. 查看error log

默认情况下，只有失败的登录会被记录在error log里，如果想登录失败/成功都被记录到error log，需要开启如图选项：

 

用SQL语句修改注册表，也同样可以开启，键值对应关系如下：

0, None

1, Failed

2, Successful

3, Both failed and successful

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3 GO

在error log里查看登录记录：

exec xp_readerrorlog 0,1, N'Login', N'for user', null, null, N'DESC'

 

2. 利用LOGON 触发器进行记录

从SQL Server 2005 SP2开始引入了LOGON Trigger，可以用它在登录时做个记录，实现如下：

--创建LOGON触发器
CREATE database DBA
GO USE DBA GO IF OBJECT_ID('login_history','U') is not null DROP TABLE login_history GO CREATE TABLE login_history ( FACT_ID bigint IDENTITY(1,1) primary key, LOGIN_NAME nvarchar(1024), LOGIN_TIME datetime ) GO IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger') DROP TRIGGER login_history_trigger ON ALL SERVER GO CREATE TRIGGER login_history_trigger ON ALL SERVER FOR LOGON AS BEGIN --IF SUSER_NAME() NOT LIKE 'NT AUTHORITY\%' AND -- SUSER_NAME() NOT LIKE 'NT SERVICE\%' IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY\%' AND ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE\%' BEGIN INSERT INTO DBA..login_history VALUES(ORIGINAL_LOGIN(),GETDATE()); END; END; GO --登录后查看记录 SELECT * FROM login_history