When was a Database taken Offline
You can discover all of these events that are in the current event log cycle using sp_readerrorlog
:
EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'OFFLINE';
You can cycle through values of @p1
if you don't find it in the current event log. By default you should be able to read the current and prior 6 error log files, so use 0-6 as the arguments there to go back as far as possible (on my system I could not get 0
/NULL
to aggregate across all log files; YMMV).
Will return something like this:
LogDate ProcessInfo Text
------------- ----------- ---------------------------------------------------------
yyyy-mm-dd... spid72 Setting database option OFFLINE to ON for database 'foo'.
There's a chance, of course, that the error log gets populated enough that the event(s) happened before the current set of error logs. In that case, you are out of luck. To keep a longer running history in the future, you can change the number of error logs that are kept. In Object Explorer, expand Management, right-click SQL Server Logs, and choose Configure. There you can change error log file recycling settings, including keeping up to the previous 99 files. Also see this answer.
Note that sp_readerrorlog
is undocumented and unsupported, though many people have written about it. In the end, the error log files are just plain text files, so you could write your own PowerShell, CLR etc. that just parses the files and returns the same information. You can determine where the error log files are for this instance using:
SELECT SERVERPROPERTY('ErrorLogFileName');
The files will be named ERRORLOG
, ERRORLOG.1
, ERRORLOG.2
, etc. You can go and open the files in a basic text editor to see the structure, though I would be cautious about opening the current file in use (ERRORLOG
).
If the database was set offline:
i.e.
alter database AdventureWorks2012
set offline;
go
You would indeed see a logged message in the SQL Server error log:
Setting database option OFFLINE to ON for database 'AdventureWorks2012'.
select *
from sys.messages
where language_id = 1033
and text like '%setting database option%for database%';
Message ID of 5084 if you're monitoring database option changes.