I added a maintenance CleanUp task pointing to the right folder I checked the include first-level subfolder box, and delete files based on the age of the file at task run time
I specified "delete files older than " 1 day
But the cleanup task is not working, I have to manually go a delete the files myself.
Am I missing something? should I run the maintenance task first? and then the backup task?
I have a problem with deleting old bak files from a Cleanup Maintenance Task. I have backups in subdirectories and I read in SQL Server 2005 Books Online that:
In contrast to earlier versions of the Maintenance Cleanup task, the SQL Server 2005 version of the task does not automatically delete files in the subdirectories of the specified directory. This constraint reduces the surface area of any attack that could exploit the functionality of the Maintenance Cleanup task to delete files maliciously. To delete the first-level subfolders, you must explicitly elect to do this by checking the Include first-level subfolders option in the Maintenance Cleanup Task dialog box.
I cannot find such an option in my Maintenance Cleanup Task dialog box. I do not think I installed any service packs yet, could this be the problem?
Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2.
this is transact sql running in the back ground. EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'"bak" & "trn"',N'2007-03-26T22:21:14',1
This DOESNT WORK.
It works if I just try to delete only one of the things ie trn or bak files.
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.
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.
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases) - Plan 1 (1 database) - Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order: - Check Database Integrity - Backup Database (Full) - Shrink Database - Update statistics - Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run plan name task name duration log type log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
I've had problems trying to get a new maintenance plan on a new server to work.
I'm running a plan that does an integrity check, reindex, shrink, backup, the a maint cleanup to get rid of backups older than 2 weeks.
I'm getting failures that say:
Error Number -1073548784
Error Message: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'BAK',N'2007-10-18T18:43:41',1 " 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.
Yes, this is a post SP2 server. Current version, with the patches I've applied looking to fix this is: 9.0.3054
Additionally, if you go in to edit a maintenance cleanup task, you cannot click on OK, or the T-SQL. Changing values within it does not help.
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases) - Plan 1 (1 database) - Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order: - Check Database Integrity - Backup Database (Full) - Shrink Database - Update statistics - Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run plan name task name duration log type log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Our backups by default go to a network location, but I'd like to modify our maintenance plans to backup to an alternative location if the primary location isn't available. I've setup two Backup Database Tasks where the second one runs only if the first one Fails, and if the second one runs (on first one's failure) it then sends a notification to me so I know this occurred.
The Plan is running as expected, when I simulate a bad path in the first Backup Task the second one runs and the notification is sent, but the Job shows failure. I'd like to show the job as Successful when this occurs since I'm handling the issue and notification within the Plan, but I'm unable to find out how. I've set FailParentOnFailure to False on the Plan and I've changed the MaximumErrorCount to 2 with the assumption that this would work, but neither didn't.
Also I'm running into this in both SQL 2008 and SQL 2012.
I have a scenario where i have to run update task on multiple servers in parallel and once all of them are completed (success or failure) another task is to be run on another server
1. in maintenance plan, if we add tasks which are not joined, will they run in paralled at the same time 2. if we link the last task to all the tasks with link type 'completed' will the last task complete after all tasks are completed or when any one of them is completed (i have big doubt here)
the business requirement behind this is to bring data from multiple servers into shadow copies locally and then process them together. its ok if some server data transfer fails, but its not ok to start processing centrally while data transfer is going on. further, we want to run data transfer from multiple servers in paralleled to save time.
Ques; can "both" files the db backup (.bak) file "and" the (.txt)report file in a maint plan object be cleaned up at same time?
The object is working ok but trying to setup and match backup text reports to the db, I have way more .bak files than text files.
Created from/after db maint plan (which is working ok) from/in the object Maintenance Cleanup Task and the object selections are
Delete files of the following type: _backup files _maintenance plan text reports
File location: _delte specific file File name: __ _search folder and delete files based on an extension Folder:__ File extension:__ File age: _Delete files based on the age of the file at task run time... delete files oder than the following. . . .
I've noticed that wheter if you put the condition on files' age or not it generates exactly the same T-SQL statement, it says that the actual one could be different if you set conditions on the task, but if it is so why they work the saem way?
I am using Ola Hallegren's scripts to do backups. He uses @Cleanup Time to delete backups older than a certain number of hours. My situation is I do a full backup of a database on Sunday and then I have a few Differentials and then log backups for the rest of the week. When Sunday rolls around again and my full backup is finished, I would like to delete all the differential backups and log backups. Any way that I could accomplish this using Ola's scripts?
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
In my maintenance plan task I have added a final step, an execute T-SQL statement, the code is simple, just a truncate of the database log to free space in the server, the code I am using is this on:
use NAV001 GO DBCC SHRINKFILE(NAV001_Log, 1) BACKUP LOG NAV001 WITH TRUNCATE_ONLY DBCC SHRINKFILE(NAV001_Log, 1)
The problem is that I get an error when the maintenance task reach this step, the error log says that:
DBCC SHRINKFILE(NAVISION_PRUEBAS_Log, 1)" failed with the following error: "Could not locate file 'NAV001_Log' for database 'master' in sys.database_files. The file either does not exist, or was dropped.
Why is it trying to use master database if I am telling sql server to "use NAV001" in the first sentence?
I deleted a database referenced in a maint. task and then I tried to delete the maint. task. I am getting an error "Exception has been thrown by the target of an invocation" and also it says Named Pipes Provider:, error 40: Could not open a connection to SQL , error 53). I think it has something to do with deleting Maint. Tasks outside of SSIS. Does anyone know of a solution?
Ever since upgrading SQL Server 2005 to SP2 build 3050 all my maintenance plans which begin with a Check Database Integrity Task are failing! If I remove the task from the plan they all execute properly. If I run the T-SQL equivalent manually it executes properly.
I upgraded the server to build 3054 and rebooted several times and Im still having no luck. What am I doing wrong? Thanks for your help with this. -Kenny
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 have inherited a server on which is a maintenance plan with two subplans on different schedules. In each subplan there are Execute T-SQL tasks with scripts for index rebuilds. Each task is set up with a Completion arrow to the next task and a Failure arrow to a Notify Operator Task. I was asked to add a task for index rebuilds to a specific subplan for a specific database, which is what the other tasks also do. I discovered that my task was failing but the others were fine. No notification was sent about my task failing even though the job is marked in MSDB as a failed job. I have sent a test email using the "Send Test Email..." option when right clicking Database Mail in SSMSand I receive an email so I know Database Mail works.
I set up a test job to model the index job that I can't get notifications from. I have two T-SQL tasks that just select the top row from a small table. The first task has a syntax error that I did so it would fail. I have a failure arrow to a Notify Operator Task and a Completion arrow to another T-SQL task with no syntax error which has a Success arrow to a Notify Operator task. As expected, when I execute this job I receive one failure email and one success email.
The only other troubleshooting step I know to try is to add a Notify Operator task before my failing task. That Notify Operator task will hopefully fire to tell me that the previous step was successful. I am not having problems with the other steps so I was just thinking I would try to get the subplan to send me a success email about one of the steps that has been working fine.
Created a maintenance plan to backup my sharepoint databases. When I execute it the following error occurs: Execution failed. See the maintenance plan and SQL Server Agent job history logs for details: Additional Information: Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI) - Execute maintenance plan. SharePointBackUP (Error) Messages * Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
When checking the Maintenance PlansharePointBackUP log it is empty! Under Job History I thinks this: Date 4/18/2008 12:55:35 PM Log Job History (SharePointBackUP.Backup_SharePoint) Step ID 1 Server DESD7 Job Name SharePointBackUP.Backup_SharePoint Step Name Backup_SharePoint Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed. line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error. Under SQL Agent there are no entries! So what bloody log am I suppose to be checking?! This is very frustrating. I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.
An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.
I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild reorg weekly, but do people also do update stats nightly?
I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently.
I created a maintenance plan with full user database backups, backup files cleanup, and database shrink.
I did it in SSMS and it also created "Reporting Task for subplan-{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}".
It works fine in the development server where I created the plan. I imported the same into other environments test, stage, and prod. I did use BIDS to change the connection string manual "Local server connection". The job runs fine in other environments but it fails at the end where the "Reporting Task for subplan" is executed. I looked at the dtsx file codes and I could not find anything wrong.
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.
How should I go about releasing any objects I've created? I've got a very complex CLR stored proc that creates a dataset, many sqlcommand objects, an adapter, etc, etc. It's used to evaluate high school transcripts for completion of the core subjects. It's a very complex SP that takes a single SSN as a parameter. Right now it works as expected when I call it from a query window in SQL Mgmt Studio, I can stack 39 calls in a row like:
exec usp_Evaluate_eTranscript '011...' exec usp_Evaluate_eTranscript '012...' ... exec usp_Evaluate_eTranscript '039...' And if I build a little test harness in VB.Net calling it in a populated dataset For ... Next loop, it works just fine for the 39 valid values I have. But, when I build a cursor in a query window in MgmtStudio I get some strange behavior, that just doesn't really make sense. I'm getting a foreign key violation and I'm not even trying to insert any rows, just updating existing ones. So, the only possible cause that I haven't eliminated yet is that some objects are still hanging around when the cursor tries to call the SP again. It's declared:
<Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub usp_Evaluate_eTranscript(ByVal SSN As String) And exits with the standard End Sub. Is there some way I can help the garbage collector along? Kick it into action? Maybe some
Set sqlcommand = Nothing or sqlcommand.Dispose dataset.Clear or dataset.Dispose or somesuch thing? I'm closing the context connection immediately after each time I use it (I have to so the next object can work).
Hey gang, I have 2 100,000 record tables, with a ID & address field. (I've exclude all the other field for now) I need to make sure the ID field in table A is equal to table B and that the address in table A is equal to table B. If the Id's or address do not match create a separate table. Any good ideas
