0

Find when a specific database went offline and the reason for same and when did it recover back.

All I know there is a SCOM alert set for the server and I got a notification as Database is offline and later when I checked I found it as online. So, I want to check at what time it went offline and how and when did it come online?

1

At minimum, please check sql logs, for example, if I purposely take a db offline using

alter database [TestDB] set offline with rollback immediate;

I can see in SQL Server log, the following message

Date 10/24/2016 12:28:57 PM Log SQL Server (Current - 10/24/2016 12:00:00 AM)

Source spid51

Message Setting database option OFFLINE to ON for database 'TestDB'.

  • We wont be able to find the reason as why it went offline and how did it come online ? – Puskar Oct 24 '16 at 19:39
  • I am not sure about the "reason", but I think I would first figure out who / when gets the database offline and then make logic conclusion as to what is the reason behind who/when doing this. On the other hand, I believe using Extended Event (database_stopped event), you can automate the monitoring of who/when in kind of real-time. – jyao Oct 24 '16 at 21:31
  • You are very welcome, @Puskar ! – jyao Oct 25 '16 at 5:41

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.