July 1, 2010 at 4:33 am
Hi,
I need to create a procedure that analises my databases on the server (all the databases) and see if any one of them is not alright (suspect, offline, readonly, dbcc checkdb)...
I was trying to do this, but i don't know how to do it.
Can someone give a help, please?
Thank you.
July 1, 2010 at 4:36 am
No need for a procedure. Query sys.databases, most of the columns you want are in there.
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
July 1, 2010 at 4:45 am
Thank you for reply GAIL.
you sugest this?:
select * from master.sys.databases
where [state_desc] <> 'online'
Question:
If a database is suspect what will be is state_desc?
If a database is in read_only what will be is state_desc?
thank you
July 1, 2010 at 4:52 am
river1 (7/1/2010)
If a database is suspect what will be is state_desc?
SUSPECT
If a database is in read_only what will be is state_desc?
Probably ONLINE. The is_read_only column (also in sys.databases) will have a value of 1.
I suggest you read the entry on sys.databases in Books Online. It details the possible values for many of the columns.
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
July 1, 2010 at 4:56 am
simple script, but should give you the results...
select name,
database_id,
user_access_desc,
state_desc,
(CASE is_in_standby WHEN '0' THEN 'NO' ELSE 'YES' END) as Is_in_standby,
GETDATE() as time_stamp
from sys.databases
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply