Why is sp_helpdb and sysdatabases different

[Akinja]

I have a strange question and hope someone can shed some light on the
problem.

First let me give you my scenario. We are using doubletake to replicate our
database server files between the local site and off site. I will name
these servers srv1a (local) and srv1b (remote). It now time to move to new
servers. These servers will be srv2a (local) and 2b (remote).

Currently srv1a replicates to srv1b. The sqlserver service is off on srv1b.
It is only replicating the database files. Works great.

Now the problem. srv1a and srv1b have all the logs and data files on the
d:\ drive. On the new servers (2a and 2b) we will place the log files on
e:\ and the data files on f:\. This is the case for all databases except
the system databases, which will reside all ldf and mdf on f:\.

In order to move to the new servers, as a test we began replicating to srv1a
to svr2a. It placed all the system dbs on srv2a d:\ drive and all other
files in the new structure (e:\ and f:\). There is some work that you must
do to move the master, msdb, model, and temp to the new location (change the
startup parameters and detaching and reattaching some other databases). All
this works great and we have everything up and running.

As part of a sanity check, I ran the following statements

select name, filename from master..sysdatabases where name = 'master'

GO

exec sp_helpdb master.

GO

results are

name filename
master d:\Microsoft SQL
Server\MSSQL\data\master.mdf
(1 row(s) affected)

name db_size owner dbid created
master 22.00 MB sa 1 Aug 6 2000 Status=ONLINE,
name fileid filename
filegroup size maxsize growth usage
master 1 F:\Microsoft SQL Server Data\MSSQL\Data\master.mdf
Primary 19072 KB Unlimited 10% data only
mastlog 2 F:\Microsoft SQL Server Data\MSSQL\Data\mastlog.ldf
NULL 3456 KB Unlimited 10% log only

If you notice that sysdatabases table is showing the master files on D:\ but
sp_help is showing on F:\ F: is the correct location. Also my start
parameters is
-dF:\Microsoft SQL Server Data\MSSQL\Data\master.mdf
-lF:\Microsoft SQL Server Data\MSSQL\Data\mastlog.ldf
-eF:\Microsoft SQL Server Data\MSSQL\Data\ERRORLOG

Any ideas why the two are not showing the same thing? Everything is working
fine and all other databases (sp_helpdb) is correct and sp_helpfile master
is showing the same thing as sp_helpdb master. Since I though sp_helpdb was
querying sysdatabases, I am confused as to why this is happening.

Any help would be greatly appreciated. Sorry for the long post.

[Ross Presser]

On Fri, 01 Jul 2005 18:40:28 GMT, Akinja wrote:

Any ideas why the two are not showing the same thing? Everything is working
fine and all other databases (sp_helpdb) is correct and sp_helpfile master
is showing the same thing as sp_helpdb master. Since I though sp_helpdb was
querying sysdatabases, I am confused as to why this is happening.

Any help would be greatly appreciated. Sorry for the long post.
Using Query analyzer, I scripted master.dbo.sp_helpdb to inspect it. In
fact it queries sysdatabases for the first resultset - the one that lists
name,db_size,owner,dbid,created,status,compatibili ty_level . But for the
second resultset, that shows the file allocation, it is in fact invoking
sp_helpfile. So the filename column in sysdatabases isn't referenced by
sp_helpdb at all.

sp_helpfile doesn't look at sysdatabases at all, it looks at sysfiles.

I am guessing that what you describe as some work that you must do to move the master, msdb, model, and temp to
the new location (change the startup parameters and detaching and
reattaching some other databases)

does not in fact change the column in sysdatabases, and that that column is
not really used in system startup.

[Akinja]

That sounds logical. However, this seems a little strange that I would list
two different locations within various system tables. I am hoping this is
not a receipt for a problem later on. Make you want to say 'Uhmmm'

What I meant by moving the system databases was that on the new servers, D:\
was too small for all the databases. So I needed to place the system dbs
there and bring up the server. The databases will come up Suspect. In
order to move Master, I needed to change the startup parameters to reflect
the new location. In order to change model and msdb (in that order) I
needed to add a trace flag 3608 to the startup parameters then detach, move,
and reattach the database. Temp just required an Alter statement.

Appreciate the feedback (a real puzzle)

Akinja

"Ross Presser" <rp******@NOSPAMgmail.com.invalid> wrote in message
news:3l*****************************@40tude.net...

On Fri, 01 Jul 2005 18:40:28 GMT, Akinja wrote:

Any ideas why the two are not showing the same thing? Everything is
working
fine and all other databases (sp_helpdb) is correct and sp_helpfile
master
is showing the same thing as sp_helpdb master. Since I though sp_helpdb
was
querying sysdatabases, I am confused as to why this is happening.

Any help would be greatly appreciated. Sorry for the long post.

Using Query analyzer, I scripted master.dbo.sp_helpdb to inspect it. In
fact it queries sysdatabases for the first resultset - the one that lists
name,db_size,owner,dbid,created,status,compatibili ty_level . But for the
second resultset, that shows the file allocation, it is in fact invoking
sp_helpfile. So the filename column in sysdatabases isn't referenced by
sp_helpdb at all.

sp_helpfile doesn't look at sysdatabases at all, it looks at sysfiles.

I am guessing that what you describe as

some work that you must do to move the master, msdb, model, and temp to
the new location (change the startup parameters and detaching and
reattaching some other databases)

does not in fact change the column in sysdatabases, and that that column
is
not really used in system startup.