Best Way To Clean Up Temp Files
Mar 13, 2006
I have a custom Data Flow task that creates temp files to the system temp directory during processing. A lot of times, we'll use SSIS to do one data transformation, running and tweaking the package along the way... we do this in the designer ... if we notice something that's incorrect in the data view, we just hit the stop button and fix it. However, when we do this, the Cleanup() function isn't called, and my temp files are left in the temp directory, when they really ought to be disposed of.
Is there a method that gets called every time when the DtsDebugHost quits, whether it finished, didn't finish properly, or was stopped in the middle? What would be a good way (other than having some service that monitors what temp files are used by what processes) to clean up temp files after we don't need them?
~Steve
View 1 Replies
ADVERTISEMENT
Mar 28, 2006
I had received a message that my log file is full and it do not enable to me to do a database backup before free up disk space. How do i clean up de log file (_log.ldf)?
View 5 Replies
View Related
Mar 3, 2004
Hi,
I need some advise in the log shipping. The log files in the primary server get cleaned up according to what I have specified in the maintenance plan. But the log files that got shipped to the secondary server stay there for ever wasting my hard disc. Will it make any problem if I remove them or can I set it up to remove all files earlier than past 2 hrs? Please advise.
Thanks
View 6 Replies
View Related
May 17, 2008
Quick history, I have substantial experience using MS Access, VBA, and Regular Expressions to clean up messy text files for use in MS Access.
I want to upgrade an MS Access back end to SQL Server (2008 to be specific). I need to upload several delimited files on a daily basis. The files are full of formatting that I strip out using VBA and Regular Expressions. I've poked around on the site and found some UDFs that implement the VbScript Regular Expressions object as a stored procedure in Transact SQL.
My questions is, am I better off processing the files externally using a procedural language like VB or can SQL Server handle the processing as gracefully as MS Access/VBA/RegEx does?
Thanks for letting me tap into your experience.
I could simply puzzle it out myself by implementing it in SQL Server but I'd rather not waste the time as a newb to transact SQL and then find out that I'd be better off doing it externally.
J--
View 1 Replies
View Related
Jan 2, 2000
Using SQL Server 7 I have the following files hanging out and I'm not sure what they are for or if I can delete them.
My data and logs are on the same RAID 5 drives. They are located in the E:DATA and E:LOGS directories.
I have the following files on the root of my E: drive
tempdbData.ndf
tempdbLog.ndf
tempdbData1.ndf
tempdbLog1.ndf
this goes on up to tempdbData4.ndf. The data files are large up to 2 GBytes. The dates are not current except for the last 2 sequential files.
Anybody know what they are there for and my I delete any of these files?
Thanks and Thanks again......
View 1 Replies
View Related
Nov 6, 2015
I re-started the SQL Service.
I did numerous commands with no luck.
Shrink DB, Shrink files and shrink DB.
I tried the GUI but it bombs out.
View 2 Replies
View Related
Mar 14, 2007
I am developing application with SQLCE2.0, NETCF1.0,Sp1,VS2003.
I found there is some files are created under "Temp" folder by the system with size "0B".
Why/when these file are created? Do I need to clean it periodicly? If not, will this cause exception like "Not enough storage is available to complete this operation"?
Thanks.
View 10 Replies
View Related
Mar 8, 2006
We are using the Import/Export wizard to create some simple packages to transfer tables. When doing a lrge number of tables, the 'Optimize for many tables' option is automatically selected (as noted in BOL). What we've found is that the package creates a bunch of temp files in the creator's Documents and Settings....Temp folder. Needless to say this package cannot be re-run later, nor scheduled, since the path referenced doesn't necessarily exist on the server. Is there a way to specify where these files should be created so thatthe package is re-usable and still be optimized?
Steve
View 1 Replies
View Related
Oct 22, 2007
Hello,
I created a SSIS solution for reading data from dbase and storing them in SQL Server. In a ForEachDirectory-Loop up to one thousand dbase files are read and stored. The system where the packages are running has 16 GB RAM.
For the first few hundred dbase files everything goes fine, but then, the RAM seems not to suffice any more and a temp file is created (I changed the path in BufferTempStoragePath).
How can it be that there is a need to create temp files if there is so much RAM available?
Why is the RAM filled more and more during the SSIS package execution?
Is there anything I can do to release some of it? (it is running in a loop and there is no need to store all the data)
Could it be caused by dbase?? (I use Microsoft Jet 4.0 OLE DB Provider)
Another thing is that the temp file is not stored in the path I set in BufferTempStoragePath.
There are sufficient permissions set, but temp file is still created in user temp folder...
Any kind of help is very much appreciated!
Best Regards,
Stefan
View 5 Replies
View Related
May 29, 2007
Hi all,
I have a problem with a Sort Transformation, I have a CSV file with 200'000 rows the csv file is about 30Mb. When the rows are processed in the Sort Transformation, SSIS generates around 160 temp files of about 10Mb each.
How can avoid so many temp files to be generated ?
View 4 Replies
View Related
Feb 18, 2014
I am having with trying to import XLSX files into SQL 2012 64 Bit.
I have installed the Access driver (AccessDatabaseEngine_x64.exe)
I have configured the script to run the following SP
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
[Code] ....
So I first create my Temp Table
The run the SP above then I run the insert into the Temp table defined
INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:TempNotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')
[Code] ....
The error message I get back is
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
What I have set wrong on the import? Using SSIS at this point is not a real option.
View 0 Replies
View Related
Nov 17, 2004
Hi all,
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
View 2 Replies
View Related
May 29, 2006
My DB is holding some data for the moment I'd like to clean.
I'm wishing to erase the WHOLE data stored in the database (just the data, not the DB itself). What SQL command should I use ?
If there is another way to do what I'm wishing to do, let me know please.
Thanks.
View 8 Replies
View Related
Oct 22, 2007
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
View 4 Replies
View Related
Dec 13, 2006
PHP Code:
WHERE
(dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.documentCategory.docCategoryName LIKE '%olic%') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude))
OR (dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) AND (dbo.document.docDesc LIKE '%olic%')
OR(dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) AND (dbo.document.docType BETWEEN 1 AND 4)
OR (dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude))
OR(dbo.t_files.fileName IN ('127-2006-1-27-3321130.pdf', '127-2006-1-30-3726619.pdf', '127-2006-1-27-5700042.pdf', '127-2006-1-27-5678586.pdf', '127-2006-1-27-5693574.pdf', '127-2006-1-27-5873392.pdf'))
ORDER BY dbo.document.docDesc
Right - as you can see, some of the line slook pretty similar. When I try and do:
(one AND two AND three) AND (four OR five), I get
(one AND two AND three AND four)
OR
(one AND two AND three AND five)
when using enterprise manager. Is there anyway to keep the first way of doing it. Otherwise, everytime I add anotehr OR statement in, I'll have to create a new line!
View 5 Replies
View Related
Apr 10, 2008
I have the following query which strips out middle initial data from the first_name column and populate the middle_initial column with the relevant data.
What my query does not handle is when a first name has a single character (IE: A). In its current state, the "A" would be moved to the middle_initial field with a period added to the end (IE A.). The first_name column would also include "A". Basically, when a single character first name is found in the first_name column, I do not want to populate the middle_name field.
Hope this does not sound too cryptic; query is below along with some sample data when run.
SELECT first_name,
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name))),1) +'.'
ELSE NULL
END AS 'middle_initial',
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),CASE WHEN LEN(LTRIM(RTRIM(first_name)))>=2 THEN LEN(LTRIM(RTRIM(first_name)))-2 ELSE
LEN(LTRIM(RTRIM(first_name))) END)
ELSE LTRIM(RTRIM(first_name))
END AS 'first_name_removing_initials'
FROM contact
Sample Data
first_name, middle_initial, first_name_removing_initials,
Paul, NULL, Paul
A, A., A,
A Fred, NULL, A Fred,
Aaron, NULL, Aaron
View 4 Replies
View Related
Nov 28, 2007
Dear All,
i'm trying increase the performance of one select statement.
after trying first time, at the second time it is giving results fastly.because the data is there already in the buffers.
how can i clean the buffers everytime after run the query?
i'm using
--dbcc dropcleanbuffers
--dbcc freeproccache
are these enough or need some more....
please guide me
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 8 Replies
View Related
Jan 17, 2008
I have a db server that had served both my application data and reporting services. I have since installed a second db server to be the reporting services server; the primary box still stores the application data. I am keeping reporting services installed and set up on the primary box, since I would like it to act as a warm standby for the reporting services function.
However, the ReportServerTempDB on my primary box is still quite large. The ChunkData table is quite close to 10 gig. The CleanupCycleMinutes property is set to the default of 10. Clearly any data that might be in this table is far older than that.
Is there some recommended method of clearing this data? Can I just delete the rows or do a truncate on the table? I would prefer not to uninstall/reinstall reporting services, as this machine is actively serving my application data.
Thanks.
View 1 Replies
View Related
Apr 28, 2008
Hi everyone,
What is the easy way to clean up all ms sql 2005 tables?
For example, a database table named Customers which has triggers and primary key and foreign keys. Now I clean up the Customer table using the folowing statement
delete Customers
And the ms sql 2005 asks me to remove all triggers and foreign keys before allow me to clean up the Customers table. Is there a way to clean up all tables without remove the tiggers and foreign keys?
Thanks
May
View 4 Replies
View Related
May 16, 2007
Hi,
My problem is that I cannot completely clean buffer cache on SQL Server 2005 version 9.00.2047.00 (probably SP1).
Right after I run DBCC DROPCLEANBUFFERS in the context of my database (this is development server, and so far I am only the one who is working with a particular database), I run a script that quetries sys.dm_os_buffer_descriptors view also from the context of my database to make sure that the buffer cache is really clean. However it shows large number of entries totalling 42 MB.
I ran both DBCC an the script in the past too, and it always showed nothing in the results, that means that buffers were really clean. The reason why I am running this is for benchmarking of existing and new application.
Does anybody have any idea, suggestions, how to troubleshoot this issue ? I already closed all connections to this database, but rebooting the server is not an option since other people are also working on it.
Thanks
View 2 Replies
View Related
Sep 22, 2005
How to clean all messages of a queue ?
View 5 Replies
View Related
Sep 11, 2006
Is there some recommended way to clean input before submitting it to the database? We'd like to develop a library that can be used on our ASP/ASP.NET apps to filter input before it's sent to the SQL Server and Oracle databases. Is there a way to create a .NET DLL that can be used for both ASP.NET and classic ASP apps. Thanks.
View 3 Replies
View Related
Feb 4, 2003
I am importing Visual FoxPro (6) views into SQL 2000 tables and I am looking for a snippet of code to "clean up" date fields upon insertion. I can insert the views into the SQL tables fine using the tables/views selection of DTS as my source. However, I would like to ensure the date fields are in fact vaild dates and not garbage using the SQL query option of DTS source. I would like to do the insert and cleanup in one step. Do you have a snippet of code to validate a date field that I can use? Thank you for any assistance in advance, Terry.
View 3 Replies
View Related
May 12, 2006
I posted this in the SQL Server Express forum as well...
My app deployed via ClickOnce. It ships with a database creation script which creates the database in the ClickOnce data directory. Upon uninstall, the entire data folder is deleted successfully. But if you then reinstall the app, SQL Server Express still thinks the database exists. Right now, to get a re-install to work, I have to go into Management Studio Express, click on the db (get an pop-up error as the physical db file is no longer there) and then click delete.
So my question is... what system proc should I execute in the db creation script before I run the CREATE DATABASE command?
View 1 Replies
View Related
Mar 13, 2008
Good afternoon.
I have an IS package that contains some legacy DTS tasks. The package does run successfully on some machines, but others not at all, generating a weird error that says "Error: The task failed to load. The contact information for the task is "Execute DTS 2000 Package Task;Microsoft Corporate...." There's more to the error but searching on it hasn't turned up any results so I'm not posting the entire thing. I can if requested.
On the server where the DTS packages fail, regular IS packages that contain no DTS legacy tasks run successfully, and the only difference between the machines that work and don't work is SQL Server 2000 is not and was never installed, whereas the machines that work are running 2000 and 2005. The servers where the tasks fail version 9.0.3050.
One thing I have noticed is that on the machines where DTS works there is an extra file in C:Program FilesMicrosoft SQL Server90DTSBinn called Microsoft.SqlServer.Exec80PackageUtil.dll. Copying the file to a non-working server did not solve the issue. I tried installing the backwards compatibility update but it said that a newer version was already installed.
If anyone could help I would greatly appreciate it. If you need more information just ask and I would be happy to provide it.
Thanks again,
Kelly
View 5 Replies
View Related
Oct 13, 1998
A customer is about to replace their PDC with SQL Server running on it.
MY companies role is to build the new server, and get their SQL Server running again.
I need to have the devices, databases, tables, data and the USERS copied across. I cannot use transfer manager as their will be only 1 instance of SQL Server running on the network.
If all we have is an NT backup is perfomed on the entire disk, i.e. the MSSQL/DATA directory.
How do we recreate the databases and their associated tables and also, how do we recreate the User accounts ?
Any ideas would be appreciated.
Please feel free to e-mail me with a response
NEILA@ANGLIABC.CO.UK
TIA
View 4 Replies
View Related
Feb 17, 2007
Hi,
I cannot log in to SQL Server 2005 Dev Edition in my local machine using Windows Authentication. The server returned "Login failed..." when connecting with SQL Server Management Studio.
I have not change anything since installation of this server.
This problem happens in RTM and SP1 versions, both running on Windows Vista RTM.
Anyone having this kind of problem too? Any solution? I'm guessing it's Vista-related.
View 1 Replies
View Related
Sep 7, 2007
We are using SQL Server 2005 SP2 to do transactional replication.
We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.
I have been getting an error message when we run the distribution clean up job.
Executed as user: PRODsqladmin. Could not remove directory '\Tes01boxRepldatauncqabox01_DB01_TO_ORACLE20070905104896'. Check the security context of xp_cmdshell
I have dropped the publication and recreated which is what appears to have caused the error.
From
http://technet.microsoft.com/en-us/library/ms151151.aspx
Note:
If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.
The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.
Finally my questions:
Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?
If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?
View 3 Replies
View Related
Jul 25, 2006
In a integration project I am moving data from A to B.
First time is fine - since table B is empty.
However next time I run the process, I would like to delete all records in B before I run the project again.
What is the best way to delete / clean up data when you re run the process ?
Cheers, T
View 1 Replies
View Related
Jun 29, 2015
I have problem when I create the clusters I created domain account and give him permission create computer objects and real all properties but there are problem when clean up the cluster always pop up and error but latter I tried using my domain admin and worked perfectly is there any permission is missing for cluster ? or all I need is Create Computer Object and Read Permission?
View 3 Replies
View Related
Nov 27, 2007
I have a database that I have been creating and testing. I have added some junk data and some data into lookup tables. Is there a way to create a clean copy of the db and keep the lookup table data? Also will I be able to create the db under a new name?
View 1 Replies
View Related
Jun 18, 1999
I have a number of test databases which were created from the same backup. Set up SQLMaint to run on them each night. SQL Maint seems to run clean on all of them, but on two the Scheduled Task shows "Failed". All tasks have been set up exactly the same.
Has anyone run into something like this?
Here's the output from the maintenance job for one of the failing db's:
Microsoft (R) SQLMaint Utility, Version 6.50.240
Copyright (C) Microsoft Corporation, 1995 - 1996
Logged on to SQL Server 'COSTGUARD2' as 'sa' (trusted)
Starting maintenance of database 'Test15' on Thu Jun 17 23:10:01 1999
[1] Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Check Data and Index Allocation...
** Execution Time: 0 hrs, 0 mins, 2 secs **
[3] Check System Data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Update Statistics...
** Execution Time: 0 hrs, 0 mins, 27 secs **
End of maintenance for database 'Test15' on Thu Jun 17 23:10:30 1999
SQLMAINT.EXE Process Exit Code: 0 (Success)
View 2 Replies
View Related