Sysaltfiles Wierdness
May 31, 2007
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:m
ssqldata emplog.ldf may be incorrect.
The interesting thing 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).
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 FilesMicrosoft SQL ServerMSSQLdata empdb.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
View 3 Replies
Apr 11, 2007
I attempted to move some tasks into a new sequence container. It failed saying element cannot be found in a collection. Now I find that visually the tasks do not appear in the container. But in some ways they are. I can not try to move the tasks to the container, or I get the same "element can not be found". If I execute the container, the tasks outside the container run.
I thought to CUT and PASTE my tasks, but then it says "The package contains one or more event handlers for this task. Deleting the task will also delete the event handlers and the event handler context."
View 4 Replies
View Related
Sep 14, 2007
I'm using the Merge transformation to merge two sorted tables on four sort fields.
It all looks OK until several records share some of the same sort fields.
For example if table 1 contains
Zip Code(sort field 1) Cust Name(sf2) Cust Number(sf3) Record type(sf4)
1234 Bob 444 A
1234 Bob 555 A
and table 2 contains
Zip Code Cust Name Cust Number Record type
1234 Bob 444 B
1234 Bob 555 B
then the output order is
Zip Code Cust Name Cust Number Record type
1234 Bob 444 A
1234 Bob 555 A
1234 Bob 444 B
1234 Bob 555 B
and I would have expected
Zip Code Cust Name Cust Number Record type
1234 Bob 444 A
1234 Bob 444 B
1234 Bob 555 A
1234 Bob 555 B
Both input table are explicitly sorted just prior to the merge.
Any ideas?
View 6 Replies
View Related
Jul 28, 2004
I'm taking the Administration of a DB wich it has on system table sysaltfiles some leftoff files that are not being used anymore on TempDB,
how can i remove them ? Every time i restart the SQL Service it tries to open those files on sysaltfiles..
I tried ALTER DATABASE tempdb remove file XXXX , it did not work...
I got this error:
ALTER DATABASE failed. Some disk names listed in the statement were not found. Check that the names exist and are spelled correctly before rerunning the statement.
View 1 Replies
View Related
Aug 2, 2007
I succesfully did a MSSQL "file" restore of production to a different node yesterday. But failed to apply any transaction logs, it complained that one of the files have not been restored. On further investigation I found that one of the files are missing in sysfiles, but the file is in sysaltfiles.
This SQL statement does not return the same number files.
SELECT * FROM <DB>..sysfilesSELECT * FROM master..sysaltfiles WHERE dbid= DB_ID('<DB>')
sp_helpdb '<DB>' gives the same result as sysfiles.
Any idea on how to fix this ?
Thanks
Tiaan
View 7 Replies
View Related
Jan 18, 2007
Try a little experiment. Partly to humor me, and make me believe I am not quite insane.
Step 1: Install SQL server 2000, such that the data files are not in the default location, but in a location with a shorter path (i.e. install the data files to E:MSSQL8).
Step 2: Run the following queries, and comment on any oddities:
select filename
from master..sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename)), filename
from sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename))
from sysaltfiles
where dbid = 2
I am guessing that #2 is some sort of odd effect caused by the fixed length data field, but I just want to make sure that other people get this oddity, and not just me. I have no idea what could be causing the third output...or perhaps the lack of it.
View 2 Replies
View Related
Jun 29, 2007
HI All,I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.1.)is there any systable still to be altered???2.) i have even tried alter database, doesnt work.3.)The master files have been taken from server1 (where tempdb points to d:data) to server2 ( where i need to point tempdb to point E:mssqldata). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path., too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path???
View 5 Replies
View Related
