Error in sysdatabases - 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)

Error in sysdatabases

Author

Topic

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 04:50:44

DBCC CHECKDB('master') WITH NO_INFOMSGS

reports no errors

SELECT version from master.dbo.sysdatabases

lists various numbers, and then fails with

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

If I do:

SELECT name from master.dbo.sysdatabases ORDER BY name
SELECT name, version from master.dbo.sysdatabases ORDER BY name

I can see which one it appears to fail on (assuming that all output has been displayed up to that point).

The error was detected immediately after a server reboot this morning (which we do every Monday morning)

A database was renamed yesterday (not the one which I had to "fix" below)

Enterprise Manager lists no databases for the server - probably because its using

exec sp_MSdbuseraccess N'db', N'%'

which gives the same error.

I found an article

http://groups.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

which suggests forcing the database online with

ALTER DATABASE MyDatabase SET ONLINE

so I tried that and it worked OK.

I restored the backup from yesterday morning (to a Temp Database) and it shows:

version name
------- ----------
539 MyDatabase

clearly the [Version] was not bust at that time. After forcing it online I'm getting the same version number, so no idea what it got set to, or why.

Any ideas?

Thanks

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 05:29:07

Very strange indeed. Books Online confirms that STATUS column is INT.
http://msdn2.microsoft.com/en-us/library/aa260406(SQL.80).aspx

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 06:37:55

Well ... Enterprise Manager : Table : Design disagrees!

Definitely a SMALLINT in there (same on other servers here)

Formula = (convert(smallint,databaseproperty([name],'version')))

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 06:59:24

I checked the three servers within my reach and got following result

Status column in sysdatabases

8.00.818 Enterprise - INT
8.00.818 Standard - INT
8.00.2039 Enterprise - INT

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 07:42:43

Most of ours are 8.00.760 (SP3a I think), we have one on SP4 8.00.2039

They are all smallint (and all Enterprise version I think)

Hmmm ... I wonder why that is ??

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 07:50:36

I searched Google and I found a blog where I read something about difference between SQL Server 2000 upgrade [from 7.0] versus SQL Server 2000 clean install.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 08:45:52

Sounds promising but ... I've got a clean install done only a week or so back. That's smallint too. It was installed from very old release CDs though, and then patched to SP3a.

Unless my Enterprise Manager is lying about the datatype of that column ...

I wonder why yours are different

Kristen

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-05 : 09:59:40

Maybe you could get the DBCC master (member PaulRandal) to comment...possibly via his own site since his interaction here has diminished a little bit since his move from Microsoft?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 10:07:40

I thought was because of his marital bliss?
http://www.johnmarkphotography.com/blog/paul-randal-kimberly-tripp-wedding

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 10:32:10

[code]
DBCC master (member PaulRandal)
[/code]
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'PaulRandal'."

You'd think that would be a reserved word, eh?!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources