tsql- to find when was the database taken offline

Question

Is there a way how we can know when was a database taken offline? Platform: SQL server 2005

Answer 1

There doesn't seem to be a way in TSQL to do this. It isn't captured as an attribute in in the sys.databases catalog view, and most other possibilities (such as DBCC) require the database to be online in order to work.

However, the information is captured (with date/time stamp) in the event log. So you'll see an event from MSSQLSERVER with a description such as "Setting database option OFFLINE to ON for database MyDatabase".

Answer 2

You may get lucky if you have default trace enabled. Try this code below if it worked and let me know. Note that this code will show all records that made modifications to the database, not onlt taking it offline.

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'

FROM sys.traces

WHERE is_default = 1;

SELECT

    gt.EventClass,
    e.name as EventName,
    gt.TextData,
    gt.ObjectID,
    gt.ObjectName,
    gt.DatabaseName,
    gt.SessionLoginName,
    gt.StartTime,
    gt.ApplicationName,
    gt.HostName,
    gt.NTUserName,
    gt.NTDomainName
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN sys.trace_events e 
    ON gt.EventClass = e.trace_event_id
WHERE gt.EventClass = 164  -- Object Altered Event
  AND ObjectType = 16964   -- Database Object

Answer 3

Random thought: the file date/time stamp may change as the DB engine takes it offline.

From ALTER DATABASE

The database is closed, shut down cleanly, and marked offline. The database cannot be modified while it is offline.

I can't test this idea, sorry.

Edit:

In SQL Server 2000 there is the undocumented xp_getfiledetails which is not in SQL Server 2005.

In ye olden days, I used this as a DBA to read the file timestamps quite cleanly. Anything to avoid using DTS and keep as much as I could in t-sql...