MS SQL Server :: Missing Option In Maintenance Cleanup Task

I Hate The Maintenance CleanUp Task :=)

May 4, 2007

I have a backup database task that runs at 4 am.

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?

View 10 Replies View Related

SQL 2012 :: Maintenance Cleanup Task Not Cleaning Up?

Jul 8, 2015

I am running a maintenance cleanup task as shown below but despite the log saying "success" it is not successful as nothing is cleaned up.

Maintenance Cleanup Task (VPS20604)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2015-07-08T16:14:24.
Task end: 2015-07-08T16:14:24.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''C:SQLBkp'',N'''',N''2015-06-10T16:14:24''
GO

View 6 Replies View Related

Remove Old Backup And Tran Logs Thru Maintenance Plan Cleanup Task

Mar 27, 2007

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.

EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'bak',N'2007-03-26T22:21:14',1

But is there a way to delete both bak and trn files using one task ?

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

Maintenance Cleanup Task……

Sep 14, 2007

SQL Server 2005 Standard Edition
Windows Server 2003.

I have created an ��Backup Database Task�� to create the database backups and checked the option ��Create a subdirectory for each database�?.

When I add the ��Maintenance Cleanup Task�? I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the ��Create a subdirectory for each database�? option to create the backup files in one location.

Thank you,
Smith

View 10 Replies View Related

In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing

Apr 27, 2008

Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.

View 2 Replies View Related

1. Maintenance Cleanup. 2. 'View History' Anomaly

Mar 31, 2008

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?

Thanks,

Ed

View 4 Replies View Related

Problem With SQL Server Maintenance Cleanup Tasks

Nov 2, 2007

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.

Any clues?

charles@at@borner.us

View 1 Replies View Related

1. Maintenance Cleanup. 2. 'View History' Anomaly

Mar 31, 2008

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?

Thanks,

Ed

View 4 Replies View Related

Publication Retention To Increase, How To Avoid Missing Metadata After Cleanup

Mar 29, 2007

I currently use 7 days for subscription expiration setting for my two merge publications, which will cause metadata to clean up very 7 days. Now I need to increase the retention period to be 14 days. How I can avoid missing metadata after cleanup? Microsoft ms151188 (http://msdn2.microsoft.com/en-us/library/ms151188.aspx) warns that publisher may not have enough metadata, which may lead to non-convergence. I want to change this setting without causing any data loss.

Thanks much,

View 1 Replies View Related

History Cleanup Task

Feb 11, 2008

I have set up a history cleanup task to be performed once per week, however it doesn't seem to delete the old files, what am I doing wrong?

View 8 Replies View Related

Maint Cleanup Task

Mar 19, 2007

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

View 1 Replies View Related

Cleanup Task Deleting Everything - T-SQL

Apr 24, 2007

Hi all,

I created a maintenance plan with a cleanup task to delete bak files older than 4 days, but it keeps deleting everything but the current day files.

This is the generated T-SQL:

EXECUTE master.dbo.xp_delete_file 0,N'F:SQLDBBACKUP',N'BAK',N'2007-04-20T11:59:30',1

Someone can tell what's wrong with it?

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?

Thanks!

Cheers,

Giovanni

View 4 Replies View Related

DTS- Excel Option Missing

Mar 1, 2004

I am a senior DBA using SQL Server 2000.

I just did a clean install of SQL 2000 Personal edition. I am trying to DTS an excel file in but the excel icon is not there!!!!! There are the other excel ODBC sources but they each require me to setup a DSN (not normal!!!)

Anyone had this problem before? I am uninstalling and reinstalling.

-Kevin

View 6 Replies View Related

The Export Option Is Missing

Dec 9, 2007

Hi everyone,

I am running Win 2003 Ent. and SQL 2005 Ent. SP2.

I used Management Studio to copy a database from one server to another. It created a DTS package, which I can see when I log in to Integration Services through MS. However, when I right click on the name of the package, I don't see an export option. I am a member of the sysadmin group. Any idea why the option is not there and how to fix it? Thanks in advance for your help.

View 1 Replies View Related

SSIS Import Option Missing

Jan 31, 2008

I want to start the SSIS wizard to import data from a flat file, but I right click the DB > select Task and the Import and Export options are not there. Is there something I have to enable before the options show up

Thanks

View 3 Replies View Related

Save Copy Of Package Option Missing

Mar 5, 2008

On my workstation the option is there, but on a couple other workstations that option is not available from the file menu. I tested doing the exact same thing and the option just isn't there. Anyone have any idea's?

View 3 Replies View Related

Generate Scripts Missing Option In Management Studio

Jun 13, 2006

I was hoping this would be fixed in SP1, but it is still missing. Particularly in a development environment, I find it very useful to have scripts DROP first, then do a CREATE for tables. There is an option called Script Behavior that is supposed to allow this, according to the help text. There are supposed to be 3 options, DROP only, CREATE only and DROP + CREATE. This third option (the one I need) is missing. It does not show up on the drop down.

Sorry, I don't want to be rude, but before anyone suggests it, yes, I know I can create a DROP script first and then go back in and create a CREATE script and then run both. And I don't want to change my methodology, and I don't want to do twice the work. All I would like to know is if anyone knows how to get this 3rd option to appear, or if its a known bug, is there a hotfix or a plan to fix it soon.

Thanks,

Wayne

View 2 Replies View Related

Maintenance Plan Options Missing

Jul 10, 2007

Hi,

I have installed SQL2005 enterprise edition default installation with SP2 to migrate over SQL7 instances. Installation was fine and migration was smooth. After completion, i tried to access the Maintenance tab but when i right-clicked, my only option there was refresh. There was nothing under the maintenance tab for me to create a new plan.

Is this problem anything to do with SP2. Anyone else had this similar problem? Do i have to install integration services too?

Regards,

Siva

View 3 Replies View Related

Maintenance Plan Wizard Missing

Jul 9, 2007

Under Management, all I have is SQL Server Logs and Activity monitor, no option to create a maintenance plan or run the wizard ??

View 4 Replies View Related

Sql Server 2005 Maintenance Plans Folder Missing

Aug 10, 2007

I've seen a few threads on this which seem to suppose the problem is on Express edition rather than Standard edition. Not so here.
This is definitely Standard edition, SP3 as seen by running
SELECT CONVERT(char(20), SERVERPROPERTY('Edition'))
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
In SSMS
Under the Management folder there is no "Maintenance Plans" folder.
There IS a "Maintenance Plans" folder under the Legacy Folder.
I have another instance which is Standard and SP1 which looks just fine.

View 9 Replies View Related

Flat File Source Option Missing In The Sql Server Import && Export Wizard.

Jun 21, 2007

Hi All,

I want to import a txt file data to a sql server database table, to do this i used sql server import and export wizard. In this when we choose a Data Source, the option Flat file source is not coming up in the combo box in the wizard.

I am using sql server 2005, Management Studio to do this.

steps 1. right click on the database --> all tasks --> import data --> sql server import export wizard --> choose data source dialog box....

please help me.

thanks in advance.

View 1 Replies View Related

Reporting Services :: Email Option Is Missing In Manage Subscription (SSRS 2012)

Aug 29, 2015

In the Project Web Access (PWA 2010), I have created Report Library which contains .rdl files --> Manage Subscription --> Add Subscription --> Email subscription is missing

Did i missed out some thing but other email functionalities like notifications, Approval workflow are working fine so no issue with SMTP.

View 4 Replies View Related

Truncate Log As Maintenance Task

Jun 23, 2008

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?

View 8 Replies View Related

To Configure Database Mirroring-Dont Get Mirror Option In TASK

Jul 7, 2006

To configure database mirroring

After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.

Expand Databases, and select the database to be mirrored.

Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

To begin configuring mirroring, click the Configure Security button to launch the Configure Database Mirroring Security Wizard.

but i do not see any "Mirror" Option in the "Tasks" menu.

Why ?

View 1 Replies View Related

Cannot Delete A Maintenance Task In Mgmt Studio

Mar 25, 2008

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?

Thank you.

View 20 Replies View Related

Check DB Integrity Task Fails In Maintenance Plan

Apr 26, 2007

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

View 7 Replies View Related

FTP Task For SSIS Missing

Dec 22, 2006

Hi Everyone,

When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.

Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.

Thanks

View 1 Replies View Related

SQL 2012 :: Maintenance Plan - Show Successful Even If A Task Fails?

Apr 3, 2014

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.

View 1 Replies View Related

SQL 2012 :: Maintenance Plan Task Only After Completion Of Other Parallel Tasks

Dec 11, 2014

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.

View 0 Replies View Related

Sql Server 2005 Maintenance Task Delete Files Does Not Work

Mar 24, 2008

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.

View 6 Replies View Related

Missing Dataflow Task? ( Throw Execption)

Dec 27, 2007

Maybe I am mistaken ( most likely ). But I am missing a dataflow task, which would do something similar like Throw Execption.

The project I am currently working on needs to validate a lot of different data. And sometimes incorrect data ( corrupted, incomplete or unexepected ) is coming from the source system. In case of this I need to trigger an error and discard the complete row or batch depending on the situation.

For example:
In our source system we have some flag fields. And certain combinations of flags are not logical (business rules) but the system allows data to be inputted in this unlogical ways. And because we are creating the system I am expecting some combinations flags which are allowed but not properly defined in the specification.

So when I use a conditional split, I want the default output to trigger an error instead of an output.

View 10 Replies View Related