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

Procedure to see database status Expand / Collapse
Author
Message
Posted Thursday, July 1, 2010 4:33 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
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.
Post #946053
Posted Thursday, July 1, 2010 4:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 42,460, Visits: 35,520
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

Post #946055
Posted Thursday, July 1, 2010 4:45 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
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
Post #946061
Posted Thursday, July 1, 2010 4:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 42,460, Visits: 35,520
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

Post #946063
Posted Thursday, July 1, 2010 4:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 1,904, Visits: 2,827
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.
Post #946066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse