Maintenance Plan

Question

Post reply

Maintenance Plan

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
January 17, 2010 at 12:53 pm

#137500

Hi Team,
We have created a maintenance plan for T-Log backups. From the last 2 days, its getting failed. We have checked the selected databases option. 3 days back we have deleted one of the database. From that day onwards this job getting failed.
But when we checked which databases selected in that Maintenance plan, we are unable to find the deleted database.
When we checked the history we got the message like "Unable to find the one of specified databases."
Please let me know how to resolve this issue.
Thanks and Regards!!
Thank You.
Regards,
Raghavender Chavva

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

rspskumar-704119 SSC Veteran Points: 232 More actions · Answer 1

Please delete and recreate maintplan and let me know the status.

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 2

I have done that only, but I need to know why that happened, is there any other resolution for that issue. Is this a bug in SQL Server 2005 ?

Thanks and Regards!!

Thank You.

Regards,
Raghavender Chavva

SQLbee Ten Centuries Points: 1009 More actions · Answer 3

You need to dig into the sql server logs and windows application logs as to why it was unable to find the database.

Maintenance plans are generally not recommended. Deploying a simple script instead is much better for efficient troubleshooting and management.

Regards

Akhil

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 4

I had a chance to repro the issue. It is certainly not a bug and also there is no need to create a new maintenance plan. Modify the existing plan and it will work.

Regards,
[font="Verdana"]Sqlfrenzy[/font]

OomBoom Hall of Fame Points: 3481 More actions · Answer 5

The reason for the maint plan not working is that the pointer to the deleted database is saved in the package and not automaticaly updated. As mentioned you can just open the maint plan and click safe. This should overwrite old package and thus remove the pointer to the deleted db.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 6

tvantonder-992012 (1/18/2010)
The reason for the maint plan not working is that the pointer to the deleted database is saved in the package and not automaticaly updated. As mentioned you can just open the maint plan and click safe. This should overwrite old package and thus remove the pointer to the deleted db.

We have tried to edit the package, by selecting all the databases which need to do T-Log backups, but that also wont work and failed with the same reason.

Thanks and Regards!!

Thank You.

Regards,
Raghavender Chavva

Brandie Tarvin SSC Guru Points: 173002 More actions · Answer 7

Caveat - I am using Litespeed for my backup maint. plans.

However, I have had the same issue. If I delete a database *before* altering the plan, the plan no longer works and I have to recreate it from scratch.

However, if I know I'm going to delete a database, then I go into the plan and remove said database from it *before* deleting the database, then the plan continues to work fine.

This was happening when I've used SQL Server SP1 + SP1 Hot Fix. I don't know if this happens in other SQL Server versions (i.e., SP2) as I've since changed my methodology and have not tried deleted dbs before altering the plan. Really, it's better to change your behavior to this anyway. Just for a little CYA.

It certainly can't hurt.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

OomBoom Hall of Fame Points: 3481 More actions · Answer 8

I have had the same problem that it fails but it was always intermediate. Some systems will safe correctly and some not. The systems that it worked on is sql 2005 sp2 and 2008 can not tell you if it was all. On the 2000 boxes I also had to recreate if I did not remove it beforehand.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Brad McGehee SSCertifiable Points: 5282 More actions · Answer 9

Maintenance Plans are far from perfect, as you have discoverd one of their many problems. On the other hand, if used are appropriately, they can be very useful. I have just written a free eBook about how to use Maintenance Plans that you might find useful. Check out: http://www.bradmcgehee.com/2010/01/free-ebook-on-sql-server-maintenance-plans-now-available/[/url].

Brad M. McGehee
DBA

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 10

Raghavender (1/17/2010)
Hi Team,
We have created a maintenance plan for T-Log backups. From the last 2 days, its getting failed. We have checked the selected databases option. 3 days back we have deleted one of the database. From that day onwards this job getting failed.
But when we checked which databases selected in that Maintenance plan, we are unable to find the deleted database.
When we checked the history we got the message like "Unable to find the one of specified databases."
Please let me know how to resolve this issue.
Thanks and Regards!!

First need to know the cause by checking below things

Job view history

Maintenance plan history

SQL Server logs

Event viewer logs

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 11

So there is no resolution for this issue, only precautiuon ?

By de selecting the database in maintenance plan before dropping it ?

Thanks and Regards!!

Thank You.

Regards,
Raghavender Chavva

Brandie Tarvin SSC Guru Points: 173002 More actions · Answer 12

Precaution and dropping / recreating your maintenance plan are your only two solutions for now.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.