SQL Server Maintenance Task Failing

mlnews

Researching for others....

SQL Server Scheduled Job 'MaintenancePlan.Subplan_1' (0xF8A03D1AD115CE479F49E0EFBD6F69C3) - Status: Failed - Invoked on: 2015-10-08 09:03:36 - Message: The job failed. The Job was invoked by User "My sql login here". The last step to run was step 1 (Subplan_1).

Anyone familiar with this error or what might be the cause?

dafyre

Check the username & password that the job is running under.

scottalanmiller

I found someone with a similar issue but that one appears to be related to MS DPM, not SQL Server alone.

https://social.technet.microsoft.com/Forums/en-US/243b40d6-6ae5-44b4-bf39-7c12ae73f9f4/sql-server-scheduled-job-fails?forum=dataprotectionmanager

mlnews

This might be useful...

http://stackoverflow.com/questions/16766416/the-job-failed-the-job-was-invoked-by-useruser-the-last-step-to-run-was-step

JaredBusch

The process to troubleshoot this would be to open the maintenance plan and edit the step one and see what it is attempting to do.

Here is a job called rebuild indexes.subplan 1.

When I dive into it, you see that it is calling the Maintenance Plan Rebuild Indexes.

Right click on the plan and when the GUI opens, right click on the task and choose edit.

You can then click ont he databases drop down and see which databases are being affected.

You can also click view T-SQL to see more or less what the actual commands are that get used.

JaredBusch

Once it populates, you can look through this and throught the SQL command logs to potentionally find the error.

JaredBusch

My co-worker wrote a quick stored procedure to make searching the command logs easier also.

You can execute this proc with no parameter to see everything, or you can pass a search string. Remember it is a string and use the % wildcard appropriately.

exec AA_CmdsExecPast24Hrs '%tablename%'
exec AA_CmdsExecPast24Hrs '%Jared%'
etc.

/****** Object:  StoredProcedure [dbo].[AA_CmdsExecPast24Hrs]    Script Date: 10/08/2015 11:02:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Bill Russell - Bundy & Associates
-- Create date: 6/29/2015
-- Description:	Returns a list of queries run over the past 24 hours.
-- =============================================

CREATE PROCEDURE [dbo].[AA_CmdsExecPast24Hrs]
	@ContainsLIKE varchar(300)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT execquery.last_execution_time AS ExecTime, ExecSQL.text AS [Script] 
	FROM sys.dm_exec_query_stats AS ExecQuery
	CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS ExecSQL
	WHERE @ContainsLIKE='' OR (@ContainsLIKE <> '' AND ExecSQL.text LIKE @ContainsLIKE)
	ORDER BY ExecQuery.last_execution_time DESC

END