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.
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?
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?
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?
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.
I have just done my first clean install of SQL 2005. I have previously upgraded several boxes from SQL 2000 without incident.
The clean install seemed to go fine but the Maintenance Tasks didn't install? I have looked on the web but have been unable to unearth any information. Has anyone encountered this before and if so I how do I add Maintenance Tasks to the Management folder?
in Management Studio, when i click on MODIFY a maintenance plan, the first thing I get is an alert "the diagram layout information was rebuilt" - "cannot set the default layout style of the diagram to 'microsoft.datawarehouse.layout.graphlayout', which i simply ignore, according the the limited info i was able to dig up on this alert.
my question is related to the color of the connectors between tasks. when i use the wizard to create a maintenance plan the connectors are blue, but when i add a task to an existing plan (by dragging an dropping from the available tasks into the diagram) and then choose "add precendence constraint" from the last task to the newly added task, the connector is green. WHY is that?
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.
Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015
Running Cleanuphistory Procedure On Server:
NO of records are qualifying for deletion:0
Deleted Sysmail MAil Items, Old history: 1 Deleted Sysmail Log Old history: 1 0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??] Deleted SQL JOB Old HISTORY: 1
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
I would like to run several checklists against each sql server 2005 instance.
I can get a list of the sql server 2005 machines (Which are on the network) from a sql server table.
There is a another table which holds the list of the checks that need to be carried out against each sql server 2005 machine called tblCheckList.
The list of tasks in the table is briefly as follows:
1) get the path where the .mdf file is placed. 2) get the path where the .ldf file is placed 3) get the path where .BAK or .TRN are placed 4) get the path where the sql 2005 is installed 5) get the servicepack version 6) get the authentication 7) remove sample databases i.e. northwind or pubs, etc... 8) get the recovery model on UserDB 9) get the size of each database
1) Is there a way to call a second task 'upon completion' of the first one. Or would I need to place the code of the second task in one task? (This is v6.5) I guess the main purpose is to avoid conflict & see if one fails, the other may succeed, etc. I generally like to keep diff tasks separate, but one is dependent upon the completion of the other.
2) I want to restore a db, upon completion of the backup of another db. They are on separate servers, but i know i can use remote sp's.
Any advice, help, code, scripts, etc, would be appreciated. Thank you.
We have SQL SERVER 2000 Runnin on our server. We are trying to do a db cleanup, ie all tbls/views that were created earlier and are not needed any more, need to be deleted. However, is there a way, to do a cleanup in a better method, other than going thru the whole bunch of tbls/views manually,and determining which ones are needed or not and delete the ones that are not needed.
I want to schedule a task that checks if a file(s) exists on d:Acc and then run the sql script shown below i.e. with the file(s) to load the content of the file(s) every day new files will be loaded. after the file(s) are loaded the file(s) are deleted.
I currently can achieve the above using Java and sql server and running the program as a scheduled task... (only reason for change would be that i wouldnt require java, JDBC etc to run the software it would be easy manage, migrate etc)
I wanted to find out if it can be done with sql server directly? (i.e. check if file exist and ased on that carryout the task or other wise just ignore) What do i need to look for? does sql server have a procedural language in it to be used for such thing? does sql server have an inbuilt task scheduler?
I am currently using the following script in a java program please note that i have used fixed values in this script for readabliity (of what i am doing)..
@bulk_cmd varchar(1000) SET @bulk_cmd = 'BULK INSERT AccInv..VW_AccInvoice FROM ''d:Invbatch2001.txt'' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''+CHAR(10)+''')' EXEC(@bulk_cmd)
I have an issue that hopefully someone can help me with. We have SQL server running in a 64 bit environment. We are running SQL Server 2005 SP2. The problem I am experiencing is that my SSIS script components produce the following error when the SSIS package is executed from the server.
Code: 0x00000003 Source: Set Perspective Connection Description: Precompiled script failed to load. The computer that is running the Script Task does not have a compiler capable of recompiling the script. To recompile the script, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).
I have reviewed this article and everything points to SP2 as being the fix. Being that I am running SP2 there is something else going on. I was curious to see if anyone else has run into this issue with SP2.
Which sp code part is efficient? 1st code from sp_MSdistribution_cleanup. Used for Replication Cleanup job. For 1500 rows, runs a day. Is something wrong with this code? 2nd code part is an alternative idea!. /************************************************** *****************/ delete MSsubscriber_status from MSsubscriber_status ss1 where publisher_id = @publisher_id and publisher_db = @publisher_db and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db and job_id < @max_cleanup_job and job_id <> (select max(job_id) from MSsubscriber_status ss2 (index = ucMSsubscriber_status) where ss2.publisher_id = @publisher_id and ss2.publisher_db = @publisher_db and ss2.subscriber_id = @subscriber_id and ss2.subscriber_db = @subscriber_db) /************************************************** *******************/ select @maxCleanup_job = max(job_id) from MSsubscriber_status (index = ucMSsubscriber_status) where publisher_id = @publisher_id and publisher_db = @publisher_db and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db delete MSsubscriber_status from MSsubscriber_status ss1 where publisher_id = @publisher_id and publisher_db = @publisher_db and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db and job_id < @max_cleanup_job and job_id <> @maxCleanup_job
I need to restore a DB but it was prevented by a background process of "Ghost Cleanup". server is SQL2000 ENT. sp4.
It could not be killed, neither it was stoped after restart the server. Is there a way to change its running schedule and/or to kill it when I need to restore the db?
I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data.
The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves.
This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources.
My question is, how can I increase performance? Should I add 'commit' after deleting data in each table? Would it help to make it a stored procedure?
Script looks kinda like this:
SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules)
DELETE FROM price_product WHERE priceId IN ... #tobedeleted .... ....
I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.
Because when I use peer to peer replication the @allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.
Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?
Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)
I have two servers, A for dev and B for production.
On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.
All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.
On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.
The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.
Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.
Can anyone help with this as my deployment to live isn't going very well to say the least!
Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.
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 have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner? Thanks in advance..
sysmail_mailitems table in msdb stores data for emails sent using database mail. The DB was growing and I found sysmail_mailitems table is taking almost 85% space. What is the procedure to clean up data in this table. I did dirty way of removing FK's for the table, truncating data and re-establishing FK relationships.
Is there some way or settings somewhere to schedule cleanup on this table?