As a DBA often we face a scenario that someone has dropped the database and we need to find out who dropped it. We can easily find the information from Schema Changes History and SQL Server Log. But here we find an excellent stored procedure to find the same at https://raresql.com.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Use Master Go CREATE PROCEDURE Recover_Dropped_Database_Detail_Proc @Date_From DATETIME='1900/01/01', @Date_To DATETIME ='9999/12/31' AS ;WITH CTE AS ( Select REPLACE(SUBSTRING(A.[RowLog Contents 0],9 ,LEN(A.[RowLog Contents 0])),0x00,0x) AS [Database Name] ,[Transaction ID] FROM fn_dblog(NULL,NULL) A WHERE A.[AllocUnitName] ='sys.sysdbreg.nc1'AND A.[Transaction ID] IN ( SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT') AND [Transaction Name] LIKE '%dbdestroy%' AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)) SELECT A.[Database Name] ,B.[Begin Time] AS [Dropped Date & Time] ,C.[name] AS [Dropped By User Name] FROM CTE A INNER JOIN fn_dblog(NULL,NULL) B ON A.[Transaction ID] =B.[Transaction ID] AND Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT') AND [Transaction Name] LIKE '%dbdestroy%' INNER JOIN sys.sysusers C ON B.[Transaction SID]=C.[Sid] GO -- Run the below-stored procedure to find the deleted database info EXEC Recover_Dropped_Database_Detail_Proc GO |
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…
Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.