9

I am trying to find a way to figure out when was couple of my sql databases were taken offline.

I checked the the logs but could not find any such info and moreover there is no default trace enabled.

I just have a info that earlier there was a DBA who took that offline, but no emails or a written communication as such..

Can we find an info on this?

please suggest, thanks!

0
11

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).

0
5

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.

0
0

If you don't find anything in the logs (it could have been taken OFFLINE a while ago) you can always check the date modified of the physical data files of the database as it would have not changed after been taken OFFLINE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.