previously succssessful maintenance plan backup job started failing

Question

previously succssessful maintenance plan backup job started failing

EllieDBA

SSCommitted

Points: 1786
More actions
June 18, 2009 at 8:32 pm

#210980

we have SQL 2005, sp3. we had amny backup jobs that were running fine till last night that they disabled and then restarted SQL server agent on cluster environment, we have other update and else jobs that run fine, but the backup jobs keep failing, I have modified them, chnaged the owner to sa, changed the owner to service accounts, yet they stilll fail, I even set up a new backup maintenance plan and it still failed,
I am posting the error at the buttom, anyone who can think of anything, please enlighten me.
I also went on msdn and one post was talking about "it seems DTS.dll might be unregistered" and once the blogger reregistered the DTS.dll, the jobs worked fine again, if you know of this dll can you tell where the system engineers might be able to find it so they reregister it?
Code: 0x80012017 Source: Transact Backup Local Description: The package path referenced an object that cannot be found: "\Package\Subplan_1.Disable". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set \Package\Subplan_1.Disable value to false. Started: 8:00:00 PM Finished: 8:00:02 PM Elapsed: 1.937 seconds. The package execution failed. The step failed.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What else changed? Prior to the failure, were you running in a clustered environment or was this a change that was being made moving to a clustered environemnt?

EllieDBA SSCommitted Points: 1786 More actions · Answer 2

we were running on a cluster environment, i am trying to figure out if system engineers moved any service for sql from one node to the other last night, I know they restarted the sql server as well as SQL agent was disabled and they reenabled it. but more than that, I don't think anytihng has changed.

|--|999 SSC Eights! Points: 827 More actions · Answer 3

Run the backup from Query ANalyzer and see if still is failing ...

BACKUP My Database to disk = 'd:\MyBak.bak'

WITH INIT;

where "D" or whatever, is your drive

also, please check the permissions on that drive for the SQL server service; on SQL2005 backups run under that security context.

|--|999 SSC Eights! Points: 827 More actions · Answer 4

Forgot to give you these nice T-SQL queries; they will let you know compare and see if something changed at Cluster level

--To see cluster associated drives

SELECT * FROM sys.dm_io_cluster_shared_drives

--Check Node configuration

SELECT * FROM sys.dm_os_cluster_nodes

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 5

I remember this happened with one of my clients in the past .

But sadly i am not able to find that mail with all the steps i took .

basically it looks like the enviornment variable have got changed and not pointing to the right path : right click my computer >> properties >> advanced >> enviornment variables .

See if you can do something there .

There is also a registry key to do that but i do not recollect that as well .

Or in the job you will see DTSEXEC or something like this .GIve the full path of the file instead of just the name and save th file .

Try running the job ..

I feel your issue is revolving around this .

hope this helps .

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

EllieDBA SSCommitted Points: 1786 More actions · Answer 6

thanks jcampo and abhay,

jcampo, i manually backup full or transct the databases to the same path and it succeeds, it is only maintenance plan that fails

Abhay, since this is clutester, can you tell me where I should right click on the properties? which node and which location? Also can you advise which location would these configuration files be?

I also checked the command line for package and it is like my command line for any other sql environment

/SQL "Maintenance Plans\backup hostsharedev02" /SERVER ourservername /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\backup servername.Disable";false /REPORTING E

also set value in ssis package is set to false like the rest of successful environments

Any advice would be more than helpful

EllieDBA SSCommitted Points: 1786 More actions · Answer 7

All

Just wanted to thank you all for your responses and let you know the issue is fixed by following measure taken.

I asked our SE team to reregister DTS.dll and it worked. we are in cluster and he reregistered the dll on both active nad passive nodes. Below link was helpful in diagnosing this

http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/84ab08d6-ed31-4b8e-8382-cd495c28cfde/