查询数据库各种历史记录,Replication的犄角旮旯

前言:这是昨天刚刚发生的案例,尽管事件的起因只是一个简单的DDL操作,但影响面和影响时间可以说是大大超出了预期;我们将在描述本案例的前因后果之后,聊聊如何近似估算DDL的操作进度,以及关于logreader延迟的问题;

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

由于直接找MS开了case,直接引用标准回复格式;

一. 数据库启动记录

 

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'Server process ID is'exec xp_readerrorlog 1,1, N'Server process ID is'exec xp_readerrorlog 2,1, N'Server process ID is'exec xp_readerrorlog 3,1, N'Server process ID is'exec xp_readerrorlog 4,1, N'Server process ID is'exec xp_readerrorlog 5,1, N'Server process ID is'exec xp_readerrorlog 6,1, N'Server process ID is'

对于一张11亿的数据进行PK字段的int到bigint的类型转换,一直没有完成。发现replication延迟仅1小时

 

 

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

问题排查

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

=========

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

1.sp_replcounters发现replbeginlsn的值一直没有改变,但是replnextlsn一直在变化

 

2.sp_replcounters返回未发送的transaction持续上升

二. 登陆数据库记录

 

1. 查看error log

发生原因

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

=========

 官方澳门新永利下载 1

  1. 执行ALTER
    TABLE修改PK字段从INT到bigint时,由于一直没有完成,这被视为是一个active
    transaction,这个值代表当前LOG的minLSN,
    由于这个transaction一直没有做完,所以这个值一直没有变化

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

Replbeginlsn

binary(10)

Log sequence number (LSN) of the current truncation point in the log.

0, None

1, Failed

 

2, Successful

  1. 但是根据我们对于log
    reader的理解,这个beginLSN即使一直没有变化,也不会影响log
    reader对于日志的读取,因为log reader会直接从replnextlsn开始扫描

3, Both failed and successful

 

USE [master]GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'AuditLevel', REG_DWORD, 3GO
  1. 由于active
    transaction一直没有提交,导致日志无法被截断,日志持续自增,目前已经有270GB,
    4000个VLF

在error log里查看登录记录:

 

exec xp_readerrorlog 0,1, N'Login', N'for user', null, null, N'DESC'
  1. VLF太多通常是会导致log
    reader读取日志较慢,但是由于目前4000个VLF中只有2500个处于status=2的活动状态,并不是很多,这也不是导致replication延迟的原因

 

 

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

5.select *from
fn_dblog(null,null)发现有大量的LOP_MODIFY_COLUMN的日志记录
(处理在LCX_HEAP上),这个应该针对于每一条记录做类型转换时都需要记录的日志.而这个记录还在不断增多.由于这部分日志会有超过11亿条,并且replication不需要发送这些日志(因为这张表已经从article中移除).但是这部分日志还是需要被log
reader扫描一遍,然后跳过去,这样的扫描造成了log
reader读取日志变慢,从而导致replication的延迟.

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

 

--创建LOGON触发器CREATE database DBAGOUSE DBAGOIF OBJECT_ID('login_history','U') is not null  DROP TABLE login_historyGOCREATE TABLE login_history(FACT_ID     bigint IDENTITY(1,1) primary key,LOGIN_NAME   nvarchar(1024),LOGIN_TIME   datetime)GOIF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')  DROP TRIGGER login_history_trigger ON ALL SERVERGOCREATE TRIGGER login_history_triggerON ALL SERVERFOR LOGONASBEGIN  --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

解决方案

 

========

三. 创建,修改,删除记录 (DDL)

1.持续等待到ALTER TABLE做完,这样log
reader跳完了所有的日志以后,replication的延迟会自动追上去

1. 服务器对象的创建,修改

2.手动cancel这个alter table,让他回滚,这样就不会产生新的日志,log
reader不需要再扫描那些日志,也会慢慢追上延迟

--创建数据库select name, create_date from sys.databases--创建,修改登录select name, createdate, updatedate from sysloginsselect name, create_date, modify_date from sys.server_principals --创建,修改LOGON触发器select name, create_date, modify_date from sys.server_triggers 

 

 

最后您通过cancel这个alter table的语句,这个问题得以缓解.

2. 数据库对象创建,修改

 

--创建数据库对象select name, create_date, modify_date from sys.objects --创建,修改触发器,DDL触发器不在sys.objects里select name, create_date, modify_date from sys.triggers 

下一步方案

注意:

========

(1) 索引的创建,修改并没有记录

根据我们以前case的历史背景,和今天的电话沟通,我建议您对于这张表的字段修改还是使用导到新表,然后重命名的方式.因为这样的办法使用的是select
into,属于BULK操作,在SIMPLE模式下是不记日志的,所以不会对replication有影响.

sys.objects --里面没有0,1 之外的索引sys.indexes --里面没有日期objectproperty() --没有日期属性indexproperty() --没有日期属性sys.dm_db_index_operational_stats sys.dm_db_index_usage_statssys.dm_db_index_physical_stats --也都没有STATS_DATE (table_id, index_id) --是索引的统计信息最后更新时间

 

(2) 关于creator和owner

=====================华丽丽的分割线========================

