Sysaltfiles And Sysfiles Inconsistant
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
ADVERTISEMENT
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
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
Apr 7, 2008
select * from sysdatabases
give the information for all DB's, where as
select * from sysfiles
gives the information only for current DB.
Is there a system table which contain the information for all the files on a server? Or Do I need to use SP_MSFOREACHDB?
------------------------
I think, therefore I am - Rene Descartes
View 2 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
Mar 5, 2004
I have a DTS package that runs once a week. There is a xp_sendmail SQL task at the end that sends emails to our field sales people with a file attachment based on the results of the DTS package. Simple enough.
The problem I have is that the sendmail chokes when the mail task is executed at the end of the package - sometimes. And when it does fail, the DTS step with the sendmail can be executed separately without a problem.
The error message the gets generated is:
MAPI_E_NETWORK_ERROR80040115-2147221227
This does not make sense. If there was a network problem I should still get a message when executing the step that failed 30 seconds prior...not the case.
The other odd thing is that this package has been running fine for about 3 mo with no troubles, but our mail server may have undergone some changes that I am not aware of so...anyway if anyone has any info or has come across this oddness please feel free to share with me what you know.
I am a t a loss here with this one.
Thanks in advance to anyone else with a dented skull that has an answer!
RLM
View 1 Replies
View Related
Jul 12, 2002
I have 50 SQL2k MSDE servers with 250 databases.
Need to know the 250 databases size on daily bases.
It looks dbsize is not in server.master.sysdatabases,
but in individual sysfiles.
Any idea about how to retrieve the dbsize in a better way?
-David
View 1 Replies
View Related
Feb 21, 2008
Hi,
I am supposed to use 250 MB of space in SQL Server 2005 database
I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.
When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?
I thought it might be log files..but my hosting provider tells me that db is set to Simple Recovery which does not utilize a Log File. So we cannot shrink it.
So i fired the command
'select * from sysfiles'
Results:
11251602880012820ASPNETDB_DAT_DATD:Program Files....dncASPNETDB.MDF
206312800128660ASPNETDB.MDF_logD:Program Files....dncASPNETDB_log.ldf
3025612800110486420dnc_log2D:Program Files....dncdnc_log2.ldf
What can I do now?thanks.
View 9 Replies
View Related
Jul 17, 2007
First; thanks for looking.
On SQL Server 2000. IBM X series, dual 2GHz Xeon processors, 3GB RAM, RAID 5 DB array and separate Mirrored drive for OS, Windows 2000 Server.
Note: I should have moved the log files to the mirrored volume - I only just noticed it wasn't like that ... oops.:o It only has the OS and backup files (different logical volumes).
Problem: I have a Bill of Material system. It has a batch (Stored Procedure) that explodes Order Line Items. Each line has about 40 components.
The SP has 5 major steps to explode the Assembly, figure out colors/sizes to get the actual part numbers, assign quantities, update flags on the line item table (that has 3,500,000 rows but is not a clustered index - fill factor 85% on the unique autonumber index I use for the updates). The SP is jam packed with User Function calls, sub-Stored Procedures, and (yes) some cursors (on small declared temp tables created for each Order Line processed).
The execution plan for the whole batch of 100 is humongous - like 1000 pages, however nearly all steps are about 16 - 40 milliseconds to execute.
It normally takes 13 seconds to process 100 line items (3900 assembly rows created). However; about every 20th run, it takes 2 or 3 minutes ON THE VERY SAME LINE ITEMS. No programming changes! Run back-to-back, or with a minute or two delay. Then running it again, back to 13 seconds. Sometimes it takes 25 or so seconds, but that oddball 3 minutes is a killer.
I put some timing INSERTs into a temporary log file to localize the problem.
So; here's the REALLY weird thing. The part that experiences the intermittent 2 minute delay is a VERY SIMPLE call to a SP that INSERTs to the tbBATCHs table and gets the identity and passes it back with an Output variable. The tbBATCHs table only has about 8 columns. Nobody else uses it so it's not locking. It has an "If Exists" selection following the Insert to see if another batch is in progress not yet flagged as complete, but's not the issue because it's never true (hard coded values for testing forces this).
I have a Standby Server (duplicate config of main server) so I ran it there. Same issue.
If I loop 100 times on the tbBATCHs INSERT SP, it is pretty quick (like 2 seconds for all 100).
It's never on the first run, so I think it may be some sort of Log File catch-up.
Also; I DO NOT have Transaction Logging in place yet.
Ideas?
View 5 Replies
View Related
Apr 4, 2008
I have databse called RM-A1 and a log of RM-A1.log
When i run this statement it errors.
select ceiling((size * 8192.0)/(1024.0 * 1024.0)),
case when status & 0x100000 = 0 then
convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0)))
else convert (varchar, growth)
end + char(10)+char(13)
from RM-A1_log.dbo.sysfiles where fileid = '1'
--This works
select * from dbo.sysfiles
Im using a dynamic statement
set @cmd = N'select @ceil = ceiling((size * 8192.0)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
''MB '' + convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) '
+ 'else ''PER '' + convert (varchar, growth) '
+ 'end' + char(10)+char(13)
+ 'from ' + @l_db_name + '.dbo.sysfiles where fileid = ''1'''
exec sp_executesql @cmd, @retType, @cnt OUTPUT, @ceil OUTPUT
I cannot determine why log name of '-' are erroring all other databases are working.
View 17 Replies
View Related
Apr 22, 2008
Could someone explain how to convert this size to gigabytes? I look at it, and I can't figure out what size it is in. Maybe bits?
EXEC sp_MSForEachDB 'SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,
''?'' as DatabaseName,[?]..sysfiles.size, [?]..sysfiles.status, [?]..sysfiles.name, [?]..sysfiles.filename,convert(sysname,DatabasePropertyEx(''?'',''Status'')) as Status,
convert(sysname,DatabasePropertyEx(''?'',''Updateability'')) as Updateability,
convert(sysname,DatabasePropertyEx(''?'',''UserAccess'')) as User_Access,
convert(sysname,DatabasePropertyEx(''?'',''Recovery'')) as Recovery From [?]..sysfiles '
Any help is appreciated.
-Kyle
View 5 Replies
View Related
Feb 28, 2000
Hi,
I currently think Im going mad!!
I have a large table (2+Million records, 2000+ new per day). If I want to count the records in one month, I could do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01'
and '1999-12-31'
or
Select Count(*) from Table1 where Added_Date >= '1999-12-01' and
Added_Date <= '1999-12-31'
For these two above, I get the same result.
HOWEVER!!
If I then do:-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00'
and '1999-12-31 23:59:59'
or use the >= and <= operators, the result is larger.
If I then do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00.000' and '1999-12-31 23:59:59.999'
or use the >= and <= operators, the result is larger still but i believe this including more rows than there actually is?.
I can get over the problem programatically using Convert to truncate to a day, month or year however, I don't understand why the results are different.
Any Ideas as to why this is happening, A'm I being stupid!!!!!!!
(PS Added_Date has a non_clustered index which has been rebuilt twice and DBCC finds no errors)
Many thanks,
Peter Farley
View 1 Replies
View Related
Jul 20, 2005
I have a table that seems to have a bad index. When I do the followingquery I get inconsistant and needless to say incorrect results.select count(*) from mytable where mycolumn = 1If I remove the index from "mycolumn" the query works correctly. If Iadd the index back (even with a new name etc...) it doesn't workright.Has anyone ran into this? or does anyone know how I can fix thisproblem?It seems that removing the index is not really removing everythingbecause when I add a new one I get this same problem... btw, this isisolated to this column on this table. all other indexes within thedatabase are fine.Any help would be appreciated.Thanks,dharper
View 1 Replies
View Related
Jan 21, 2008
The documentation for sp_trace_generateevent states that the following arguments are available:
Arguments
[ @eventid=] event_id
Is the ID of the event to turn on. event_id is int, with no default. The ID must be one of the event numbers from 82 through 91, which represent user-defined events as set with sp_trace_setevent.
[ @userinfo= ] 'user_info'
Is the optional user-defined string identifying the reason for the event. user_info is nvarchar(128), with a default of NULL.
[ @userdata= ] user_data
Is the optional user-specified data for the event. user_data is varbinary(8000), with a default of NULL.
@userinfo is stored in the textdata column, which after inspecting sys.trace_columns is of type text with a max size of 1073741824.
@userdata is stored in the binarydata column whose type is image with a max size of 1073741824.
I am attempting to build a stack trace of sorts that will capture detailed information about the run-time code executed when an error (contained within a try/catch) occurred.
The sql returned from dm_exec_sql_text goes well beyond 8000 and is getting truncated.
I haven't found any hot-fixes for SQL Server 2005 that addresses this issue. Has anyone else bumped into this limitation or found any explanation on the inconsistency between the stored procedure€™s arguments and the column types that the values are stored in?
T-SQL sp_generate_traceevent
http://msdn2.microsoft.com/en-us/library/ms177548.aspx
Trace Columns
select * from sys.trace_columns
View 1 Replies
View Related
Apr 30, 2015
Whenever I'm running query against the below database alone, it throws me the error.
use [AdventureWorks_2005]
SELECT * FROM dbo.sysFILES
OR
SELECT * FROM [AdventureWorks_2005].[sys].[SYSFILES]
Error- >
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.sysFILES'
View 3 Replies
View Related
