March 25, 2009 at 1:25 pm
Here is my maintenance task on sql 2005 EE server
1)backup all user databases
2)backup log for all user databases
3)reorganize index
4)rebuild index
5)update statistics
6)cleanup backup history task
this job used to run fine for all adtabases. Then one day we put new database on that server and that database was acting as a mirror database. After that day I was observing maint plan history and I was able to see that my step-3,4 and 5 are not performed and this is happening now daily since last 15 days
I understand it should not take backup of that mirrored database so step-1 and 2 may fail
Also step 3,4 and 5 fails for mirrored database but it should work for all other databases..and its not.
Also between 2 boxes of ssis package, I m using on completion(blue line) so on completion of task 1 it should go to next task.
Can anyone tell me why this is happening?
March 25, 2009 at 1:29 pm
On your SSIS package, what is your MaxErrors setting for the package. We had a problem like this because if the package itself throws an error, it fill send a failure to the job.
March 25, 2009 at 1:38 pm
I can see these settings
Fail Package on Failure =False (package fails on step-1 and 2 but still goes to next step)
Max ErrorCount =1
So according to u I need to increase max error count and that can solve the issue right? What is the max range for max error count?
March 25, 2009 at 1:43 pm
I am not POSITIVE that would work. I was just trying to give you a direction to possibly look. All of our maintenance plans are through SQL Agent jobs and uis meta data driven. There is no SSIS involved. I just came across this in a past client extraction project where we set MaxErrors to 99999 to keep it going when some of the items might have or were expected to fail.
I would personally not do anything to a mirrored database. If you do maintenance on the prioncipal, those changes would propogate to the mirror through the mirroring technology since it is directly related to events in the tran log.
Our mirrored databases are in recovery status as you know and cannot be accessed, shrunk, nothing, BUT all changes made to the principal are also applied to that mirror
March 25, 2009 at 1:44 pm
Running these for all databases doesn't make sense if you have a mirror there. Instead you'll want to exclude these items. You should consider specifying databases for this maintenance items.
March 25, 2009 at 1:51 pm
Well thanks for ur replies but
Books online says
MaximumErrorCount The maximum number of errors that can occur before a package stops running. The default value of this property is 1.
In my case my package is not failing but it only skips few steps. So m not sure abt increasing maxerrorcount thing
Also I can exclude my mirror database but what if failover occur some day then I have to make changes in maint plan manually to take its backup and run rebuild tasks on it…So I want to avoid that manual thing..
I can see log files for each steps but there is no any error messages inside
March 25, 2009 at 2:11 pm
dallas13 (3/25/2009)
Here is my maintenance task on sql 2005 EE server1)backup all user databases
2)backup log for all user databases
3)reorganize index
4)rebuild index
5)update statistics
6)cleanup backup history task
First issue - you have the option selected to backup all user databases. Now that you have databases that are mirrors which cannot be backed up, the maintenance plan is failing. Modify the plan and select only the databases that you want backed up.
Second, backing up the transaction log in this plan is not correct. You should have a second sub-plan or maintenance plan that backs up the transaction logs on a regular basis. This should be at least every hour, but is really dependent upon your business model. Review the article in my signature about managing transaction logs for further information.
Third, you don't need to reorganize and rebuild your indexes. Perform one or the other - not both. If you choose to reorganize the indexes - then you will need the step to update statistics, if you rebuild the indexes the statistics are updated when the index is rebuilt.
There are various scripts available on this site - and others where you can perform a smart re-indexing scheme. They all generally do the same thing where only those indexes needing to be reindexed are selected. I would recommend reviewing those scripts and using one of those instead.
And finally, the task you have to cleanup history will not remove old backup files. You need to add two new tasks. One to remove old backup files (Maintenance Cleanup Task - setup to remove 'bak' files) and one to remove old transaction log backup files (Maintenance Cleanup Task - setup to remove 'trn' files).
In every single task - you need to make sure you select the databases individually. Using the option for all user databases will cause the tasks to fail when they encounter a database that is not online for whatever reason.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2009 at 2:26 pm
Wow that’s a nice description. I will correct all things but
If u consider my scenario(I know its not good) but it used to work fine until now.
The question is why its skipping some tasks…
And still if I select databases individually then I am not able to select a database which is acting as a partner right now.Tomorrow when failover occurs then I need to manually change my job to include that database. Right??
March 25, 2009 at 2:46 pm
Yes - on a failover you would have to manually configure those tasks to include the partner database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2009 at 6:46 pm
dallas13 (3/25/2009)
Here is my maintenance task on sql 2005 EE server1)backup all user databases
2)backup log for all user databases
3)reorganize index
4)rebuild index
5)update statistics
6)cleanup backup history task
this job used to run fine for all adtabases. Then one day we put new database on that server and that database was acting as a mirror database. After that day I was observing maint plan history and I was able to see that my step-3,4 and 5 are not performed and this is happening now daily since last 15 days
I understand it should not take backup of that mirrored database so step-1 and 2 may fail
Also step 3,4 and 5 fails for mirrored database but it should work for all other databases..and its not.
Also between 2 boxes of ssis package, I m using on completion(blue line) so on completion of task 1 it should go to next task.
Can anyone tell me why this is happening?
Or you could do this way:
-Identify indexes with fragmentation
-Defrag the indexes that has lower fragmentation
-Update Statistics for those indexes which lower fragementation
-Rebuild indexes with higher fragmentation
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply