Maint Plan Fails :-(

Question

Maint Plan Fails :-(

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
November 2, 2010 at 6:42 am

#388146

SQL 2008 created via maint plan wizard , two standard steps and one added T-SQL task.
fails on first step to reorganize index with
Source: Reorganize Index Description: The Execute method on the task returned error code 0x80004002 (Unable to cast object of type 'System.DBNull' to type 'System.String'.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:37:58 Finished: 12:38:01 Elapsed: 2.328 seconds. The package execution failed. The step failed.
any words of wisdom before I through the pc out the window ? google reveals nothing of use
weeps in corner...
~simon

Viewing 8 posts - 1 through 7 (of 7 total)

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

DNA_DBA SSCrazy Eights Points: 9132 More actions · Answer 1

find the SQL Agent job for this, go to Steps and edit the step - on the Advanced area, enter an output file and click on Include step output in history - you may get more information in the log

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 2

Instead of viewing the job history, you need to look at the maintenance plan history. Right-click on the maintenance plan in Object Explorer and view history.

Find the step that failed, highlight it - and at the bottom of the form you will have a text box with the actual error. Copy/Paste everything in that text box to notepad (I find it easier to read this way).

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

hada79 Old Hand Points: 356 More actions · Answer 3

Old post, I hope jeff is still around, he seems like a smart guy...

I have the same error when my mx plan runs. Following your advice, I right click the mx plan and go to view history. The problem is hat it has a single row with a green check, without a plus sign to expand any steps - giving me no indication that anything went wrong with the job.

Any further suggestions? Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 4

hada for me, the maintenance plan is outdated and not really good for production; it's fine for shops with no DBA, but I would strongly recommend replaicng it with the scripts from ola hallgren; kid tested, mother approved, peer reviewed, recommended in countless posts here.

it creates jobs for you, so all you have to do is schedule them, and gives you more control over checking your db, reindxing in a smarter manner, and so much more:

http://ola.hallengren.com/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hada79 Old Hand Points: 356 More actions · Answer 5

Thanks Lowell, I have these installed but never found the time to configure them. Mx plans were just easier, and we are a small shop. I'll give them a go.

Lowell SSC Guru Points: 323518 More actions · Answer 6

isn't that always the way it works?

i install all kinds of stuff locally, but never have time to dig deeper into the details...

until a fire comes along and i discover i might already have the right extinguisher!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hectorsmith786 Valued Member Points: 61 More actions · Answer 7

Check if you have any offline database on your server & that is part of your maintenance plan. I was having update statistic task & i selected All User Databases option to update the statistic. Changing it to manual selection of databases, it worked.