March 7, 2008 at 5:49 pm
I've got a SQL 2005 SP 2 instance that is having problems with backups via a maintenance plan. There is a User DB maintenance plans with 2 subplans that do backups and a few other tasks (a daily transaction and a weekly full). Both are failing and I've narrowed it down to 1 database. When I remove that database from the plan, it works just fine. I can also manually run a backup to the exact same directory as the maint plan as the same user. I haven't been able to get a maintenance plan to work for this database even when a backup is the only task. This is the error I get in the log, " failed with the following error: "Cannot open... The package execution fa... The step failed."
What can't I open and why is the happening on just 1 database?
Thanks,
Tim
March 8, 2008 at 9:19 am
Can you manually run a backup against that database using:
backup database db_name to ...
If you can do that can you successfully execute that command in a SQL Server Agent Job?
It sounds like there is a permissions issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 8, 2008 at 5:02 pm
can you please add following command in backup step in job:
select databasepropertyex(' ','UserAccess')
and let me know output?
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 10, 2008 at 2:23 pm
Jack, I ran the TSQL command backup of the database and also scheduled it as a SQL Agent job using the same user as the Maintenance Plan and it works just fine.
Mohan, MULTI_USER
Thanks,
Tim
March 10, 2008 at 2:34 pm
Could you provide the full text of the error message? With all ...'s it is hard to know what the error actually is.
Thanks
😎
March 10, 2008 at 2:43 pm
Here is the "Full" text from the Job History Log. Unfortunately there are quite a few "..." spots.
Date3/10/2008 1:39:12 PM
LogJob History (User Databases.Daily Transaction)
Step ID1
Server
Job NameUser Databases.Daily Transaction
Step NameDaily Transaction
Duration00:00:11
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: ".: 100% complete End Progress Progress: 2008-03-10 13:39:17.14 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2008-03-10 13:39:17.1... The package execution fa... The step failed.
March 10, 2008 at 2:47 pm
I think some of that got cut off. Here it is again.
Executed as user: [domain\user]. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:39:12 PM Progress: 2008-03-10 13:39:13.11 Source: {DB534AEE-4253-413B-A62D-E7F073DED8B9} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-03-10 13:39:13.55 Source: Check Database Integrity Executing query "USE [database] ".: 100% complete End Progress Progress: 2008-03-10 13:39:15.33 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2008-03-10 13:39:15.35 Source: Check Database Integrity Executing query "USE [database] ".: 100% complete End Progress Progress: 2008-03-10 13:39:17.14 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2008-03-10 13:39:17.1... The package execution fa... The step failed.
I'm not cutting off parts at the end, it really says, "The package execution fa..." periods and all.
Tim
March 10, 2008 at 2:59 pm
There is possibly an error report somewhere (no I don't know where or its name other than it probably ends in the extension of .txt).
One possiblity here from what I saw was that the database it was trying to check could not be placed in single user mode. I have some maintenenace plans on one of our SQL 2000 boxes that fails periodically for that reason.
You should see if there are any other processes trying to run at the same time.
😎
March 10, 2008 at 3:06 pm
Thanks, I'll look around for that txt file.
I placed the database in single user mode and tried running the maint plan again, but still got the same error.
Tim
March 10, 2008 at 3:31 pm
I noticed you mentioned that the Maintenance plan does a log backup. That would fail if the DB is in Simple recovery mode.......
(Log backups only "work" in Full or bulk-logged)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 3:39 pm
Found it!
Lynn, the path to those text files is C:\Program Files\Microsoft SQL Server\MSSQL\LOG and this is where I found the problem. Thanks!
The error is with the name of the database. Somehow (surely it wasn't me ;)) a space was added to the end of the database name. When the maint plan executes the mast.dbo.xp_create_subdir command it uses the space at the end. Then it can't open the backup device ...\server\database"space"\backupfile
March 10, 2008 at 3:49 pm
Great!!
😎
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply