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.
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.
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.
And, yes it does.
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.
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.