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.
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:.
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.
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.
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???
I have a SQL Server 2000 database with a primary data file (MDF) and a secondary data file (NDF). I would like to remove the secondary file and only have the MDF. Is this possible?
Error:- (1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note: =>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error... => I executed above commands and restarted services...no change...
I'm attempting to alter a database by removing one of two log files. I have truncated both log files successfully and the database has no processes but I get an error stating file cannot be removed because it is not empty. Help
In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. Â I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.
I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.
I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.
We are using log shipping and we would like to remove all transfered and applied journal (in the primary box). We have the intentionto use a trigger like this :
CREATE TRIGGER del_log ON log_shipping_plan_history AFTER INSERT as declare @lastfile nvarchar(256) SELECT @lastfile=i.last_file FROM log_shipping_plan_history e INNER JOIN inserted i ON e.sequence_id = i.sequence_id where i.activity=1 begin if IF (@lastfile <> NULL) ... ... remove file (using xp_cmd for example...) ... end
but the problem is that we have only the last file transfered and applied that will be removed (some time, more that 1 file are applied in one shot ... see num_files column in log_shipping_plan_history).
Any solution to remove all the files generated before the last one given by the query ? Any other solutions (sql wizard gives the possiblity to to remove file after a laps of time 1hour, 1day...).
I am looking for the table that contains all the journal files (that we can see when we try to retore a db) ?
We are running SQL server 2003 with SP3. I'm trying to shrink a data files with the emptyfile option so I can eventually remove the file using the alter database command. However, I get the following error message when I run the alter database command:
Error: the file PRADATA4 cannot be removed because it is not empty.
The file that I'm trying to remove still has 62 extents on it. I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3. commands that I'm running are as follows:
1) USE PRA DBCC TRACEON(8901) DBCC SHRINKFILE ('PRADATA4', EMPTYFILE) DBCC TRACEOFF(8901)
2) USE PRA GO ALTERDATABASE PRA REMOVE FILE PRADATA4 GO
I have a CSV file that I am importing via SSIS into a SQL table.On the Flat File connector, I have specified Line Feed as the row delimiter.The data flow is failing due to some of the rows having line feeds before the end of the row.Is there a way to get rid of some line feeds but not others, so that I can run the data flow successfully.
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.
I am having a hard time removing my SQL instance inside the Add/Remove program. After i select the SQL Instance name and then I tried to remove it but it won't allow me to delete it. There isn't any error message or whatsoever. Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box. Is there any way to remove the Sql Instance in my system?
Hi, for some AP issue, the file I upload must be without the line feed/carriage return in the last line. for example:
original fixed-length file (exported from SSIS) line NO DATA 1 AA123456 50 60 2 BB123456 30 40 3 CC123456 80 90 4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line. line NO DATA 1 AA123456 50 60 2 BB123456 30 40 3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Hi all In my table one of the column consists the values like this Krishna" Rama" - - - - - I want to remove ". I started with instr to findout the occurence. Its giving the message that 'InStr' is not a recognized built-in function name.. How to solve it. Waiting for valuable replies Thank u Baba
Please help to remove (-) The EM_TERMINATION_DATE column have records with no data. I am using the following below script to format the dates. The problem is : the format with (-) between yyyy-mm-dd retrive NULL records with (-) see the output. How to fix it? Here is my ouput: EM_TERMINATION_DATE 2006-03-15 - - - - - - - -
Here is my script:
select
cast(left(EM_TERMINATION_DATE, 4) as varchar(4))
+ '-' +
Cast(right(EM_TERMINATION_DATE, 4) as varchar(2))
+ '-' +
Cast(left(Right(EM_TERMINATION_DATE,2), 2) as varchar(2))as TERM_DT
Hi, I don't need sql express, never use it and I would like to remove it. Is it possible without loosing important functionalities in VWD? Is that true that cannot compile the applications (ctrl+f5) anymore?
I had sqlcachedependancy installed. But now I cannot uninstall it? 1. I unregistered: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727>aspnet_regsql.exe -E -S localhost-d DNN1 -dd 2. I remove the Dependencry.Start() from my application_start ' System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString) 3. I removed the cache section from my web.config 4. I removed the outputcache statements from my aspx pages. <!--<%@ OutputCache Duration="1" VaryByParam="*" SqlDependency="CommandNotification" %>--> However, I still get the next error:
File
Error When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance. What to do? Any help or advice is appreciated. Jelle
Would someone mind helping me with formatting a string please?? I have a column DECIMAL(4,2). In a stored proc I am selecting this field and converting it to varchar so I can append certain characters to it (this is really irrelevant to my question). However, before appending the characters, I need to remove zeros after the decimal point. Examples: 3.00 should be 3 3.20 should be 3.2 3.05 should be 3.05 etc
