5

Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc..

Here is the query:

SET NOCOUNT ON
DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)
DECLARE @NUMOFTRANSACTIONS AS INT = 3;

-- common table expression to get the last N LSNs from the LDF
with currentlsns as (
SELECT TOP(@NUMOFTRANSACTIONS) [Current LSN] FROM fn_dblog(NULL, NULL) WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] DESC
)
-- set the @LSN to the lowest because we are reading forward
SELECT @LSN = MIN([Current LSN]) from currentlsns


-- Convert @LSN to HEX for use in fn_dblog
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)

-- insert everything into a temp table so we only refer to fn_dblog once
IF OBJECT_ID('tempdb..#transactions') IS NOT NULL 
DROP TABLE #transactions

SELECT * INTO #transactions 
FROM fn_dblog (@LSN_HEX, NULL)


-- now show last N transactions and some connection, query info for those transactions
SELECT fd.SPID, es.login_name, text, Description, Operation, Context, [Log Reserve],AllocUnitName,[Begin Time],[Transaction Name], 
[End Time], [Transaction Begin],[Number of Locks] 
FROM
    #transactions,
    (   SELECT
            [Transaction ID] AS [tid],
            [Transaction SID],
            SPID
        FROM #transactions
        WHERE
        [Transaction SID] IN (select top(@NUMOFTRANSACTIONS) [Transaction SID] FROM #transactions WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] desc)
   ) [fd]
INNER JOIN sys.dm_exec_connections c on c.session_id = fd.SPID 
LEFT OUTER JOIN sys.dm_exec_sessions es ON es.session_id = fd.SPID
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) as st
WHERE
    [Transaction ID] = [fd].[tid]
    AND [Transaction ID] IN (select top(@NUMOFTRANSACTIONS) [Transaction ID] FROM #transactions WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] desc
 )

enter image description here

This part of the query takes too long

-- common table expression to get the last N LSNs from the LDF
with currentlsns as (
SELECT TOP(@NUMOFTRANSACTIONS) [Current LSN] FROM fn_dblog(NULL, NULL) WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] DESC
)
-- set the @LSN to the lowest because we are reading forward
SELECT @LSN = MIN([Current LSN]) from currentlsns

I just want to get the Nth from the last LSN where the Transaction SID is NOT NULL. The only way I could think of to accomplish this was by creating a CTE and inserting the last N LSN's that match.

Question: What would be a better way to find the last N'th Current LSN where [Transaction SID] IS NOT NULL?

7
  • Note that fn_dblog is undocumented and unsupported. Out of curiosity, what are you trying to achieve ? Any specific problem you are trying to solve ?
    – Kin Shah
    Dec 13, 2013 at 0:51
  • Hello @Kin. Yes I am aware of that. Recently there was a LDF file that kept growing inspite of numerous checkpoints, trn backups and there wasn't any obvious reason. Reading the LDF directly allowed me to find out at least who was working in that database. Dec 13, 2013 at 8:21
  • 2
    I don't think there's anything you can do to improve this. The execution plan suggests the log file is handled as a file and so has to be read in full before a filter is applied to remove null [Transaction SID] records. Dec 13, 2013 at 9:40
  • 1
    If you're using SQL Server 2012, a better way might be to use Extended Events.
    – Paul White
    Dec 16, 2013 at 7:37
  • 1
    I might be worth piping the output of fn_dblog into a temp table before ordering/topping the contents. fn_dblog is not back by an ordinary table, so getting "in and out" may be faster than trying to run logic on it Dec 28, 2013 at 15:00

1 Answer 1

1

SELECT * into dbo.dblog

FROM fn_dump_dblog ( NULL, NULL, N'DISK', 1, N'C:\tranlog.trn', 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); GO

Then add indexing, etc to the table dbo.dblog.

2
  • Hello and thanks for your answer. I read on sqlskills that using fn_dump_dblog will create additional scheduler threads that don't go away, which is why I've avoided it. Is this true? sqlskills.com/blogs/paul/… Jul 18, 2014 at 7:36
  • 2
    I don't know. If Paul Randall says that it does, then it does. You can always copy the tranlog to your personal machine and do it there instead of on your production server.
    – stacylaray
    Jul 28, 2014 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.