Identifying offline databases programmatically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identifying offline databases programmatically

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-29 : 16:24:36

How can I identify databases that are NOT online using T-SQL. I'm not only looking for offline. Restoring, standby, etc as well.

I also need this to cover 2000, 2005 and 2008 databases. I've been looking at master.dbo.sysdatabases and the best I can do is look at the version column.

There must be a better way though.

Thanks for the help!

Craig Greenwood

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 16:40:30

Did you look at the Status column?

DECLARE	@Status TABLE
	(
		Code INT,
		Msg VARCHAR(1000)
	)

INSERT	@Status
SELECT	1, 'autoclose (ALTER DATABASE)' UNION ALL
SELECT	4, 'select into/bulkcopy (ALTER DATABASE using SET RECOVERY)' UNION ALL
SELECT	8, 'trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)' UNION ALL
SELECT	16, 'torn page detection (ALTER DATABASE)' UNION ALL
SELECT	32, 'loading' UNION ALL
SELECT	64, 'pre recovery' UNION ALL
SELECT	128, 'recovering' UNION ALL
SELECT	256, 'not recovered' UNION ALL
SELECT	512, 'offline (ALTER DATABASE)' UNION ALL 
SELECT	1024, 'read only (ALTER DATABASE)' UNION ALL
SELECT	2048, 'dbo use only (ALTER DATABASE using SET RESTRICTED_USER)' UNION ALL
SELECT	4096, 'single user (ALTER DATABASE)' UNION ALL
SELECT	32768, 'emergency mode' UNION ALL
SELECT	4194304, 'autoshrink (ALTER DATABASE)' UNION ALL
SELECT	1073741824, 'cleanly shutdown'

SELECT		d.Name,
		d.dbID,
		s.Msg
FROM		master.dbo.sysdatabases AS d
INNER JOIN	@Status AS S ON s.Code & d.status > 0
ORDER BY	d.Name,
		s.Code

N 56°04'39.26"
E 12°55'05.63"

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-29 : 17:06:42

This is most useful. I have a question though. One database I am using in my tests is currently offline. It is called "Measures" and reports status as 520 in the sysdatabases table. 520 is NOT in your list. 8+512=520 and I am getting messages for status 8 and status 512. Which is way cool.

When I ran this on one server it pulls a LOT of information, including databases that are online, such as master, model, msdb and other non-system databases. Is it possible to tweak this to pull only databases that are not online?

Craig Greenwood

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-29 : 17:15:05

You should be able to adapt this for what you need:

select
	convert(varchar(20),databasepropertyex(a.name, 'Status')) as DB_Status,
	a.name
from
	master.dbo.sysdatabases a
order by 
	a.name

Results:

DB_Status            name
-------------------- ---------------
ONLINE               master
ONLINE               model
ONLINE               msdb
ONLINE               Northwind
OFFLINE              pubs
ONLINE               tempdb

(13 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources