All my databases are missing

[Trev@Work]

In EM that is, in QA if I use:

use master
select * from sysdatabases

I get:
(6 row(s) affected)

Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 42840.

[Erland Sommarskog]

Re: All my databases are missing

Trev@Work (no.email@pleas e) writes:[color=blue]
> In EM that is, in QA if I use:
>
> use master
> select * from sysdatabases
>
> I get:
> (6 row(s) affected)
>
> Server: Msg 220, Level 16, State 1, Line 1
> Arithmetic overflow error for data type smallint, value = 42840.[/color]

Oops! I assume that this is the message that you get in Enterprise
Manager?

If you don't have SP3 installed, try to install it. The bug may have been
fixed.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Trev@Work]

Re: All my databases are missing

Erland Sommarskog wrote:[color=blue]
> Trev@Work (no.email@pleas e) writes:
>[color=green]
>>In EM that is, in QA if I use:
>>
>>use master
>>select * from sysdatabases
>>
>>I get:
>>(6 row(s) affected)
>>
>>Server: Msg 220, Level 16, State 1, Line 1
>>Arithmetic overflow error for data type smallint, value = 42840.[/color]
>
>
> Oops! I assume that this is the message that you get in Enterprise
> Manager?
>
> If you don't have SP3 installed, try to install it. The bug may have been
> fixed.[/color]

No EM said nothing, just didn't list anything. There was nothing under
management either and the backups hadn't run.

I restarted the service and the databases re-appeared. There are some
that I had taken offline, these are now marked as offline/suspect.

SP3a is already installed. I wonder if there's a bug with taking
databases offline?

If I query sysdatabases in QA, it was OK until I included the version
column. The offline dbs had quite high numbers here, now showing 0 in
that column, most are showing 539. I don't know the significance of this
number.

[Erland Sommarskog]

Re: All my databases are missing

Trev@Work (no.email@pleas e) writes:[color=blue]
> If I query sysdatabases in QA, it was OK until I included the version
> column. The offline dbs had quite high numbers here, now showing 0 in
> that column, most are showing 539. I don't know the significance of this
> number.[/color]

That's a version number for the database format. Anything but 539 sounds
highly suspcious.

What happens if you try to bring these databases online?

If these databases are production data, and you don't have a clean backup,
I think you need to open a case with Microsoft. Something appears to be
broken.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Trev@Work]

Re: All my databases are missing

Erland Sommarskog wrote:[color=blue]
> Trev@Work (no.email@pleas e) writes:
>[color=green]
>>If I query sysdatabases in QA, it was OK until I included the version
>>column. The offline dbs had quite high numbers here, now showing 0 in
>>that column, most are showing 539. I don't know the significance of this
>>number.[/color]
>
>
> That's a version number for the database format. Anything but 539 sounds
> highly suspcious.
>
> What happens if you try to bring these databases online?
>
> If these databases are production data, and you don't have a clean backup,
> I think you need to open a case with Microsoft. Something appears to be
> broken.[/color]

Hi Erland, thanks for responding.

I brought all the databases online, all now show 539 for the version
except one called "WebCat", this was never taken offline as it's used on
a daily basis, this one shows null :-\.

All databases are backed up daily on a schedule, the webcat one doesn't
matter if it loses data as it's re-created every night anyway (it's just
a catalogue of files on a particular web server). I might just drop that
database and re-create it.

[Trev@Work]

Re: All my databases are missing

Stranger still,

Taking a database offline now sets version to null, I can't however take
"WebCat" offline as it says it's in use (it isn't according to process
info), this is the one where version is already null.

[Erland Sommarskog]

Re: All my databases are missing

Trev@Work (no.email@pleas e) writes:[color=blue]
> Taking a database offline now sets version to null, I can't however take
> "WebCat" offline as it says it's in use (it isn't according to process
> info), this is the one where version is already null.[/color]

I have no idea what is going on with WebCat. I guess the reason that you
see NULL for the offline databases, is because this number is derived by
actually querying the database file itself, so if the database is offline,
the number cannot gotten hold off.

I checked a little further and found that version is in fact a
computed column:

version AS (convert(smalli nt,databaseprop erty(name,'vers ion')))

At least here we see the source for the conversion error you had.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[mimorr]

Re: All my databases are missing

I have had this issue several times, and discovered a post from Dan
Carollo in 2002
that led me to the fix.

http://groups-beta.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/3612fc6d46548635/c728290bf2776908?q=corrupted+version+column+in+master&_done=%2Fgroups%3Fq%3Dcorrupted+version+column+in+master%26hl%3Den%26&_doneTitle=Back+to+Search&&d#c728290bf2776908

Perhaps the version for WebCat got corrupted and if you run the command
in QA to bring it back online, it might fix that corruption.

alter database WebCat
set online

That is what I was able to do and Enterprise Manager works again. In
the past, I have just had to reinstall SQL replacing the Master
database which takes forever!

Michelle