473,320 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Why is sp_helpdb and sysdatabases different

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.


Jul 23 '05 #1
2 7757
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.
Jul 23 '05 #2
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.

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Edwinah63 | last post by:
Hi Everyone, All the very best for 2004!! i need urgent help with this problem, the users are about to skin me alive!! we have an access front end with linked to sql server 2k tables. ...
2
by: Eyal Goren | last post by:
Hi, One of our customers claims that the sp_helpdb truncates database name larger than 24 characters, the release is 7.00.1094, did any one encounter such a problem ???
1
by: Neil | last post by:
Folks, I've got a problem at work, and I'm hoping that someone out there may have had something similar (although I doubt it!) or may be able to offer some advice. I'm in the process of...
1
by: [BuKoX] | last post by:
Hello. How to format field "create" (Jan 12 2005) in sp_helpdb procedure to sth like yyyy-mm-dd (2005-01-12) in SQL? bye... -- __ __ |__\\ | || |_// / \\ \_// ...
5
by: Hendrik Schober | last post by:
Hi, we just run into the problem, that "default" alignment in the project properies dialog seem to be different. We have a project that's a DLL, which is linked with a couple of LIBs. All are...
27
by: Ben Finney | last post by:
Antoon Pardon wrote: > I just downloaded your enum module for python > and played a bit with it. IMO some of the behaviour makes it less > usefull. Feedback is appreciated. I'm hoping to...
4
by: cantatahost | last post by:
Hello, Likely this has been asked before... We have a library (in DLL form) that we distribute. The interface to the library is all C, but within the library it uses C++ in many places. ...
2
by: moonrox | last post by:
Thanks for you help in advance. I am trying to use sp_helpdb to get the size of all the databases on a sql server. The results of sp_helpdb (when using the @dbname='databasename') is 2 sets of...
5
by: ph3ng | last post by:
Hi I was executing sp_help on a server (ran against master database) and i get the following message: Associated statement is not prepared(HY007) Anyone got any idea what is wrong?
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.