identifying offline databases

Question

Post reply

identifying offline databases

iainthepitman

SSCommitted

Points: 1666
More actions
January 14, 2008 at 1:41 am

#182058

is there a simple way of identifying offline databases on an instance from a system table? there is a status column in sysdatabases but i cant find a way of showing databases that are offline. any ideas?

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

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

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Try using DATABASEPROPERTY() function with the 'IsOffline' property.

Paul

iainthepitman SSCommitted Points: 1666 More actions · Answer 2

problem with that is you need to change the database context for each database or create some kind of loop to change the database name in the function. i was hoping that it would be easy to extract a value from sysdatabases (or somewhere else) so i can identify all the offline databases in an instance using one sql query.

iainthepitman SSCommitted Points: 1666 More actions · Answer 3

actually, i think this works.

SELECT name FROM master..sysdatabases

WHERE substring(convert(binary, status),len(convert(binary, status))-1,1) <> 0x02

kgottfredsen Mr or Mrs. 500 Points: 542 More actions · Answer 4

How about this:

SELECT [name]

FROM master.dbo.sysdatabases

WHERE CAST(DATABASEPROPERTY([name], 'IsOffline') as int) = 1

ORDER BY [name]

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 5

This works too:

SELECT [name] AS Name, dbid AS DBID,

DATABASEPROPERTYEX([name], 'Status')AS DBStatus

FROM master.dbo.sysdatabases