Last Deleated History [LDH]
1. Find Recently Deleted Rows (Data History)If data was recently deleted from a table and the database is in Full Recovery Model, SQL Server logs every deletion in the transaction log. You can query these active logs using the undocumented system function sys.fn_dblog. [1] (https://stackoverflow.com/questions/13786028/undelete-recently-deleted-rows-sql-server)
Run the following query to view the history of DELETE operations, including the transaction ID and the exact time it happened
SELECT
[Current LSN],
[Operation],
[Context],
[Transaction ID],
[Begin Time],
AllocUnitName AS [Table Name]
FROM sys.fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
AND AllocUnitName LIKE '%YourTableName%';
Use code with caution.Note: The actual data in [RowLog Contents 0] will be in Hex format. To read or recover the exact data, you would need to use a third-party log reader tool or restore a transaction log backup up to a specific Log Sequence Number (LSN).
2. Find Recently Deleted Objects (Tables, Views, Databases)If someone deleted (dropped) an entire table, column, or database, you can find the history using SQL Server's built-in Default Trace. This trace captures schema changes automatically unless it has been manually disabled.
Run this script to extract the last deleted objects from the trace files:sql
DECLARE @trace_file VARCHAR(MAX);
-- Get the path to the current default trace file
SELECT @trace_file = path
FROM sys.traces
WHERE is_default = 1;
-- Query the trace data for Object Deleted events (EventClass 47)
SELECT
StartTime AS [Deletion Time],
NTUserName AS [Windows User],
LoginName AS [SQL Login],
DatabaseName,
ObjectName,
EventClass
FROM fn_trace_gettable(@trace_file, DEFAULT)
WHERE EventClass = 47 -- 47 corresponds to "Object:Deleted"
ORDER BY StartTime DESC;
Use code with caution.
3. You want to find who executed a DELETE query If you are looking for the historical execution of a DELETE statement, you can get it from the plan cache using Dynamic Management Views (DMVs).
SELECT
dest.text AS [Query Text],
deqs.last_execution_time AS [Last Executed],
deqs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text LIKE '%DELETE%'
AND dest.text NOT LIKE '%sys.dm_exec_query_stats%' -- Filter out this tracking query
ORDER BY deqs.last_execution_time DESC;
Additionally,
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Context],
[AllocUnitName] AS [TableName],
[Transaction Name],
[Begin Time],
[End Time],
SUSER_SNAME([Transaction SID]) AS [UserWhoDeleted]
FROM
sys.fn_dblog(NULL, NULL)
WHERE
[Operation] = 'LOP_DELETE_ROWS' -- Looks for row deletions
ORDER BY
[Begin Time] DESC;
Key Strengths of SQL Server Patching
Security Enhancement: Microsoft Cumulative Updates (CUs) and General Distribution Releases (GDRs) guard against exploits like SQL injections and data theft by resolving known CVEs (Common Vulnerabilities and Exposures).
Performance and Stability: Updates clear out known engine bugs and introduce performance optimizations that keep your queries running smoothly.
Compliance Assurance: Staying updated satisfies internal and external auditors, preventing legal and financial penalties tied to data leaks.
Full Support Eligibility: Maintaining a supported build level ensures you remain eligible for official Microsoft assistance and hotfixes.
Regards
Amit Vaid
No comments:
Post a Comment