SQL Server里只有owner,数据库里对象的owner必须是一个有效的database
principal
(user或者role),没有creator,很难知道是谁创建了这个对象,因为owner并不准确:

 

首先,数据库对象的owner可以被修改,ALTER
AUTHORIZATION或者sp_changeobjectowner都行;

案例补充说明:

其次,就算owner没被修改过,默认情况下数据库对象的owner沿用schema的owner,除非在创建schema时特意指定了某个owner;

由于alter
table操作并不能直接获取操作的进度(sys.dm_exec_requests中的percent_complete对alter
table操作不计算执行进度),经过MS工程师的指点,我们依然可以间接的估算出操作进度;以下通过一个测试案例说明

最后,系统表并没有记录creator,如果想要查询,也许得利用DDL
触发器来记录。

1、创建一个数据表,填充数据;

关于owner简单举例如下:

test_1表,id列为主键自增列,类型bigint;填充数据51W条,数据大小2G左右;

--用sysadmin权限的账号登录后创建USE masterGOCREATE LOGIN test_login WITH PASSWORD=N'123', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFALTER SERVER ROLE sysadmin ADD MEMBER test_loginGOCREATE database DBAGOUSE DBAGOCREATE USER test_user FOR LOGIN test_loginGOCREATE SCHEMA test_schemaGO--用"test_login"登录后建表if OBJECT_ID('test_schema.test_owner','U') is not null  drop table test_schema.test_ownerGOcreate table test_schema.test_owner(id int)GO--表的owner还是用了schema的ownerselect s.name as schema_name, dp2.name as schema_owner,    o.name as object_name, coalesce(dp1.name, dp2.name) as object_owner,* from sys.objects oinner join sys.schemas son o.schema_id = s.schema_idleft join sys.database_principals dp1on o.principal_id = dp1.principal_idleft join sys.database_principals dp2on s.principal_id = dp2.principal_idwhere o.name = 'test_owner'--用objectproperty也可以查看ownerselect name as object_ownerfrom sys.database_principals where principal_id = OBJECTPROPERTY(object_id('test_schema.test_owner'),'OwnerId')

2、修改id类型(int改为bigint),由于id是主键,所以需要先删除主键约束才能继续alter
table。删除主键约束后,手动checkpoint一下,清理一下fn_dblog;

 

官方澳门新永利下载 2

3. 默认跟踪里的创建,修改,删除对象 (create, alter, drop)

3、执行alter table语句并检查fn_dblog

从sql server 2005开始引入了默认跟踪,这是sql
server默认开启的跟踪,并定义了事件、文件大小,个数,查看定义如下:

官方澳门新永利下载 3官方澳门新永利下载 4

--默认定义好的默认跟踪事件select t.eventid, te.namefrom (select distinct eventid from sys.fn_trace_geteventinfo(1)) tinner join sys.trace_events teon t.eventid = te.trace_event_id--最多5个文件,每个文件20MB,依次滚动覆盖select * from sys.traceswhere id = 1示例,利用默认跟踪查看删除数据库记录如下:DECLARE @path varchar(1024)SELECT @path = pathFROM sys.traceswhere id = 1SELECT *FROM fn_trace_gettable(@path, 1)where DatabaseName = 'DBA'and EventClass = 47  --46表示Create对象,47表示Drop对象,164表示修改对象and ObjectType = 16964 --16964表示数据库
SELECT [Current LSN],Operation,Context,[Transaction ID],[Log Record Fixed Length],[Log Record Length],AllocUnitIdFROM fn_dblog(NULL,NULL) fnlogWHERE Operation='LOP_MODIFY_ROW' AND Context='LCX_HEAP' AND [Transaction ID]='0000:00ed4660'

注意:

View Code

(1) 其他对象比如表的删除等也都可以查到;

官方澳门新永利下载 5官方澳门新永利下载 6

(2)
跟踪返回的列值有很多定义,没有系统表记载,需要去翻帮助,比如ObjectType列值参考这个列表:

--查询fn_dblog中每个VLF包含的记录数SELECT LEFT([Current LSN],CHARINDEX(':',[Current LSN])-1),COUNT(1)FROM fn_dblog(NULL,NULL) fnlogWHERE Operation='LOP_MODIFY_ROW' AND Context='LCX_HEAP' AND [Transaction ID]='0000:00ed4660'GROUP BY LEFT([Current LSN],CHARINDEX(':',[Current LSN])-1)

View Code

(3)
注意默认跟踪的时效性,5个20MB的文件,也许想要看的信息很快就被覆盖了;

官方澳门新永利下载 7

(4)  truncate table并没有被默认跟踪记录。

小结:根据fn_dblog中某一段的日志情况(通过Operation=’LOP_MODIFY_ROW’
AND Context=’LCX_HEAP’ AND [Transaction
ID]=’0000:00ed4660’确认正在执行的DDL操作,其中[Transaction
ID]和Current LSN的起始位置,可以通过dbcc
opentran确定),统计出平均VLF中的记录数(由于实际环境中影响日志记录的因素较多,因此需要多看几个VLF来估算DDL操作日志量的平均占比情况),再根据DBCC
LOGINFO中当前活动VLF的数量推算出DDL操作的进度;

 

 

四. 数据库表的各种记录

发表评论

电子邮件地址不会被公开。 必填项已用*标注