List of Suspect Databases | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

List of Suspect Databases

I want ot get the list of suspected databases as one of our servers has shown around 15-20 databases in suspect mode. I ran this query. declare @Dbname as varchar(150)
declare @S as bit
DECLARE cur_DatababeNames CURSOR FOR
select name from sysdatabases
OPEN cur_DatababeNames
FETCH NEXT FROM cur_DatababeNames into @Dbname WHILE @@FETCH_STATUS = 1
BEGIN
select @S = databaseproperty (@Dbname,’IsSuspect’)
if @S = 1
begin
print @Dbname
end
FETCH NEXT FROM cur_DatababeNames into @Dbname end
Close cur_DatababeNames
But this gave me only two databses. what will be the issue —————————————-
http://spaces.msn.com/members/dineshasanka

databaseproperty is only there for backwards compatibility. You should use databasepropertyex instead.<br /><br />There’s also no need for the cursor <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />.<br /><br />select name, databasepropertyex(name,’Status’)<br />from sysdatabases<br />where databasepropertyex(name,’Status’) &lt;&gt; ‘ONLINE'<br /><br />You could look for a status of ‘SUSPECT’ but you might as well look for all non-online databases.<br /><br />Hope that helps,<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br /><br />I want ot get the list of suspected databases as one of our servers has shown around 15-20 databases in suspect mode. I ran this query.<br /><br /><i>declare @Dbname as varchar(150)<br />declare @S as bit<br />DECLARE cur_DatababeNames CURSOR FOR<br />select name from sysdatabases<br />OPEN cur_DatababeNames<br />FETCH NEXT FROM cur_DatababeNames into @Dbname <br /><br />WHILE @@FETCH_STATUS = 1<br />BEGIN<br />select @S = databaseproperty (@Dbname,’IsSuspect’)<br /> if @S = 1<br /> begin<br />print @Dbname<br /> end<br />FETCH NEXT FROM cur_DatababeNames into @Dbname <br /><br />end<br />Close cur_DatababeNames <br /></i><br /><br />But this gave me only two databses. what will be the issue<br /><br /><br /><br />—————————————-<br /<a target="_blank" href=http://spaces.msn.com/members/dineshasanka>http://spaces.msn.com/members/dineshasanka</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
]]>