Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Scenario:-

• During DR exercise,Prod and DR was configured with log shipping technology and once the Dbs. were brought online, somehow due to incorrect connection string settings by application team, Dbs. which were brought online gets dropped/deleted.
• As a DBA our primary tasks were to find out why and how databases getting dropped.
• Post identifying problems inside setting up connection strings through application, next things were to find out which user and what dbs got dropped.

Solution:

Graphical Way

• To find out user who is responsible for dropping off DBs, we have following way.
• Connect the DB server through management studio–>Go to Object explorer–>Report–>Standard report–>Schema changes history.

Using Default SQL Trace Files

The SQL Server Default Trace file provides lots of useful data to a DBA to understand what is going on inside SQL server. For more information, visit MSDN site.

Find events captured by the default trace.

SELECT DISTINCT Trace.EventID, TraceEvents.NAME AS Event_Desc
FROM   ::fn_trace_geteventinfo(1) Trace
,sys.trace_events TraceEvents
WHERE Trace.eventID = TraceEvents.trace_event_id

Execute the below query to find the default path of trace file in SQL Server.

SELECT
	 path AS [Default Trace File]
	,max_size AS [Max File Size of Trace File]
	,max_files AS [Max No of Trace Files]
	,start_time AS [Start Time]
	,last_event_time AS [Last Event Time]
FROM sys.traces WHERE is_default = 1
GO

How to Load SQL Server Trace File in SQL Server Table

Execute the below script to load the default trace file content in a temporary table of SQL server to read the required details with respect to who dropped the user database on the instance of SQL Server. If you don’t find the relevant information in the latest trace file then it is recommended to load the data from all the available trace files.

USE tempdb
GO

IF OBJECT_ID('dbo.TraceTablefordrop', 'U') IS NOT NULL
	DROP TABLE dbo.TraceTablefordrop;

SELECT * INTO TraceTablefordrop
FROM ::fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_99.trc', default)
GO

SELECT
	 DatabaseID
	,DatabaseName
	,LoginName
	,HostName
	,ApplicationName
	,StartTime
	,CASE
		WHEN EventClass = 46 THEN 'Database Created'
		WHEN EventClass = 47 THEN 'Database Dropped'
	ELSE 'NONE'
	END AS EventType
FROM tempdb.dbo.TraceTablefordrop
	WHERE DatabaseName = 'DropDbtest'
		AND (EventClass = 46 /* Event Class 46 refers to Object:Created */
			OR EventClass = 47) /* Event Class 47 refers to Object:Deleted */
GO

Hope this helps!