multiple tempdb references in master sysaltfiles

Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
....
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
Rusty
I've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!
|||Hi
"rnbwil@.gmail.com" wrote:

> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John
|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.
|||Hi
"rnbwil@.gmail.com" wrote:

> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John
|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.