|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679,
Visits: 953
|
|
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.
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730,
Visits: 29,996
|
|
No need for a procedure. Query sys.databases, most of the columns you want are in there.
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679,
Visits: 953
|
|
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
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730,
Visits: 29,996
|
|
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 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 1,871,
Visits: 2,692
|
|
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
---------------------------------------------- Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
|
|
|
|