Microsoft – SQL Server – Profiler – Database ” cannot be opened because it is offline

Background

For some odd reason, I ended up in Microsoft SQL Server Profiler land looking at session traces.

 

Database cannot be opened

As I dug deeper, I noticed quite a few repeating entries bearing:


Database 'DBLabInMemory' cannot be opened because it is marked offline.

 

DatabaseCannotBeOpened

 

 

Diagnostic

Got me thinking where is the error coming from?

  • Another Application on my machine
  • SQL Server Agent
  • Internet Parlance

 

I tried everything:

  • Using “SQL Server Configured Manager”, disabled TCP/IP Network Protocol for the SQL Instance
  • Using Services Management Console, determined that SQL Sever Agent is not enabled to run
  • Using MS Network Monitor, captured network traffic – No 1433
  • Using Netstat.exe, captured and analyzed network traffic
  • Reviewed SQL Server Error Log – Knowing that Microsoft has done a very good job writing much more detailed connectivity errors into its errorlog

 

Nada.  Nothing lined up.

 

Hunch

 

Create a new database

Created a new database unknown to the world and no other application

create database [DBOffline];

alter database [DBOffline] set offline;

 

Monitor SQL Server Profiler

Monitor SQL Server Profiler and see if same issue occurs for this new database.

DatabaseCannotBeOpened - DB Name -  DBOffline

And, yes it does.

 

Interpretation

 

Column Interpretation
Login Name = sa sa
spid < 50 System process has spid less than 50
ClientProcessID is blank Internal Session

 

It is an internal SQL Server Session and not anything we are doing.  One of the Internal processes is trying to perform ‘housekeeping’ and it is not checking the status of the DB, before trying to access\open it.

 

Summary

It is my humble opinion that SQL Server Internal processes should not surface errors caused by an Administrators’s intentional act of having a database offline.

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s