November 18, 2009 at 2:57 pm
When I do the bit math to decode master.dbo.sysdatabases.status, I am seeing 65536 for some of our databases, but not all of them. A google search lead me to believe that 65536 means "online." However, if that is the case, wouldn't all of the databases (which are all online) have that bit set? (They don't.)
I have pasted the Books Online documentation below, but it does not define bit 65536. Can anyone point me to additional documentation?
Thanks in advance.
----------------------------------
status int Status bits, some of which can be set by the user with ALTER DATABASE (read only, offline, single user, and so on):
1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
November 18, 2009 at 3:35 pm
November 18, 2009 at 7:01 pm
AlexSQLForums (11/18/2009)
http://www.sqlservercentral.com/Forums/Topic326235-5-1.aspx
Sorry, I'm looking for a "65536" hit on the status column, rather than the status2 column.
November 18, 2009 at 7:38 pm
shew (11/18/2009)
AlexSQLForums (11/18/2009)
http://www.sqlservercentral.com/Forums/Topic326235-5-1.aspxSorry, I'm looking for a "65536" hit on the status column, rather than the status2 column.
look at the post again...the code for reading the status column is a couple of posts above the post for the status2 column.
page two of the post has the code to plug in a number and see what bits are flipped;
it's kind of interesting that 65536 and 0 have the same bits flipped.
declare @status int
SET @status = 65536
select @status as OriginalStatus,
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]
Lowell
November 19, 2009 at 3:40 am
look at the post again...the code for reading the status column is a couple of posts above the post for the status2 column.
Sorry I don't see 65536 in the link or your post for status either. This list omits status, as my original post does. Am I missing something?
November 19, 2009 at 4:18 am
Since you're using SQL 2005 (or I assume you are, since this is posted in the SQL 2005 forums), use sys.databases instead. The statuses are neatly broken down in a readable format in that view.
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
November 19, 2009 at 6:01 am
GilaMonster (11/19/2009)
Since you're using SQL 2005 (or I assume you are, since this is posted in the SQL 2005 forums), use sys.databases instead. The statuses are neatly broken down in a readable format in that view.
Bless you! Yes, I am working with SQL 2005. Unfortunately, I inherited this environment after it was installed and the former DBA left without warning. Most of my SQL Server experience spans 4.21a through 2000. I didn't know that sys.databases exists. This looks great! Thanks so much.
November 19, 2009 at 7:09 am
Take a look at the new system views. sysdatabases and many others that you would know from SQL 2000 are now deprecated and will be removed in a future version.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply