CODEWITHSUNDEEP
sql-server
jahu
jahu

Reputation: 5657

How to find the names of recently dropped functions?

Apparently I can find drop operations in the log using:

SELECT * FROM sys.fn_dblog(NULL, NULL)
WHERE [transaction name] IN ('DROPOBJ');

but I need to find out what actually was dropped (name of a function, aggregate etc.) and the log mostly seems to contain ids and binary data and not the name. How do I get the names of dropped objects (ideally without the use of 3rd party tools)?

Upvotes: 2

Views: 583

Answers (2)

Yuri
Yuri

Reputation: 1814

It could be seen using the query below also (don't forget to switch database context or manually write:

USE DATABASE_NAME
GO

And then:

DECLARE @Database_Name NVARCHAR(MAX) = 'DATABASE_NAME'
    ,@DateFrom DATETIME = '2015-08-01'
    ,@DateTo DATETIME = GETDATE();

WITH TransactionTable
AS (
    SELECT DISTINCT [TRANSACTION ID]
    FROM sys.fn_dblog(NULL, NULL)
    WHERE Context IN ('LCX_NULL')
        AND Operation IN ('LOP_BEGIN_XACT')
        AND [Transaction Name] = 'DROPOBJ'
        AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @DateFrom
        AND @DateTo
    )
SELECT [Database Name]
    ,CONVERT(VARCHAR(Max), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Action]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
    AND [Context] = 'LCX_MARK_AS_GHOST'
    AND [AllocUnitName] = 'sys.sysobjvalues.clst'
    AND [TRANSACTION ID] IN (
        SELECT *
        FROM TransactionTable
        )
    AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0

Upvotes: 1

wiretext
wiretext

Reputation: 3342

please see below images i hope this will help you enter image description here

Upvotes: 2

Related Questions