Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Is there a good way to understand the sysdatabases STATUS fields? Expand / Collapse
Author
Message
Posted Wednesday, November 29, 2006 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 12,881, Visits: 31,816

you'll notice that this is really a way to store multiple values in a single data field....it kind of goes against the grain of a relational database...one field=one value and all that, but logic like this is used in a lot of applications;



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 #326415
Posted Wednesday, November 29, 2006 8:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
The explanation in books online wasn't clear enough, are you sure you read it??
Post #326416
Posted Monday, October 6, 2008 9:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:55 AM
Points: 7, Visits: 169
There are some databases showing as offline. But their status in sysdatabases are 4194840 and 528 (some are 4194840 and the others are 528)...
Do anyone knows what are those and what are the different?
Thanks in advance
Post #581485
Posted Monday, October 6, 2008 9:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 12,881, Visits: 31,816
4194840 = TORN_PAGE_DETECTION,OFFLINE,AUTOSHRINK
528 = TORN_PAGE_DETECTION,OFFLINE
my script from page one helps find the values, here's a snippet where i just plug in specific values:
declare @status int
SET @status = 528
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]

eason36 (10/6/2008)
There are some databases showing as offline. But their status in sysdatabases are 4194840 and 528 (some are 4194840 and the others are 528)...
Do anyone knows what are those and what are the different?
Thanks in advance


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 #581489
Posted Monday, October 6, 2008 10:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:55 AM
Points: 7, Visits: 169
Thanks. You are genius.
Post #581498
Posted Wednesday, January 7, 2009 11:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 1:44 AM
Points: 15, Visits: 98
Excellent information !!! thank you all, this helped me understanding the status value clearly.
Post #632135
Posted Friday, April 16, 2010 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 3:52 AM
Points: 2, Visits: 19
try out this function

select name from sysdatabases
where name not in ('master','model','msdb','tempdb','pubs','Northwind')
and DATABASEPROPERTYEX(name,'status')='ONLINE'

i got it from Hall of Fame (Rudy Komacsar Senior Database Administrator)


for more information read the following thread
http://www.sqlservercentral.com/Forums/Topic371321-5-1.aspx
Post #904691
Posted Friday, April 16, 2010 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 3:52 AM
Points: 2, Visits: 19
hi
Post #904699
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse