Are you familiar with SQL Server's default trace setting? It can be helpful with finding basic who/when type information on major events. For example, you may want to know who was creating and dropping databases on a given instance.
SQL Server has a couple of options that might help you find out more about when/by who the database is being created and dropped. One is Policy Based Management but you would need to configure it ahead of time. Another option is to run a profiler trace that captures information such as CREATE, ALTER, DROP DATABASE. Some of the DMVs might have the execution information if you capture it fast enough after it happens. XEvents can be used in SQL 2008 to find all sorts of information. However, the one that might be most appropriate in this case is the Default Trace.
1) Make sure the default trace is enabled in your configuration options for this instance. If it is not enabled, you can enable it through the sp_configure settings.
-- Check to see if the default trace is enabled (0=off, 1=on)
EXEC sp_configure 'default trace enabled'
-- Enabled the default trace
EXEC sp_configure 'default trace enabled', 1
2) The trace files will eventually overwrite themselves, so check for the output soon after the problem occurs (perhaps make periodic copies of the files). They will be under the log directory where SQL Server is installed. For example, for my SQL 2008 instance named WASH the output files are in C:\Program Files\Microsoft SQL Server\MSSQL10.WASH\MSSQL\Log. The files will be named log_xxx.trc and there will be up to 5 of them.
3) Find the trace which covers the time period when the database was created or dropped. You can either open it in the Profiler GUI or you can use the query below to pull out the appropriate data. Look for the create and/or drop events and see who executed them from what workstation and at what time. Some applications will send their "application name" so you may be able to tell that as well.
· You cannot control what is captured by the default trace, how many files it captures before rolling over, or any other options. Your only option is to turn it on or off. If you want a similar trace that differs in any way you can create your own and configure it to start when SQL Server starts (or whatever time period is appropriate).
· The trace file name/number will continue to increase until you delete the files.
· The trace does NOT capture all events, it is very lightweight.
· SQL Server 2008 Internals - Chapter 1 page 73
· Searching for a Trace - Solving the mystery of SQL Server 2005's default trace enabled option http://www.sqlmag.com/Articles/ArticleID/48939/pg/1/1.html
· SQL Server Default Trace http://blogs.technet.com/beatrice/archive/2008/04/29/sql-server-default-trace.aspx
· Default Trace in SQL Server 2005 http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx
· Default Trace in SQL Server 2005 http://www.mssqltips.com/tip.asp?tip=1111
-- Example of using the default trace to find out more about who/when/why a database is dropped or created
-- Get current file name for existing traces
SELECT * FROM ::fn_trace_getinfo(0)
-- CHANGE THIS VALUE to the current file name
DECLARE @Path nvarchar(2000)
SELECT @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL10.WASH\MSSQL\Log\log_120.trc'
-- Get information most relevant to CREATE/DROP database
SELECT SPID, LoginName, NTUserName, NTDomainName, HostName, ApplicationName, StartTime, ServerName, DatabaseName
WHEN 46 THEN 'CREATE'
WHEN 47 THEN 'DROP'
END AS EventClass
, CASE ObjectType
WHEN 16964 THEN 'DATABASE'
END AS ObjectType
WHERE ObjectType = 16964 /* Database */ AND EventSubClass = 1 /* Committed */
ORDER BY StartTime
== Event Class
Indicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.
Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.
== Object Type
int Type of event subclass.
I wrote a set of reports for SSMS, which query the Default trace and agregate data from all 5 files.
Here is some documentation: sqlconcept.com/default-trace-audit-documentation-and-faq
And here are some screenshots of some of them: sqlconcept.com/.../screenshots
Can I change Retention period and size of default trace ?