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
ADVERTISEMENT
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
Mar 31, 2008
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?
thanks
View 4 Replies
View Related
Oct 10, 2006
I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.
I keep getting error:
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
ADDITIONAL INFORMATION:
The data type of variable "User::varDataset" is not supported in an expression.
Reading the variable "User::varDataset" failed with error code 0xC00470D0.
(Microsoft.DataTransformationServices.Controls)
I need the flexibilty to be able to control the Task behaviour during runtime through Variables.
Any suggestions .... or even a different approach will be helpful..
cheers
aigbor
View 1 Replies
View Related
Nov 6, 2015
I am looking to create a Stored Procedure that simply runs a Scheduled Task that they knows when the job has completed.
To call the Job I am using:
EXEC msdb.dbo.sp_start_job @job_name='MY_JOB_NAME'
As a next step is there a way to check if the Scheduled Job has completed and keep checking until the job completes via a query?
View 1 Replies
View Related
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
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
Jul 1, 2014
Under the maintenance plan, i run full backup at the same path and it was successful. The maintenance plan for selected database was run success.
BUT when i want to write the backup report "Write a report to a text file" i receive this error in maintenance plan "view history"
Access to the path 'F:MSSQL11.APPSPRDMSSQLBackupBackup LogCARSCARS DB Backup_Subplan_1_20140701200011.txt' is denied.
Basically i am using defaul NT service for SQL Agent
View 3 Replies
View Related
Oct 7, 2014
Server: SQL Server 2012 Enterprise 64bit
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.
It seems to be a bug at the Microsoft side.
View 4 Replies
View Related
Apr 29, 2015
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.
View 1 Replies
View Related
Apr 18, 2008
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.
------------------------------
ADDITIONAL INFORMATION:
Job 'SharePointBackUP.Backup_SharePoint' failed. (SqlManagerUI)
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?
View 5 Replies
View Related
Aug 23, 2015
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.
View 2 Replies
View Related
Jun 26, 2007
Hello,
I have done a search and have read some of the posts, but am left more confused than before. I am fairly new to SSIS. Here is my situation and what i am trying to accomplish.
I have a package that has a sequence container, in which there are multiple SQL tasks (about 20) running in parallel. I have checkpoints enabled, and FailPackageOnFailure enabled as well. If the package fails, when i re-run the package it will run the last task as well as all the other tasks. What I am looking to accomplish is when the package is re-run, have the SQL tasks that failed ran and not the previous successful tasks.
I think the best way would be via disabling tasks on successful completion of a task, where it writes the name of the SQL task to a temp table, but I am skeptical.
Can anyone point me in a direction to help me accomplish what I am looking for please.
Thanks in Advance.
View 4 Replies
View Related
Mar 29, 2006
I have three SQL tasks executing in parallel in an Integration Services package.
+-B-+
A-+-C-+-E
+-D-+
It starts with task A; then B, C, and D all execute in parallel; and finally task E runs after BCD are done.
B, C, and D are all Execute SQL tasks, all with the same connection manager. Here is their code:
B) SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process
FROM temp_B
C) SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process
FROM temp_C
D) SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process
FROM temp_D
Each one is setting a binary value to a package variable (using Result Set settings) based on the count of records from different tables.
This works with no problems when I run it against one server (development). But when I switch to the production server, task B and D both fail. I'v checked to make sure all of the temp tables exist in the database for that connection manager and that all three have the same connection manager - all is okay.
Here's the trickier part. When I'm still pointing to the production server and I run these tasks individually, they are all successful. It is only when they are attempting to run in parallel that they fail.
Here is the Output error:
Error: 0xC002F210 at Process Med?, Execute SQL Task: Executing the query "SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process FROM temp_B" failed with the following error: "Invalid object name 'temp_B'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What could be causing this? I'm at a loss.
View 5 Replies
View Related
Oct 24, 2006
Hi,
I have a master package with a sequence container with around 10 execute package tasks (for child packages), all in parallel. Checkpoints has been enabled in the master package. For the execute package tasks FailParentOnFailure is set to true and for the sequence container FailPackageOnFailure is set to true.
The problem i am facing is as follows. One of the parallel tasks fails and at the time of failure some of the parallel tasks (say set S1) are completed succesfully and few are still in execution (say set S2) which eventually complete successfully. The container fails after all the tasks complete execution and fails the package. When the package is restarted the task which failed is not executed, but the tasks in set S2 are executed.
If FailPackageOnFailure is set to true and whatever be the FailParentOnFailure value for the execute package task, in case of restart the failed package is executed but the tasks in set S2 are also executed.
Please let me know if there is any setting that only the failed task executes on restart.
Thanks in advance
View 1 Replies
View Related
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
Aug 23, 2006
Hi
I have a SQL Server 2000 instance running on a Windows Server 2003 box with 4 processors. SQL Server is configured to use all 4 processors, and use all available processors for parallelism.
I have created a simple DTS package which has 2 "execute external process" tasks with no precedence constraints between them. There are no connections required or defined for the two tasks (sequential
processing is forced on tasks sharing connections). The DTS package
properties have the "limit the number of tasks to execute in parallel"
set to 4.
However, despite the above configuration, the two steps are never executed in parallel, but always sequentially.
Does anyone have any ideas as to why these tasks are not being executed in parallel?
Any suggestions welcome.
Thanks.
View 2 Replies
View Related
Jan 19, 2008
I've create a package that currently uses 5 DataFlow tasks connected in series to get data from 5 different files and place that information into 5 different temp tables. Each Dataflow task contains only a OLE Source, a row count and a OLE destination. My question is - Is it normal practise to keep each of these separate, or should I put them all into a single DataFlow? The package should only continue if all five dataflow task complete successfully.
View 7 Replies
View Related
May 28, 2007
Hi,
I have a problem with the task "event watcher".
I've made a query like the one in msdn (SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name="e:\\temp""). I have 20 similar tasks for watching in different folders, but when there are too much tasks in parallel, it doesn't work anymore. I change the numbers of executables to 128 (in the general properties of the package (to test)) but it doesn't seems to work.
I don't understand why it works when there are only 1 or 2 (6 seems to be the maximum) tasks and not if there are more than 6.
Could you help me with this issue?
Configuration : Windows Server 2003, SQL Server 2005, SSIS, Sql Server Agent
Thanks a lot.
Julien.
View 3 Replies
View Related
Aug 17, 2007
Hello,
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.
Thanks.
View 1 Replies
View Related
Mar 5, 2015
I have been trying to the query optimizer to generate a parallel execution plan but no matter the MaxDOP (0) or Cost Threshold (5) settings I use it will only execute in serial.
UPDATE [dbo].[Targus_201412_V7_B]
SET [URBAN] =(
CASE
WHEN [METRO_STATUS] = 'Urban' THEN 1
ELSE 0
END)
View 9 Replies
View Related
Jul 13, 2015
I’ve been looking at a bug in an application stored proc. This merely inserts 7 million rows from one table into another (empty) table.
INSERT
dbo.TradeDataPrimary
(Id, SinaiTrade)
SELECT
ts.Id,
CAST(ts.XmlTrade
AS varbinary(MAX))
FROM dbo.TradePrimary ts
Both tables have a clustered index with the same key order (on the first column) but the query optimizer insisted on placing a SORT step in the query plan. This use a lot of tempdb which I wanted to avoid.
I discovered that the bug was a data type mismatch. Fixing the bug and/or dropping the clustered index from the target table before the insert resulted in the plan I expected, that is, with no sort.
I was hoping it would run faster but, unfortunately, as can be seen from the new plan below, a serial plan is used. This results in the insert taking nearly three times as long as the original.
I’ve been looking at Paul White’s article: [URL] ....
and Adam Machanic’s: [URL] ....
Both are excellent articles but neither approaches i.e. trace flag 8649 or make_parallel () give me a parallel plan. Am I missing a trick here? How I can force parallelism? I know there are no features that require a serial zone in the plan otherwise the plan with the sort would not be parallel..
This is SQL Server 2012 Enterprise 11.0.5522.0, 512 GB of RAM and 16 procs.
View 10 Replies
View Related
Jul 2, 2015
Is it possible to do? I'm getting lock violations in I try to execute several tasks in parallel.
View 4 Replies
View Related
Oct 9, 2007
Hi There
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?
View 6 Replies
View Related
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
Mar 5, 2001
I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan.
Thanks!
View 1 Replies
View Related
Oct 23, 2001
Hello All
I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.
I would be very grateful for any ideas
View 1 Replies
View Related
Aug 31, 2000
I have a strange thing in one of our Maintenance plans.
On the first tab where you check which databases you're including in the plan I have (say my database name is CAT) a 'CAT' and 'cat' database listed and the one chosen is 'cat'. However my database in all other views shows up in all caps. (even when I do an sp_helpdb)
The backups look like they're working, etc. but it just seems weird. If I go to create a new plan it only gives me the one option 'CAT' which is really what's there. I'm new and I'm thinking the database at one time was 'cat' and this is when the maintenance plan was created. Then it was renamed to 'CAT' and there's the two db's showing in the old mainenance plan.
What would you do? Create a new plan with "CAT" and just get rid of the old one with the weird 'cat' and 'CAT'?
Any other suggestions or ideas on what happened..
ann
View 1 Replies
View Related
Oct 4, 1999
I've created a database maintenance plan to backup a database, but it just
isn't happening, am i missing something. The maintenance plan appears to be
created successfully.
responses appreciated.
thanks
Todd Minifie
View 6 Replies
View Related
Oct 1, 2004
Can you generate script for a maintenance plan?
I know how to script a job, I was wondering about a plan.
If not, whats the best way to record the configuration?
Thanks
Lystra
View 3 Replies
View Related
Apr 7, 2008
hi everyone..
this is a little bit weird ..
i am trying to make a backup strategy. i am using sql2005.
when i go to maintenance plan. right click >> new maintenance plan...
nothing happens..
if i go with the maintenance plan wizard everything goes normally.
after doing the backup, if i right click on it and press
modify , nothing happens too.
what i mean by nothing happens is that it doesn't open the "design view".
the back up is doing normally.. but i need to set a range of 5 days before overwriting the oldest backup.
any idea what is going on or what am i missing?!
thank you
View 2 Replies
View Related
Apr 9, 2008
Windows Server 2003R2 w/ Sp2
SQL 2005 w/ Sp2
Created weekly (full backup) and a daily (differential backup) Maintenance Plans using the wizard. I formatted the server, installed the OS and SQL. Restored the full backup (No Recovery Mode), then restored the differential backup (Recovery Mode), tested and all worked well.
Then I noticed the original Maintenace Plans I created (Full and Differential) were gone; makes sense as I had formatted the server.
Is there a way to create a Maintenance Plan file or script that I can save and just add back to the server??
Hope that makes sense.
Any help appreciated.
Kerry
View 3 Replies
View Related
Feb 26, 2006
Hello 2 all,
Could someone advise and/or correct me with my thoughts on how I would do my db maintenance plans?
(db's on SQL2000 as 'full' model)
Backups:
1) Daily Transaction log backups scheduled frequently enough.
2) Full Backup scheduled daily. Good way to start I presume ;)
Maintenance: Would be scheduled daily if possible, on non-production hours and if not colliding with daily full BU schedule.
3) Full DB reorg data&indexes.
4) Update Query Optimizer Stats (although 'Auto Update Stats' is on)
5) Shrink the logfile (ldf) as I presume this will have grown due to previous maintenance jobs.
6) If 5 ok, alter ldf filesize back to new allocated size.
Rgds,
T.
View 6 Replies
View Related
