[SQL Server] Journal Transactions Divers requêtes
--Tronquer les logs en SQL Server 2005 :
backup log @dbname with truncate_only;
--Tronquer les logs en SQL Server 2008 :
BACKUP LOG BASE_MEKTABA TO DISK='NUL:'
sp_help 'sys.fn_dblog';
sp_helptext 'sys.fn_dblog';
use BASE_MEKTABA
select * from fn_dblog(null,null)
--where AllocUnitName like '%MEKTABA%'
where Context like '%LCX_HEAP%'
ou
dbcc log('BASE_MEKTABA')
SELECT
[Current LSN],
[Operation],
[Transaction ID],
[Description], SPID,[Begin Time], [Transaction SID],
name 'LoginName'
FROM fn_dblog(NULL, NULL),
(select sid,name from sys.syslogins) sl
where [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid
----
DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)
SET @LSN =(SELECT TOP 1 [Current LSN] FROM fn_dblog(NULL, NULL))
PRINT @LSN
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @LSN_HEX =(SELECT i FROM @tbl WHERE id = 1) + ':' +(SELECT i FROM @tbl WHERE id = 2) + ':' +(SELECT i FROM @tbl WHERE id = 3)
PRINT @LSN_HEX
SELECT *
FROM ::fn_dblog(@LSN_HEX, NULL)
select * from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'F:\stemp\stanley_test.tlog1.bak', DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
************************************************************
CREATE PROCEDURE sp_store_transaction
<AS
BEGIN
DECLARE @dm_transaction_id bigint
SELECT @dm_transaction_id = transaction_id FROM sys.dm_tran_current_transaction
IF OBJECT_ID(N'tempdb.dbo.##db_last_transaction') IS NOT NULL
DROP TABLE ##db_last_transaction
SELECT [transaction id] INTO ##db_last_transaction
FROM ::fn_dblog(null,null)
WHERE [Xact ID] = @dm_transaction_id
<END
GO
CREATE PROCEDURE sp_get_last_transaction
AS
BEGIN
SELECT *
FROM ::fn_dblog(null,null)
WHERE [Transaction ID] =(SELECT [transaction id] FROM ##db_last_transaction)
END
GO
And that is going to make looking at cause an effect far easier – first procedure you call anywhere within your transaction to store the current transaction_id within the log, and the second to retrieve the values for that stored transaction id. Something like:
BEGIN TRAN
UPDATE tbltest SET firstname = 'ab'
EXEC sp_store_transaction
COMMIT TRAN
sp_get_last_transaction
SELECT
SPID, -- The process ID for the transaction
[Begin Time], -- The start time for the transaction
[Current LSN], -- The lsn of this record
[Previous LSN], -- Previous lsn for this record
Operation, -- The operation performed by the log record
Context, -- The resource affected by the operation
[Log Reserve], -- The space reserved for rollback in bytes
AllocUnitName, -- The name a the affected allocation unit
[Page ID], -- The page ID of the affected page
[Number of Locks], -- The number of locks held by the transaction
[Lock Information] -- Information about the locks held and the resources locked
FROM fn_dblog(NULL, NULL)