script to find out the databses which is in SUSPECT MODE

Dear All,
I have 120 dedicated database servers. Many of the times , some databses
were marked as suspect because of some unexpected reboot, hardware errors
etc. So i just want to know Is there any script to fetch all the databases
that is marked as suspect? So that i can run the script from a central serve
r
and make sure that all are working fine.
Thanks in advance,
JJYou can use the DATABASEPROPERTYEX() function, the status attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> Dear All,
> I have 120 dedicated database servers. Many of the times , some databses
> were marked as suspect because of some unexpected reboot, hardware errors
> etc. So i just want to know Is there any script to fetch all the databases
> that is marked as suspect? So that i can run the script from a central ser
ver
> and make sure that all are working fine.
> Thanks in advance,
> JJ|||Hi Tibor,
Thanks a lot for your quick reply. This will do the needful.
How can i collect these info to a central server from all database servers?
Thanks,
JJ
"Tibor Karaszi" wrote:

> You can use the DATABASEPROPERTYEX() function, the status attribute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
>|||I can see a few possible options:
On the central server set up linked servers to all the other servers, so you
can do something like:
SELECT ... FROM srvname.master.sys.databases...
Use the SMO API for this, and there connect to each server and then store th
e result in the central
server.
Use a more classic API, like ADO.NET to do the same.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:21BC8917-2138-4EC5-991E-D253C75FD338@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> Thanks a lot for your quick reply. This will do the needful.
> How can i collect these info to a central server from all database servers
?
> Thanks,
> JJ
> "Tibor Karaszi" wrote:
>|||On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
wrote:
> Hi Tibor,
> Thanks a lot for your quick reply. This will do the needful.
> How can i collect these info to a central server from all database servers
?
> Thanks,
> JJ
>
> "Tibor Karaszi" wrote:
>
>
>
>
> - Show quoted text -
Try , Not tested
SELECT Servername.master..sysdatabases
WHERE status & 256 != 256|||Hi Tibor,
i just tried to get the result using osql.
i have used the following query:-
osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
jj74.txt
the txt file is created successfully but when i checked the file and found
that it has nothing other than the caption "name".
Any idea'
Thanks,
JJ
"M A Srinivas" wrote:

> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> wrote:
> Try , Not tested
> SELECT Servername.master..sysdatabases
> WHERE status & 256 != 256
>|||Scroll down...
I suggest you CAST the result from DATABASEPROPERTYEX to something like varc
har(50) or something
suitable...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> i just tried to get the result using osql.
> i have used the following query:-
> osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> jj74.txt
> the txt file is created successfully but when i checked the file and found
> that it has nothing other than the caption "name".
> Any idea'
> Thanks,
> JJ
>
> "M A Srinivas" wrote:
>|||Great!! That works......
this can be executed succesfully against the local server, but if i want to
execute this a gainst remote sql server what extra things has to be added?
Thanks,
JJ
"Tibor Karaszi" wrote:

> Scroll down...
> I suggest you CAST the result from DATABASEPROPERTYEX to something like va
rchar(50) or something
> suitable...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
>|||You need to specify appropriate values for the /S parameter and appropriate
login...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...[vbcol=seagreen]
> Great!! That works......
> this can be executed succesfully against the local server, but if i want t
o
> execute this a gainst remote sql server what extra things has to be added?
> Thanks,
> JJ
> "Tibor Karaszi" wrote:
>|||Ohhh !!!! Sorry....

I got it...
Thanks a ton Tiber....
"Tibor Karaszi" wrote:

> You need to specify appropriate values for the /S parameter and appropriat
e login...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
>