Detecting offline databases by querying system tables

Question

Detecting offline databases by querying system tables

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
October 10, 2008 at 8:22 am

#124269

I'm looking at the BOL documentation of view sys.sysdatabases (http://msdn.microsoft.com/en-us/library/ms179900(SQL.90).aspx) and the status column is supposed to have the value of 512 for an offline database.
Yet, when I query the sys.sysdatabases view of my instance, where I have an offline/read-only database, the status is 1552.
Does anyone know how to automate detection of offline databases? The BOL documentation seems inaccurate.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

It's bitmapped. Meaning the 512 is the bit (#9) that determines if the database is offline. So you OR it with 512 to check the status of the bitmap.

http://www.sqlservercentral.com/articles/Miscellaneous/2748/

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Steve Jones - Editor (10/10/2008)

It's bitmapped. Meaning the 512 is the bit (#9) that determines if the database is offline. So you OR it with 512 to check the status of the bitmap.

http://www.sqlservercentral.com/articles/Miscellaneous/2748/%5B/quote%5D

Thanks, this stuff has always been a mystery to me.

Looks like I need to read that link...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

noeld SSC Guru Points: 96590 More actions · Answer 3

Marios Philippopoulos (10/10/2008)
I'm looking at the BOL documentation of view sys.sysdatabases (http://msdn.microsoft.com/en-us/library/ms179900(SQL.90).aspx) and the status column is supposed to have the value of 512 for an offline database.
Yet, when I query the sys.sysdatabases view of my instance, where I have an offline/read-only database, the status is 1552.
Does anyone know how to automate detection of offline databases? The BOL documentation seems inaccurate.

SELECT *

FROM sys.databases

WHERE DATABASEPROPERTYEX(name,'STATUS')= 'OFFLINE'

* Noel

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

noeld (10/10/2008)
Marios Philippopoulos (10/10/2008)
I'm looking at the BOL documentation of view sys.sysdatabases (http://msdn.microsoft.com/en-us/library/ms179900(SQL.90).aspx) and the status column is supposed to have the value of 512 for an offline database.
Yet, when I query the sys.sysdatabases view of my instance, where I have an offline/read-only database, the status is 1552.
Does anyone know how to automate detection of offline databases? The BOL documentation seems inaccurate.
SELECT *
FROM sys.databases
WHERE DATABASEPROPERTYEX(name,'STATUS')= 'OFFLINE'

Thanks, that worked!

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Or, since you're using 2005 you can simply use

SELECT name from sys.databases where state = 'OFFLINE'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass