Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Dec 13 '13 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. –  Craig Efrein Dec 13 '13 at 8:21
1  
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. –  Mark Storey-Smith Dec 13 '13 at 9:40
    
@MarkStorey-Smith. no way to say SELECT NTH [Current LSN] FROM fn_dblog(NULL, NULL) ? The idea is just to search one record, Nth from the bottom –  Craig Efrein Dec 13 '13 at 9:46
    
@MarkStorey-Smith - I see what you mean though, whatever I do, it will read the entire log file. –  Craig Efrein Dec 13 '13 at 9:48
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.