October 7, 2014 at 9:16 am
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. Have anyone experienced the same problem?
Appreciate your help.
Thanks.
October 7, 2014 at 10:45 am
We don't use maintenance plans where I work but if you can provide an error that may help.
Another recommendation, don't use the shrink step unless you have a specific reason for it. What frequently ends up happening is the database is shrunk which fragments indexes, the reindex runs which causes the DB to grow and some of that space ends up empty which allows the shrink to run and it just turns into a cycle. There are time where there was an unusual growth event and it makes sense to shrink the database but the only way it makes sense to shrink the database on a regular basis is if you're removing more data than you're putting into the database.
October 7, 2014 at 11:19 am
Chris, Thanks.
I had a doubt about that shrink file tasks and you have cleared it. Yes, I am going to remove it from the maintenance plan and research further how my databases will grow and shrink.
I think having custom backup solution is better than maintenance plan as when I uncheck "Ignore databases where the state is not online", it still fails when databases are offline.
On the maintenance plan the errors are as below.
Code: 0xC0024104
Source: Reporting Task for subplan-{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
Description: The Execute method on the task returned error code 0x80004003 (Object reference not set to an instance of an object.). The Execute method must succeed, and indicate the result using an "out" parameter.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
October 7, 2014 at 12:27 pm
I can't piece much together from that error message unfortunately. Hopefully someone that knows more about maintenance plans stops by. Another option, if you're not wedded to maintenance plans, is to use something like Ola Hallengren's scripts (https://ola.hallengren.com/) to do maintenance. It's what we use here and it works very well for us but it may not work well in all shops.
October 7, 2014 at 2:09 pm
Thanks again. I am looking through the scripts in https://ola.hallengren.com. Sure looks like a solution to my problem. We do have a enterprise solution for backups but the server I am working with is a standalone solution for a group. I was thinking maintenance plan would be sufficient but its got many problems to workaround.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply