where is the database 'state' is stored

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » where is the database 'state' is stored

where is the database 'state' is stored

Rate Topic

Display Mode

Topic Options

Author

Message

charipg

Posted Tuesday, September 08, 2009 1:28 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:43 AM
Points: 378, Visits: 897

where is the database 'state' is stored.
Means in which table or view.

Post #784521

matt6288

Posted Tuesday, September 08, 2009 1:30 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, January 28, 2013 2:05 PM
Points: 1,130, Visits: 1,181

have you checked sysdatabases? i don't have a sql 7 instance available. try sysdatabases or sys.databases

Post #784525

charipg

Posted Tuesday, September 08, 2009 1:34 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:43 AM
Points: 378, Visits: 897

yes i checked .there is no 'state column in sysdatabases.'

Post #784529

matt6288

Posted Tuesday, September 08, 2009 1:38 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, January 28, 2013 2:05 PM
Points: 1,130, Visits: 1,181

It looks like it is the status column you are after but it is the addition of the bits. Someone has probably written a script to query this but here are the definition from BOL.

Status bits, some of which can be set by using ALTER DATABASE as noted:

1 = autoclose (ALTER DATABASE)

4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)

8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)

16 = torn page detection (ALTER DATABASE)

32 = loading

64 = pre recovery

128 = recovering

256 = not recovered

512 = offline (ALTER DATABASE)

1024 = read only (ALTER DATABASE)

2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)

4096 = single user (ALTER DATABASE)

32768 = emergency mode

4194304 = autoshrink (ALTER DATABASE)

1073741824 = cleanly shutdown

Multiple bits can be ON at the same time.

Post #784537

matt6288

Posted Tuesday, September 08, 2009 1:41 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, January 28, 2013 2:05 PM
Points: 1,130, Visits: 1,181

Found this on the site:

select name as DBNAME,
CASE WHEN (STATUS & 1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],
CASE WHEN (STATUS & 64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],
CASE WHEN (STATUS & 128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],
CASE WHEN (STATUS & 256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],
CASE WHEN (STATUS & 512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],
CASE WHEN (STATUS & 2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],
CASE WHEN (STATUS & 4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],
CASE WHEN (STATUS & 4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK_(ALTER_DATABASE)],
CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]
from master.dbo.sysdatabases

Post #784540

charipg

Posted Tuesday, September 08, 2009 1:48 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:43 AM
Points: 378, Visits: 897

i want to know the 'state' of the databases.
Means online,offline,suspect.
where this information stored?

Post #784551

matt6288

Posted Tuesday, September 08, 2009 2:19 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, January 28, 2013 2:05 PM
Points: 1,130, Visits: 1,181

ok, what about the status column of the sp_helpdb sproc?

edit: doesn't look like this returns anything for offline databases in sql 2008

Post #784565

Lowell

Posted Tuesday, September 08, 2009 2:32 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 11,605, Visits: 27,643

Mat gave you the script to read the flags of the status column; that is exact where it is stored/accessible...it might not be exactly what you wanted, but that's where it is.

here's the both status and status2 for every db;
In 2005, the info is a little easier to read in sys.databases (select * from sys.databases)

select name as DBNAME,STATUS,
CASE WHEN (STATUS &         1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],
CASE WHEN (STATUS &         4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS &         8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS &        16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],
CASE WHEN (STATUS &        32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],
CASE WHEN (STATUS &        64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],
CASE WHEN (STATUS &       128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],
CASE WHEN (STATUS &       256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],
CASE WHEN (STATUS &       512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE_(ALTER_DATABASE)],
CASE WHEN (STATUS &      1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],
CASE WHEN (STATUS &      2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],
CASE WHEN (STATUS &      4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],
CASE WHEN (STATUS &     32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],
CASE WHEN (STATUS &   4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK_(ALTER_DATABASE)],
CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]
from master.dbo.sysdatabases

SELECT name as DBNAME,STATUS2,
CASE WHEN (STATUS2 &     16384) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_null_default_(ALTER_DATABASE)],
CASE WHEN (STATUS2 &     65536) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [concat_null_yields_null_(ALTER_DATABASE)],
CASE WHEN (STATUS2 &    131072) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [recursive_triggers_(ALTER_DATABASE)],
CASE WHEN (STATUS2 &   1048576) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [default_to_local_cursor_(ALTER_DATABASE)],
CASE WHEN (STATUS2 &   8388608) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [quoted_identifier_(ALTER_DATABASE)],
CASE WHEN (STATUS2 &  33554432) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [cursor_close_on_commit_(ALTER_DATABASE)],
CASE WHEN (STATUS2 &  67108864) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_nulls_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 268435456) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_warnings_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 536870912) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [full_text_enabled_(set_by_using_sp_fulltext_database)]
from master.dbo.sysdatabases

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #784573

« Prev Topic | Next Topic »

Permissions