January 12, 2010 at 7:34 am
Hi,
I have restored the databases to sql 2008.
Executed the updateusage and then check the status of the database.
Ran the below sql and output is
select name,status from master..sysdatabases
name status
master 65544
tempdb 65544
model 65536
msdb 65544
ReportServer 65536
ReportServerTempDB 65544
DB01 20
DB02 16
DB03 16
DB04 20
DB05 16
DB06 16
DB07 16
DB08 8
DB09 16
DB10 16
DB11 16
DB12 16
Next, i went to msdn, to check the status's and here is the list
http://msdn.microsoft.com/en-us/library/aa260406(SQL.80).aspx
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.
Here are my questions,
1. In my query output , i can see some databases with "status" 16 i.e torn page.
Is there any harm? if so what we can do to avoid such errors.
2. in my output if you can see status = 65544. What does it mean? i cannot find desc for this value. What does it signify?
3. i can also see status = 8 , what does it indicate. is it ok or else do we need to do something?
4. what does 65536 signify?
5. what does 65544 signify?
But good thing is that, all are online
select name,state,state_desc from sys.databases
name state state_desc
master 0 ONLINE
tempdb 0 ONLINE
model 0 ONLINE
msdb 0 ONLINE
ReportServer 0 ONLINE
ReportServerTempDB 0 ONLINE
DB01 0 ONLINE
DB02 0 ONLINE
DB03 0 ONLINE
DB04 0 ONLINE
DB05 0 ONLINE
DB06 0 ONLINE
DB07 0 ONLINE
DB08 0 ONLINE
DB09 0 ONLINE
DB10 0 ONLINE
DB11 0 ONLINE
DB12 0 ONLINE
Thanks in Advance
January 12, 2010 at 10:38 am
I would run a DBCC CheckDB against each of the databases in question (torn page, or status that you are uncertain about).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 12, 2010 at 11:51 am
You shouldn't be using status bits directly the translations are mostly spread out into separated columns now for easier use.
That said:
* Noel
January 12, 2010 at 12:09 pm
noeld (1/12/2010)
You shouldn't be using status bits directly the translations are mostly spread out into separated columns now for easier use.That said:
Nice script and thanks for the reference Noel.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2010 at 2:19 am
Thank You All.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply