Deleting Older Files

Aug 23, 2004

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

Lystra

View 5 Replies


ADVERTISEMENT

Deleting Backup Files Older Than 2 Days

Jun 23, 2008

Friends -
I am looking for a windows script (bat file) to delete backup files which are older than 2 days.

Please provide scripts on this.

Appreciate your support

Cheers :)
Satish

View 2 Replies View Related

Deleting Backup Files Older Than 5 Days Old.

Mar 14, 2007

I am using the backup task and backing up a database but want to delete all backup files older than 5 days old. I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time. I am getting illegal character in path. How can I go about this.

I currently have E:MSSQL.1MSSQLBackupdatabasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bak

View 4 Replies View Related

Deleting A Record Older Then 10 Minutes

Aug 26, 2005

Hey Guys,I have been trying to work out how I would delete a record that was created more then 10 minutes ago.I can use this to delete records older then a day.DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1Just need something now that will do it for just 10 minutes.Cheers.

View 1 Replies View Related

Deleting Older Rows In The Table

Oct 20, 2006

Hi,

I have a table which has around 50,000 rows.

Can I delete the rows older than one year?

If no, Can I delete the last 25,000 rows (older rows) from the table?

Can you assist me with the SQL Statement to do this?

Thanks

Santhosh

View 7 Replies View Related

For Loop - Iterate From Older Files To Newer Files Based On File's Timestamp

Mar 13, 2008

In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.

Any Suggestions?

View 3 Replies View Related

Delete Backup Files Older Then...

Mar 5, 2007

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

View 6 Replies View Related

Delete Files Older Than 3 Days Using SSIS Tasks

Aug 11, 2006

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.

View 14 Replies View Related

Maintenance Plans (2000), Remove Files Older Than, And Network Shares

Sep 21, 2007

We have some to-disk backups scheduled on our 2000 Enterprise machine - nightly fulls, hourly logs - that go to a network share located on another machine. They were originally stored directly on the same machine as SQL Server, but we changed them to a remote destination within the past few weeks. This works okay, but despite having the maintenance plan set to remove files older than 2 days, old files don't seem to be removed. Understandably, this gets to be a problem when the backup disk becomes filled.

Is there any obvious reason why this option wouldn't work against a network share? I've checked the directory permissions, and the SQL Agent domain account should have no trouble deleting the files.

View 1 Replies View Related

SQL Server 2008 :: Query To Delete All Backup Files Older Than A Specific Date?

Jul 29, 2015

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"'

View 2 Replies View Related

Maintenance Plan Fails Trying To Delete Files Older Thean One Day. Running SQL Server 2005

Oct 1, 2007

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!!

View 3 Replies View Related

Deleting TRN Files

May 26, 2006

I have a database and I see that I have a lot of TRN files behind it taking up more than 82GB disk space.I have a TRN file from Jan until today. I plan to delete every one of them until April to recover 59 GB of disk space. Would that be OK?

View 4 Replies View Related

Deleting Files In A Directory More Than 200

Dec 1, 2003

http://forums.databasejournal.com/showthread.php?s=&threadid=29895

View 2 Replies View Related

Maintenance - Not Deleting Old Files

Apr 3, 2006

We have our Maintenance routines set up to delete files older that one week, but it is not working...

...Consequently, we forget to go out and remove the files manually, thus we lose backups due to the drive being full and have to manually remove files, then backup the databases and start all over again.

Has anybody had this problem and can you tell me where to look to try and figure out what the problem is ??

Thanks in advance,
Nancy

View 6 Replies View Related

Deleting Old Backups/trn Files.

Nov 21, 2005

i have a maintenance plan running on my database, in which I told the wizard, on creation, to "remove files older than 4 week" and yet it doesn't seem to be doing so, as on checking this morning, diskspace was getting low, due to over 300gb of backups and trn' dating back to september.

Anyone have ny problems with maintenance plans not cleaning up when told?

a

View 4 Replies View Related

Deleting Backup Files Question

Aug 8, 2000

I have created some backups where expiration dates or days to be
retained were not specified.

Using EM, how do I find the old backups.

Using EM, how do I delete the old backups (hoping this will
clean up the MSDB tables and physically delete the files
from disk).

Thanks!!!!

View 1 Replies View Related

Deleting Files In A Directory More Than 200MB

Dec 1, 2003

Hi,
I have posted a request regarding deleting the file more than 1 month old that we have discussed in bleow URL.

http://forums.databasejournal.com/showthread.php?s=&threadid=29895

My new request is I have enabled C2 Audit mode and wanted to delete old files that is more than 200MB.
Could you Please give me a query ?.
Thanks,
Ravi

View 2 Replies View Related

Backup Jobs Not Deleting Old Files!

Sep 12, 2007

Such a simple task. Not doing as it should!!

This Maintenance Cleanup Task is set to delete all BAK and TRN files it's made (in seperate maintenance plans) in given path, with the given file extension, delete files based on the age of the file at task run time. Delete files older that 4 days.

The files are now backing up for months and months. I'm not going to take care of this. I've got a computer to do this for me, every time it runs its jobs, every 4 hours and once overnight.

This is ignoring commands and refusing to do as it's told. I've checked the settings in here over and over. It's so simple - what could be wrong? I've checked the path, the file age, the extensions... The disks are getting full!

Has anyone seen anything like this?

View 17 Replies View Related

Deleting Files With The FTP Client On A UNIX Box

May 2, 2008



I get the following error when I try to delete files using the FTP client in a SSIS package. This is the error I get.

Error: 0xC002918E at FTP Task, FTP Task: Unable to delete remote files using "FTP Connection Manager".

Task failed: FTP Task


This is a unix server. I'm able to delete the files using other FTP clients but the FTP client in the SSIS package cannot delete the files. I read on many places on the interent that this is a known MS bug. Let me know if there is some sort of work around for this. I'm using SQL servewr 2005 SSIS packages to accomplish this task..

Thanks

View 3 Replies View Related

SQL 2012 :: Deleting Already Duplicated Filestream Files?

Feb 8, 2015

I have three FileStreams (FS1 on F drive, FS2 on H drive, FS3 on E drive) belonging to the same FileStream group of one particular database (DB) which is in Simple recovery mode in the SQL Server 2012.

FS1 contains huge number of files due to which F drive is completely full.

So, I am trying to move some of the extra files from one FileStream (FS1 on F drive) to another FileStreams (FS2 on H drive and FS3 on E drive) using command:

dbcc shrinkfile('FS1', emptyfile)

Then, I take the Full and Differential backup of the database and issue the CheckPoint and try to delete the already duplicated files from the Filestream FS1 to get some space in the F drive using command:

sp_filestream_force_garbage_collection @dbname = 'DB' , @filename = 'FS1'

But still no files get deleted and I receive the output as such:

file_name num_collected_items num_marked_for_collection_items num_unprocessed_items last_collected_lsn
DB_FS1 0 0 0 25000001749500000

how to delete these already duplicated files.

View 0 Replies View Related

2005 Maint Plans .txt Files Not Deleting?

Aug 16, 2007

I am running 2005 SP2 (It is the SP2 refresh as I downloaded the SP2 on 3/15/07) and I can't get the .txt maint plans to delete. The job completes successfully; however, the old .txt files are still there. I found this technote: http://support.microsoft.com/kb/938085
although I don't receive the error message in the KB. The report does have "New Component Output" as the 1st line in the report. I still don't see how deleting the 1st line of an already run job (the report) will affect a new run of the job.

