[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)