DB Engine :: How To Remove Log File Older Than X Days
Apr 20, 2015Log file rotation and cleanup script and how to remove the log file older then x days.
View 7 Replies
ADVERTISEMENT
Delete Records Older Than 5 Days .
Aug 20, 2002Hello ,
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
DB Engine :: Cannot Remove One Last Data File From TempDB
May 19, 2015In 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.
I have also tried using the command:
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev#, FILENAME = '<new location');
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.
Delete Records In A Table Older Than 90 Days . Help
Aug 5, 2002Hi ,
I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .
Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .
Any thoughts how to do it through query and schedule it ???
Thanks
Deleting Backup Files Older Than 2 Days
Jun 23, 2008Friends -
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
How To Delete Data Older Than X Days, Without Considering Time
Nov 11, 2005When running the following SQL statements, I get the same results.Though I need to count only -30 days. Both statements below alsoconsider the time of the day as well, which is not desiredDELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < GETDATE()- 30DELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())Here is the format of the values in columnMNT_R.TIMESTAMP2005-08-09 06:06:44.5772005-08-09 06:06:46.8102005-08-09 06:06:49.060So, since data are inserted into the MNT_R table every few seconds, mydelete statement will delete different number of rows, according to thetime of the day it runs.Can you please post a SQL query that will not give me this headache?thanx a lot all
View 2 Replies View RelatedDeleting Backup Files Older Than 5 Days Old.
Mar 14, 2007I 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
Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates
Jan 7, 2014I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
Delete Files Older Than 3 Days Using SSIS Tasks
Aug 11, 2006I 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.
T-SQL (SS2K8) :: Query To Find Older Date That Is Less Than 90 Business Days
Feb 12, 2015select DateAdd(dd,-90,getdate())
It gives the 90 days older date but i want to exclude the weekdays and holidays (saturday ,sunday,holiday)
As we dont have permission to call a function or Sp in high level environments looking for a simple query .
Maintenance Plans (2000), Remove Files Older Than, And Network Shares
Sep 21, 2007We 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.
SQL Server 2008 :: Delete Folders / Sub Folders Older Than N No Of Days
Jul 30, 2015If I run the below command, it clean up the files inside the folders but unable to clean up the folder and sub-folders older than 30 days. xp_ cmd shell ' forfiles -p "D:abcd" -s -m *.* -d -30 -c "cmd /c del /Q @path"'
View 4 Replies View RelatedDB Engine :: Creating A Schedule Task To Execute After 5 Days From The 28th Of Every Month
Jun 7, 2015Is it possible to create a SQL schedule task to execute after 5days from the 28th of every month.
View 3 Replies View RelatedDB Engine :: Cannot Remove Key Lookup From Execution Plan
Jun 4, 2015how to eliminate a key lookup from the execution plan
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED  Â
  SET NOCOUNT ON Â
  SELECT COUNT(ph.lid) AS Total Â
  FROM  PLB ph Â
  WHERE  ph.lPhysician = @Physician Â
  AND  ph.BSF = CAST(0 AS bit) Â
 Â
[code]....
For Loop - Iterate From Older Files To Newer Files Based On File's Timestamp
Mar 13, 2008In 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?
DB Engine :: Recommended Size And File Growth For A Database And Log File?
Sep 22, 2015What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy
ONÂ
PRIMARY
( NAME = Dummy_data,
  FILENAME = 'D:....DATADummy.mdf',
  SIZE = 250MB,
  FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
  FILENAME = 'D:....DATADummy_log.ldf',
  SIZE = 50MB,
  FILEGROWTH = 5MB ) ;
GO
Ldf Log File Gets Large In A Few Days
Aug 10, 2006Hi to all
My database's log file is getting very large in a few days. There are only 2 users and they do not do a lot of work. But it gets larger and larger every day.
Autogrow is enabled. File Growth is 'in percent' and Maximum file Size 'Restricted File Growth-500MB'
Is there a way to stop the log file growing huge?
Thank you
DB Engine :: How To Restore From Current LDF Log File And Old BAK Backup File
Jun 11, 2015My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC
2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original
3) Took the new database offline
4) deleted the MDF and LDF files of the new database
5) put "MyDB_0.LDF" in the place of the LDF file I just deletedÂ
6) put the database back on-line
7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)"
8) tried to do Tail Log Backup with this command
  BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak'
WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3
Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
DB Design :: LDF File Keep Getting Corrupted From Last Two Days
Aug 6, 2015I want to know is there any process alert me that my data or .ldf file get corrupted?Â
I've been going through data corruption from last 2 days in row that .ldf getting corrupted and I've to fix it.
Is there anyway I can setup any alert? or any better procedure ?
DB Engine :: Extension FILE Among Backup File
May 8, 2015I checked quickly one network  and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
View 6 Replies View RelatedDB Engine :: Cannot Create A File When That File Already Exists
Aug 18, 2014Executed as user: S233683-AD01S233683NJ3SQL05. ...at file already exists.' [SQLSTATE 42000] (Error 22048) Â ERROR -- Could not backup database: master - BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 50000) Â xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'
View 2 Replies View RelatedSQL Server - Select Records Added Today, Last 3 Days, 7 Days...
Oct 25, 2006Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
How I Remove Records From The Log File
Oct 20, 2001I have SQL Server Standart Edition and ý have a database, MDF file size is 650 MB, log file size is 1,23 GB
I tried to shrink the log file but i didnt shrink, I wanr to remove all records from the log file
How I remove records from the log file I want to shrink this file to 1 or 2 MB
Please help me
Sysaltfiles , How Can I Remove A File
Jul 28, 2004I'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.
How To Remove Secondary Data File????
Jan 16, 2003I 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?
Thanks, Dave
Shrinkfile/remove File On Active DB
Mar 14, 2003Can I execute these commands while the database is in
multi_user mode and is being actively accessed (via a
ColdFusion web app)?
dbcc shrinkfile (Web_data2,EMPTYFILE)
GO
alter database Web remove file Web_data2
GO
.
SQL 2012 :: Remove TempDB NDF File
Jul 8, 2014I have added ndf to tempdb for checking performance improvement.... Now I want to remove the ndf file... I am using below command...
USE tempdb
GO
DBCC SHRINKFILE (3, TRUNCATEONLY);
GO
use master
go
ALTER DATABASE TEMPDB Remove FILE tempdev1
Results:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
23766476643232
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...
How to remove / drop ndf file...
Remove First Line Within A Flat File
Feb 1, 2008Hello,
Does anyone know of a way to remove the first line of characters from a flat file?
Thank you for your help!
cdun2
Alter Database With Remove File Clause
Apr 4, 2001I'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
View 2 Replies View RelatedNeed To Remove Certain Rows From The Flat File Before Transformation
Jun 4, 2007hi
I have a flat file as below
Date 20070606
Empid Salary X1 x2 x3 x4 x5
100 10000 .............................
where 10000 is the salary got on date 20070606 by empid 100
.x1,x2,x3...are the remaining columns in the file.I need to extract the date and also continue reading the remaining data.How can i do this???
Any help?
thanks
ami
Need To Remove Jounal File After : Transfer/apply To Secondary Db
Jul 26, 2004We 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) ?
Thanks
Cann't Remove Data File From User Database
Mar 15, 2004We 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
Can anyone help?
Thanks
Integration Services :: Remove Certain Line Feeds In CSV File
Jun 23, 2015I 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.
View 6 Replies View Related