Also, I checked the version of the Microsoft.SqlServer.MaintenancePlanTasks.dll and it is at 9.00.3043.00 (which is the SP2 Refresh) in KB 933508 ( http://support.microsoft.com/kb/933508 ).

I have several SQL/2005 servers and they all have this problem. Any help would be appreicated...

Thanks,
DeWayne

View 3 Replies View Related

Deleting Extra Tempdb Log And Data Files

Jul 20, 2005

We had someone create an extra data file and log file for tempdb. Sowe currently have two data files and two log files. Is it possible todelete the newly created data and log files? If I just delete thephysical files, I assume they'll get created as soon as SQL Servergets started back up. Any help would be great, since a single dataand log file for tempdb is my goal.Thanks much.sean

View 3 Replies View Related

Deleting Files Using SSIS Scripting Object

Jun 5, 2006

I am utlizing a scripting object in my ssis to combine two text files into one final file, and then I want to delete the original files. To do this I am utilizing the FileSystemInfo namespace and associating the file names, then utilizing the DELETE functionality.

The creation of the final file works perfectly...unfortunately, my base files do not delete, and I do not get a failure message or indictator.

Here is my code:


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.IO.File
Imports System.IO.FileSystemInfo
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain


' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim strCurrentMonth As String
Dim strCurrentYear As String
Dim strWriteFileName As String
Dim strReadHeaderFileName As String
Dim strReadBodyFileName As String

'Utilizing a case statement, determine the monthname & year and set the appropriate variables
Select Case Month(Now())
Case 1
strCurrentMonth = "January"
Case 2
strCurrentMonth = "February"
Case 3
strCurrentMonth = "March"
Case 4
strCurrentMonth = "April"
Case 5
strCurrentMonth = "May"
Case 6
strCurrentMonth = "June"
Case 7
strCurrentMonth = "July"
Case 8
strCurrentMonth = "August"
Case 9
strCurrentMonth = "September"
Case 10
strCurrentMonth = "October"
Case 11
strCurrentMonth = "November"
Case 12
strCurrentMonth = "December"
End Select

strCurrentYear = Year(Now()).ToString

'Set variables with file names (reader files and write file) for ease in readability and to
'set final (write file) with appropriate nameing convention utilized by Matria HealthCare.

strWriteFileName = "\CUPSRV05SHAREDISPublicData ExportMatriaFiles TO Matriacup_ref_cup_" & strCurrentMonth & strCurrentYear & "_ftp_ReferralFormat.txt"

strReadHeaderFileName = "\CUPSRV05SHAREDISPublicData ExportMatriaFiles TO MatriaMatria_Referral_Control.txt"

strReadBodyFileName = "\CUPSRV05SHAREDISPublicData ExportMatriaFiles TO MatriaMatria_Referral.txt"

'create stream reader/writer objects

Dim sr As New StreamReader(strReadHeaderFileName)
Dim sr2 As New StreamReader(strReadBodyFileName)
Dim sw As New StreamWriter(strWriteFileName)

'feed the header record into the final file

Do Until sr.Peek = -1
'write the header record
sw.WriteLine(sr.ReadLine)
Loop

'close the read stream for the header record file
sr.Close()

'Feed the body records into the final file
Do Until sr2.Peek = -1
'write all base records
sw.WriteLine(sr2.ReadLine)
Loop

'close the read stream for the body records
sr2.Close()

'close the write stream for the final distribution file
sw.Close()

'dispose of all stream objects
sr.Dispose()
sr2.Dispose()
sw.Dispose()

Dim EligBaseFile As New FileInfo("strReadBodyFileName")
Dim EligHeaderFile As New FileInfo("strReadHeaderFileName")

EligBaseFile.Delete() <--These do not delete or through an error
EligHeaderFile.Delete()

'final statement for SSIS package to determine script result

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I would appreciate any light you can shed on this. Thanks!

View 5 Replies View Related

SQL 2005 Maintenance Cleanup Task Is Not Deleting Files On Remote File Share

Jan 16, 2008

I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share.
When I try and remove the old files with a clean up task I get an error and the files don't get deleted.


The version is as follows
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)

The error result is as follows,

Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file
0,N'\\EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The
system cannot find the file specified.'". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.

The maintenance plan seems to be adding extra "" though when i enter the
code directly in a query i get same error.

Query:

EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1BACKUPSABCD_BACKUPABC_DAILYABCD',N'trn',N'2008-01-13T12:52:49'

Error:

