May 17, 2009 at 8:06 am
can someone please help regarding this
I have a weekly job that runs and fails. I have created a maintenance plan on databases P6302, P66, P67, P82, P58. The maintenance plan includes
--Check database integrity
-- reorganize index
-- Rebuild index
-- update statistics.
details of error:-
Step ID0
ServerIHS-MAIN-SQL-04
Job NameP63 Maintenance Plan.Subplan_1
Step Name(Job outcome)
Duration06:26:33
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job failed. The Job was invoked by Schedule 5 (P63 Maintenance Plan.Subplan_1). The last step to run was step 1 (Subplan_1).
more details of the error:-
Date5/16/2009 10:00:00 PM
LogJob History (P63 Maintenance Plan.Subplan_1)
Step ID1
ServerIHS-MAIN-SQL-04
Job NameP63 Maintenance Plan.Subplan_1
Step NameSubplan_1
Duration06:26:33
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: IHS\SqlAgent. ...rsion 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:00:00 PM Progress: 2009-05-16 22:00:07.97 Source: {98AEE4A9-0C39-4513-9BB1-05959811F06E} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2009-05-16 22:00:10.14 Source: Check Database Integrity Task Executing query "USE [P63] ".: 50% complete End Progress Progress: 2009-05-16 23:06:07.56 Source: Check Database Integrity Task Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2009-05-16 23:14:24.53 Source: Reorganize Index Task Executing query "USE [P63] ".: 0% complete End Progress Progress: 2009-05-16 23:14:24.56 Source: Reorganize Index Task Executing query "ALTER INDEX [PK_NameSeq] ON [dbo].[_not_used_Junk8".: 0% complete End Progress Progress: 2009-05-16 23:14... The package execution fa... The step failed.
so can anybody what's happening in the above steps and how do i resolve this.
you reply is appreciated
May 17, 2009 at 9:23 am
Hello,
I can’t see any error in the details that you have posted.
Could you turn on logging for the Job Step (it’s an option on the Advanced Tab of the Job Step Properties), and then post the results of a failed execution.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 17, 2009 at 10:02 am
Or log from the package. You can have the package write a text file that you can use to troubleshoot.
May 17, 2009 at 10:08 am
Are you running any package from this SQL Server Agent job step..If yes then check the user executing the package does not have proper permission
Regard
Swayambhu
May 18, 2009 at 9:11 am
HI Aatish,
In just one maintenance plan, it is not a good idea to include index rebuild and index reorganize. You can use a script (modify it as per your req.)
http://sqlfool.com/2008/10/index-defrag-script/
in the TSQL portion of the maintenance plan. This will give you better flexibility.
Also check the permission for the user that is invoking the jobs to run.
Hope this helps,
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
May 18, 2009 at 3:44 pm
A good bet is to create the package as the very user whose credentials are used to run the package. The user should be a member of the role(s) needed per the DB tasks being performed, of course.
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply