fn_dblog & fn_dump_dblog

2023-02-01

153
0
MSSQL

--fn_dblog

select * from sys.dm_cdc_errors where session_id = 155

select [Current LSN], Operation, context, [Transaction ID],[Transaction Name], [Previous LSN], AllocUnitId,PartitionId,

[Transaction Name], Description from fn_dblog(null,null) where [Current LSN] in ('00000024:00000EF8:0001', '00000024:000005A8:0001')

-- replace [Current LSN] with start_lsn and begin_lsn in preious query result

select [Current LSN], Operation, context, [Transaction ID],[Transaction Name], [Previous LSN], AllocUnitId,PartitionId,

[Transaction Name], Description from fn_dblog(null,null) where [Current LSN] in ('00000024:00000EF8:0001', '00000024:000005A8:0001')

or [Transaction ID] in ('0000:000004bd','0000:000005ea') -- replace [Transaction ID] with Transaction ID in previous query result

select object_name(object_id) from sys.partitions where partition_id = 72057594041270272 -- replace [partition_id] with partitionid in previous query result

--fn_dump_dblog

SELECT [Current LSN], [Transaction ID],

[PartitionId],

[Operation],

[Begin Time],

SUSER_SNAME([TRANSACTION SID]) as [LoginName]

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\temp\TestCDCDB_log.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

where-- PartitionId in (72057594041270272, 72057594042122240) or

[Current LSN] in ('00000024:00000EF8:0001', '00000024:00000EF8:0001') -- get transaction ID

or [Transaction ID] = '0000:000005ea'

回首頁