xp_delete_file() returned error 2, 'The system cannot find the file specified.'

The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task.

Trying to figure out how to get the Cleanup task to delete old files. The same happens for all file extensions and I have tried other locations with simpler file paths same error.


Regards,
Scott


View 6 Replies View Related

Deleting The Master Table Withour Deleting The Child Tables

Aug 9, 2007

Hi
i have to delete the master table data without deleting the child table records,is there any solution for this,  parent table has relation with the child table.
regards
vinod.t.v

View 9 Replies View Related

T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?

Jul 18, 2014

/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]

-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.

[Code] ...

*** Getting this error below when executing the code ***

Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.

View 9 Replies View Related

SSE And Older Pc

Sep 8, 2007

I have an old Compaq Presario walmart pc. The stats on it are: 750MHz Duron, 512MB RAM, currently have 20GB and 30GB hard drives and planning on upgrading HD (100GB+), onboard nvidia vanta (8mb tnt2). If I get a larger hard drive, XP Pro, and put SQL Server Express on this computer, would I be able to get things done, or will this be extremely slowed down?

Thanks,
Nate

View 1 Replies View Related

OLDER VALUES In DDL Trigger

Feb 28, 2008

Hi All
Can anyone help me to keep track of older view or stored Procedure that has been altered so that I can compare the two and make out the changes that have been made.At present I 'm able to get just the  altered view or storedProcedure but how to know that what has been changed.
This will be somewhat similar to Instead if and after in DML trigger but DDL triggers doesn't supports it.If anyone is aware of something then please help
THANKS in advance
Anisha
 

View 2 Replies View Related

Comparing Dates (older Than)

Feb 21, 2007

Hi Guys,

I am really stuck on exactly how I can do this. I can sudo it in my mind but can't figure out how I would code it.

What i want to do is select all records from a table that the log_date is older than 7 days.

If I was comparing a date i would imagine i would do something like

log_date < NOW()-7

I have looked at:

SELECT player_name, player_last_logon_date,DATEDIFF(day, player_last_logon_date, GETDATE()) AS NumberOfDays from player

select player_name, player_last_logon_date, datepart(dd,player_last_logon_date) from player

but I dont think either is really right. any and all advice would be great!

Thanks

View 2 Replies View Related

The Posts Should Not Be Older Then 2 Months In Database

Jan 28, 2008

Is it possible to send a email after a certain date has pass by. And 2 days after the email has been sent the post should be removed if the member doesn't answer the message.
example: The posts should not be older then 2 months. When they get older than 2 months the SQL-Server detects this post and sends a messegae to the member if he wants to renew it. If he ignors it for 2 days after the email the post will be removed from the database. (Some posts have images also, They should also be removed from the harddrive)
Is this possible in MSSQL-Server to send email?
If someone could tell me how to create this.

View 6 Replies View Related

Delete Records Older Than 5 Days .

Aug 20, 2002

Hello ,

I am little confused in writing the exact query i.e filling the correct details in the query .
To simplify let me explain....

I am using the query as

delete MYTABLE
where datediff(dd,loaddate,getdate())>5

I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .

Now suppose if i want to delete previous 5 days records from today
( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
The records from 15/08/2002 to 20/08/2002 remain intact .

I am getting some different results .

Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .

Is the logic correct or i am missing someting important ?

Thanks and Regards
Admin001

View 1 Replies View Related

Need Help Restoring Database From Older Backup

May 1, 2008

I am in the process of moving a database to a new location. My problem is that I do not have the most recent full backup file. I have a previous full backup file and I also have all of the transaction log backups from the time of the old backup to the present time.

My question is, do I need the most recent backup to restore the database, or can I do it with the older backup plus the transaction log backups?

Thanks in advance.

View 14 Replies View Related

Installing SQL EXpress With Older SQL Server?

May 9, 2006

Hello,

Can I install SQL Express on Windows 2003 server which also has SQL Server 2000?

I already have .NET 2.0 Framework installed.

Should I download the full version of SSE?

Thanks

Frank

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved