Database Suspect - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Suspect

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-05-25 : 07:46:49

Can anybody help me in writing a script to detect suspect database, as I don't have any monitoring tools in my shop for monitoring. ?

thanks in advance.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Kristen
Test

22859 Posts

Posted - 2004-05-25 : 08:37:33

[code]
SELECT name
FROM master.dbo.sysdatabases
WHERE AND (status & (32 + 64 + 128 + 256)) <> 0
[/code]
I'm not sure what values you actually need - the "(32 + 64 + 128 + 256)" might only need to be 256 - the others refer to databases which are in the process of coming online, or being restored [I think!], but BOL does NOT call 256 "Suspect" (I think it says "Not recovered" or somesuch), but 256 is the value I've found in system SProcs that check for "Suspect".

The others may be handy if you want to check for "something awful may be happening!"

Kristen

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-05-26 : 10:49:32

Thanks Kirsten for the advice. I tried few solutions per your input and cdn't find the best way out. Any other suggestions, or anybody out there who can help me in this!

Thanks a bunch.!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Kristen
Test

22859 Posts

Posted - 2004-05-26 : 10:56:51

Is your SUSPECT database not showing up in the resultset then? or some other problem?

Kristen

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-05-26 : 10:58:14

Yes, I don't see any status of the suspect database as part of resultset.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Kristen
Test

22859 Posts

Posted - 2004-05-26 : 11:22:37

Yup, you are right. Checking a SUSPECT database here the only STATUS flag set is 1073741824 - which BOL says is "cleanly shutdown", so you could try


SELECT	name
FROM	master.dbo.sysdatabases
WHERE	(status & (32 + 64 + 128 + 256 + 1073741824)) <> 0
ORDER BY name

which seems to work OK here.

To get a list of all BITs used (in case there are some others applying to your circumstances) you could try:


SELECT	status, 
	(status & 1), 
	(status & 2), 
	(status & 4), 
	(status & 16), 
	(status & 64), 
	(status & 128), 
	(status & 256), 
	(status & 512), 
	(status & 1024), 
	(status & 2048), 
	(status & 4096), 
	(status & 8192), 
	(status & 16384), 
	(status & 32768), 
	(status & 65536), 
	(status & 131072), 
	(status & 262144), 
	(status & 524288), 
	(status & 1048576), 
	(status & 2097152), 
	(status & 4194304), 
	(status & 8388608), 
	(status & 16777216), 
	(status & 33554432), 
	(status & 67108864), 
	(status & 134217728), 
	(status & 268435456), 
	(status & 536870912), 
	(status & 1073741824),
	name
FROM	master.dbo.sysdatabases
ORDER BY name

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources