Procedure to see database status

Question

Procedure to see database status

river1

SSC-Insane

Points: 24302
More actions
July 1, 2010 at 4:33 am

#157046

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

river1 SSC-Insane Points: 24302 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 4

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