tempdb referenced 2 times in sysaltfiles
-
Hi,We tried to create a new database on an application server (Win 2003
server/SQL Server 2000) 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 databaseproperties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfileshas 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 pointsthe log and data files to the g: drive with DBID = 15 and one pair of rows points
them to the f: drive with DBID = 2. 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 movetempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: totry 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 turnedup (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 theserver it got moved to g: then to f:.
I've been able to verify that sysdatabases reference to tempdb pointsat 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 incorrectrows out of sysaltfiles and restart.
But the thought gives me the screaming heebie-jeebies!HELP!!! -
Here's the exact text of the error.
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 10.00 MB on disk 'Test123_dat'.
The CREATE DATABASE process is allocating 5.00 MB on disk 'Test123_log'.
Device activation error. The physical file name 'g:\Sqldata\templog.ldf' may be incorrect.
Nothing definitive is turning up in the current log.
-
-
Thanks for the reply.
I saw that article but it doesn't seem to apply. The new database isn't even created so can't be offline and tempdb is functioning normally.
The refererence in sysaltfiles to a non-existent tempdb seems to be what's precluding the creation of the new database.
I'm just not sure of a safe way to fix it.
-
I did run an alter database on tempdb (per the MS KB on moving tempdb). I basically just gave it the current, correct file locations hoping it might strip any incorrect rows. The attempt didn't return any errors but didn't do anything to the incorrect rows.
-
Forgive me if this is obvious, but have you tried shutting down SQL server? TempDB is recreated each time the service restarts.
If it were me and I could afford some downtime, I would try shutting down SQL server and then running a file search on all instances of tempdb mdf and ldf files. Delete (or more safely rename) all of these files then start SQL Server back up. This should recreate the TempDB files and get rid of the bogus ones in the system tables. I'd also be tempted to check for disk/file corruption while I had the system just to be safe.
My hovercraft is full of eels.
-
Not since we discovered the current issue, but it has been rebooted several times since the tempdb was moved from g: to f: and the stray rows in sysaltfiles seem to remain.
On a side note, this is from our OpenVMS host generating a canned system warning message...
IDX1> exit %xb70
%SYSTEM-W-FISH, my hovercraft is full of eels
IDX1>
-
The only way to remove those stray rows is to edit the system table itself. If you are comfortable with SQL and mucking witth the system tables it is a breeze and will take a few minutes at most. Let me know if you need a hand with this.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
We're comfortable with how to delete the rows but I was worried about creating more problems by creating broken links. I didn't see any other references in any of the master tables that pointed at the bad rows so I was guessing it would be okay to remove them but, well, I'm paranoid.
Will sql need to be bounced or have a "reconfigure" run after the rows are removed?
-
There should not be any issues nire a restart. However, backup the master database first !!!
Here is the general outline you'll need:
exec sp-configure 'allow updates',1
reconfigure with override
begin tran
select what you want to delete
do your deletes
select again just to make sure
commit tran - only after you are absolutely sure
exec sp-configure 'allow updates',0
reconfigure with override
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Many thanks for the input Rudy!
We'll give it a try. Keep your fingers crossed!
-
no problemo senore ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply