Hello All
I have a SQL 2005 server I inherited and am trying to clean up. I am unable to delete some log files. The databases they were associated with do not exist in sysdatabases. However, when I try to delete them I get the message they are in use. There are no other instances attached to this server.
Any help is appreciated.
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
I need to delete all the *.txt file in a specific folder modified date is older than 2 months,. How can I do this in a stored procedure?
View 3 Replies View RelatedI need to delete all the *.txt file in a specific folder modified date is older than 2 months,. How can I do this in a stored procedure?
View 5 Replies View RelatedI need to setup an automated process to delete all the files older than a month in a particular directory on a network drive, how is that possible? Any help is greatly appreciated.
thanks.
Sheila.
hi,
i want to delete the backup files older than 15 days. how can i do this?
Actually i backup the db and log using the TSQL, and the backup files are in d:ackup folder. I tried sqlmaint,xp_sqlmaint but its not running .
Any idea? thanks in adv.
kumar
can I use xp_cmdshell to delete files older than lets say 30 minutes? can anyone show me what the command will look like?
Thanks
Shahab
Hi everybody,
Can anyone help me out in deleting files more than n- hours from a physical folder using xp_cmdshell ....
how can I delete files, which are allready imported into a database. I have a folder with 600,000 files . All Files should be in a database. And I want to delete the files that are found in the table with filename . How can I make the ?? The Filename and physicals Name is the same so i can say table.filename=physical.filename.
View 20 Replies View RelatedI've been tasked with coming up with a process to delete old files out of a certain directory. It's prefered to have a configuration file to go in and be able to change the number of days to retain files (7 for delete files older than 7 days for example). I've been told I can do this with SSIS. Can anybody point me in the right direction to do this in SSIS or even in DOS? I don't know ActiveX scripting if that's what's needed in SSIS. I prefer doing something like this in DOS but not sure it's possible to automate. Any help is appreciated.
Van
I wish to delete remote files on an FTP server after I download them. I want to download files daily and delete them daily after I finish downloading. I am trying to use the FTP task to do this but I get an error when it tries to delete files from specified location. It says:
"Unable to delete files using FTP Connection Manager" The FTP connection manager contains the FTP connection string and password. I have unchecked the passive mode box. Can someone help me with this error,
Thanks
Annie
I already did the following but still it wont delete the log file because it is not empty- DBCC SHRINKFILE('logfilename',EMPTYFILE) - DBCC SHRINKFILE('logfilename',TRUNCATEONLY)- ALTER DATABASE databasename REMOVE FILE logfilename
View 4 Replies View RelatedHello,
How to delete old files stored in a directory using dos script?.
I appreciate your feedback.
Thanks,
Ravi
I have a database with 2 log files VE_log and VE_log2 (the split is left from legacy system with limited disk space). I want to eliminate the 2nd log.
I've tried SHRINKFILE (EMPTYFILE), which of course reduces the file to the default minimum pages, SHRINKFILE (TRUNCATEONLY), SHRINKDATABASE (VE), and in every case the ALTER DATABASE REMOVE FILE command fails cause the log isn't empty.
Ideas???
Thanks,
Al
Folks:
I want to delete files on windows from a directory which are 2 days old. I understand we can do that using xp_cmdshell. Anybody with a script would really help me.
Thanks !
Ok in SQL 2000 this is within the maintaince plan, where as i have to create a seprate one for sql 2005 being i assume the 'clean up history' I choose the backup andrestore option choose 2 weeks and then run the job once created it runs sucssessfully but yet it dosnt delete a thing. I have backups going back almost 2 months now.
What on earth am i not doing?
Thanks in advanced
I created a backup device and a job to backup a full dbs and transaction logs. When I first created the backup statement it was created with RETAIN DAYS = 10
Its been 15 days and the ones created after 10 days appear to be marked for deletiong but I am obviously missing a statement in my backup statement or in another job I need to schedule to come through and delete the old files from the backup device. Please help me figure out what this statement needs to be.
All the backups and T-log backups are written to one device which is one file.
Thanks
Scott
is it possible to delete a source file(*.txt) when a SSIS package is done with it?
View 3 Replies View RelatedHi all,Can anyone help me with a script which would delete files ormove them to a different folder at some scheduled time..!Please.....!!!Thanks in advance...
View 5 Replies View RelatedI have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -
EXECUTE master.dbo.xp_delete_file 1,N'E:Program FilesMicrosoft SQL ServerMSSQLLOG',N'txt',N'2007-07-09T10:59:38'
)
I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?
Thanks in Advance,
DeWayne
Hello,
I have two FTP Tasks configured in my SSIS package. One is for "Receive files" and the other is set for "Delete remote files." Both use variables for the source/destination paths. My remote path variable contains a wild card in the name field such as /usr/this/is/my/path/*.ext and it is working to FTP all the .ext files to my working directory. I then rename the files and want to remove the original files from the FTP server. I use the same variable as the remote path variable in the delete as I do in the receive.
Using the same FTP connection manager for both tasks I am always getting a failure on the delete. The FTP connection manger is setup to use the root user. Using a terminal I am able to open an FTP connection to the server and remove the files manually. There doesn't seem to be any detailed documentation on the FTP Task configured for Delete remote files so I'm hoping someone might have some insight to the problem.
I receive the same message for each of the files that was downloaded:
Error: 0xC001602A at MyPackage, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 550 usr hisismypathdatafile1.ext: No such file or directory.
The attempt to delete file "usr hisismypathdatafile1.ext" failed. This may occur when the file does not exist, the file name was spelled incorrectly, or you do not have permissions to delete the file.
With the root user/working manually I'm not understanding the permission reason, the file does exist and is spelled correctly.
Dan
We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.
We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"
This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.
Please let me know the solution.
Thanks,
Priaynk Gajera
I can create remote folder and delete it
I also can create remote files but i can not delete remote files
error warning is like subject
any ides? thanks
I'm using below script within the execute T-SQL statement task of the maintenance plan, which is created to delete the zip files older than 2 days, the task runs before the backup database task gets kicked off, but some how the task is not deleting the old .zip files.
Code being used within the task: output of the @delete_file variable within the delete command is Del X:SQLBackup*2015_05_06*.zip before it is passed to the final exec statement, the server is in EST time zone.
declare @ConvertDate char(10)
declare @Delete_file varchar(80)
set @ConvertDate = CONVERT ( char(10) , dateadd( dd, -2, getdate()) , 121 )
[code]....
I want to delete all files in a given folder that are older than 3 days from today's date.
I tried using a "Foreach Loop container" with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?
TIA,
Barkingdog
P.S. History of the problem:
I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep! I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.
Hello,
I am experiencing an issue with SQL Server 2000 Maintenance Plan.
DB Backup job fails to delete old backup files from the file server (I am backing up to the network share - actually, a DFS).
Backup part of the maintenance plan/job succeeds, but then cleanup part fails.
I made sure that service account under which SQL Server Agent is running, has sufficient privileges over the network share by logging in and successfully deleting files in question.
I was not able to locate any log entries either on the SQL Server machine or on the file server machine that would indicate the root of the problem. Even though I turned on auditing for Delete operations for the destination folder, its subfolders and files, I could not find anything in the Security event log.
I would appreciate any ideas on how to troubleshoot and correct this problem.
Thanks in advance,
I used the toolbox to select maintenance cleanup task to create the job to do this. In reading similar notes regarding this problem, some people mentioned that there was a choice to include subfolders. I do not have this choice. When I execute select @@version I get Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) . This is running on a cluster. Any idea what is going on here? Thanks.
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated!!
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies View RelatedHello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh