sysdatabases status vs databasepropertyex

Discussion:

(too old to reply)

Mark Deuser

2005-07-20 15:15:20 UTC

Hi all,

In testing some logic to test for suspect databases, I noticed the following
anomaly.

To attempt to create a suspect database condition I:
1. Stopped the SQL Server
2. Renamed mytestdb.mdf to mytestdb.sav
3. Started the SQL Server

The Enterprise Manager greys out the mytestdb database and appends
"(Suspect)" to its name under the Databases folder. Both SQL 7.0 and 2K EM
work in the same way.

However, when I issue the following command (SQL 7.0 and 2K):
SELECT name, status FROM sysdatabases
the status for mytestdb is 1073741840 which according to the online books
means:
1073741840 = 0x40000010
cleanly shutdown (bit 1073741824 - 0x40000000)
torn page detection (bit 16 - 0x10)

I was assuming that the suspect bit (aka "not recovered") bit 0x100 (256)
would have been set. Prior to renaming the database's .mdf file, it's status
was 16 (0x10), so it appears that the renaming the database file caused the
cleanly shutdown bit to be set instead of the suspect bit.

Issuing the command (SQL 2K only):
SELECT DATABASEPROPERTYEX('mytestdb', 'Status')
results in "SUSPECT" being returned. This is what I expected.

Issuing the command (SQL 7.0 and 2K):
SELECT DATABASEPROPERTY(mytestdb', 'IsSuspect')
returns a 0. I was expecting this to return a 1 in this situation.

While DATABASEPROPERTYEX seems to do the trick, it's not available on SLQ
7.0 which I also need to support.

Is the above discrepancy working as designed? If so, what's the best
programmatic way to determine a database is suspect?

Thanks!
Mark

2005-07-20 17:59:20 UTC

Permalink

Using SQL Profiler, you can monitor what commands and queries Enterprise
Manager is using to populate it's GUI.

Post by Mark Deuser
Hi all,
In testing some logic to test for suspect databases, I noticed the following
anomaly.
1. Stopped the SQL Server
2. Renamed mytestdb.mdf to mytestdb.sav
3. Started the SQL Server
The Enterprise Manager greys out the mytestdb database and appends
"(Suspect)" to its name under the Databases folder. Both SQL 7.0 and 2K EM
work in the same way.
SELECT name, status FROM sysdatabases
the status for mytestdb is 1073741840 which according to the online books
1073741840 = 0x40000010
cleanly shutdown (bit 1073741824 - 0x40000000)
torn page detection (bit 16 - 0x10)
I was assuming that the suspect bit (aka "not recovered") bit 0x100 (256)
would have been set. Prior to renaming the database's .mdf file, it's status
was 16 (0x10), so it appears that the renaming the database file caused the
cleanly shutdown bit to be set instead of the suspect bit.
SELECT DATABASEPROPERTYEX('mytestdb', 'Status')
results in "SUSPECT" being returned. This is what I expected.
SELECT DATABASEPROPERTY(mytestdb', 'IsSuspect')
returns a 0. I was expecting this to return a 1 in this situation.
While DATABASEPROPERTYEX seems to do the trick, it's not available on SLQ
7.0 which I also need to support.
Is the above discrepancy working as designed? If so, what's the best
programmatic way to determine a database is suspect?
Thanks!
Mark

Mark Deuser

2005-07-21 14:08:06 UTC

Permalink

Thanks for the tip, JT. Here's the SQL that the Enterprise Manager uses:

SQL 7.0 EM:
select name,
DATABASEPROPERTY(name, N'IsDetached'),
DATABASEPROPERTY(name, N'IsShutdown'),
DATABASEPROPERTY(name, N'IsSuspect'),
DATABASEPROPERTY(name, N'IsOffline'),
DATABASEPROPERTY(name, N'IsInLoad'),
DATABASEPROPERTY(name, N'IsInRecovery'),
DATABASEPROPERTY(name, N'IsNotRecovered'),
DATABASEPROPERTY(name, N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'),
status,
category,
status2 from master..sysdatabases

SQL 2K EM:
SELECT name,
DATABASEPROPERTY(name, N'IsDetached'),
(case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else
DATABASEPROPERTY(name, N'IsShutdown') end),
DATABASEPROPERTY(name, N'IsSuspect'),
DATABASEPROPERTY(name, N'IsOffline'),
DATABASEPROPERTY(name, N'IsInLoad'),
(case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1
else DATABASEPROPERTY(name, N'IsInRecovery') end),
(case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1
else DATABASEPROPERTY(name, N'IsNotRecovered') end),
DATABASEPROPERTY(name, N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'),
has_dbaccess=has_dbaccess(name),
status=status,
category=category,
status2=status2 from master.dbo.sysdatabases

which resulted in (SQL 7.0)
mytestdb 0 1 0 0 0 0 0 0 0 1073741824 0
1090519040

and (SQL 2K)
mytestdb 0 1 0 0 0 0 0 0 0 0 1073741840 0
1090519040

Looks like the SQL 7.0 and SQL 2K EM treats a "IsShutdown" DATABASEPROPERTY
value of '1' as the database being suspect.

Hmmm.. Working as designed/intended or a EM bug?

Post by JT
Using SQL Profiler, you can monitor what commands and queries Enterprise
Manager is using to populate it's GUI.