SQL Server – SQL Server Agent – Job “Job History (OptimizePlan.Subplan_1)” Failed

Background

Reviewing SQL Server Agent jobs found ones that consistency fails.

One of them is a Maintenance Plan that Organizes Indexes.

 

Error Message

SQL Server Agent

Image

jobhistory-optimizeplan-subplan-20170222-0245pm-cropped-up

 

Textual

Executed as user: DBLAB\sql. … Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:00:40 PM Progress: 2017-02-22 15:00:41.05 Source: {04448243-3A2E-4299-BC33-5780CE3F7DEC} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…”.: 100% complete End Progress Progress: 2017-02-22 15:00:43.82 Source: Progress Progress: 2017-02-22 15:00:43.86 Source: Reorganize Index Task Execut… The package execution fa… The step failed.

 

TroubleShooting

Maintenance Plan

Review the Maintenance Plan and here is what things look like.

OptimizePlan

Image

maintenanceplan_optimizeplan_20170222_0249pm-cropped-up

 

OptimizePlan

Reporting and Logging

Reporting and Logging ( Default )

Here is the default setting with “Log extended information” off.

reportingandlogging_20170222_0250pm

Reporting and Logging ( Post Changes )

Here is what happens when we set “Log extended information” on.

reportingandlogging_20170222_0259pm

 

Logfiles

Went to the identified Log Folder and sought for files matching the maintenance Plan we are trying to dig into.

optimizeplan_folder_20170222_0251pm

 

Logfile Contents

Image

content_20170222_0449pm

Text


USE [LNCD]
GO
ALTER INDEX [NCD_PK] ON [dbo].[NCD] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
USE [LNCD]
GO
ALTER INDEX [RelatedCase_PK] ON [dbo].[RelatedCase] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Reorganize index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Object: Tables and views
Compact large objects
Task start: 2017-02-19T00:00:12.
Task end: 2017-02-19T00:01:56.
Failed:(-1073548784) Executing the query "ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]...." failed with the following error:
"The index "IX_FullNCDCategories_NCDID" on table "FullNCDCategories" cannot be reorganized because page level locking is disabled.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:
GO

GO

Explanation

  1. The error Message has the following content
    • USE LNCD
      • Changing to database LNCD
    • Failed:(-1073548784) Executing the query “ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]….” failed with the following error
      • The name of the index is IX_FULLNCCategories
    • “The index “IX_FullNCDCategories_NCDID” on table “FullNCDCategories” cannot be reorganized because page level locking is disabled.”.
      • The targeted table is FULLNCDCategories
    • The Index can not reorganized because page level locking is disabled on the targeted table

 

Remediation

Identify impacted tables

Identify Impacted tables on current database

Code


SELECT 
		  [database] = db_name()

		, [schema]   = schema_name(tblSO.schema_id)

		, [object]   = tblSO.[name]

		, [index]    = tblSI.[name]

		, [allowRowLocks]
			= case ( tblSI.[allow_row_locks])
				when 1 then 'Yes'
				else 'No'
			  end

		, [allowPageLocks]
			= case ( tblSI.[allow_page_locks] )
				when 1 then 'Yes'
				else 'No'
			  end

FROM sys.objects tblSO 

inner join sys.indexes tblSI 

	on tblSO.object_id = tblSI.object_id 

WHERE tblSO.[type] = 'U' 

and ( 
		   ( tblSI.[allow_row_locks] = 0)  
		or ( tblSI.[allow_page_locks] = 0 ) 
	) 

and  tblSO.[is_ms_shipped] = 0 

ORDER BY 
		tblSO.[name]



Image

identifyimpactedtablesoncurrentdb_20170222_0518pm

 

Identify Impacted tables on all databases

Code


DECLARE @commandPLLD	varchar(1000) 

declare @tblObjectPageLevelLockingDisabled TABLE
(
	  [database]			sysname
	, [schema]				sysname
	, [object]				sysname
	, [index]				sysname  null
	, [allow_row_locks]		int null
	, [allow_page_locks]	int null

	, [sqlStatementPreserve] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = '
				+ case([allow_page_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end
				+ '		 , ALLOW_ROW_LOCKS = '
				+ case([allow_row_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end

				+ '		) '


	, [sqlStatementRevise] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = ON  '
				+ '		 , ALLOW_ROW_LOCKS = ON  '
				+ '		) '

)

SELECT @commandPLLD = 'USE [?]; SELECT [database] = db_name(), schema_name(tblSO.schema_id), tblSO.name, tblSI.[name],  tblSI.[allow_row_locks]	, tblSI.[allow_page_locks] FROM sys.objects tblSO inner join sys.indexes tblSI on tblSO.object_id = tblSI.object_id WHERE tblSO.type = ''U'' and ( ( tblSI.allow_row_locks = 0)  or ( tblSI.allow_page_locks = 0 ) ) and  tblSO.[is_ms_shipped] = 0 ORDER BY tblSO.name ' 

insert into @tblObjectPageLevelLockingDisabled
(
	  [database]
	, [schema]
	, [object]
	, [index]
	, [allow_row_locks]		
	, [allow_page_locks]	
)
EXEC sp_MSforeachdb @commandPLLD


select *
		
from   @tblObjectPageLevelLockingDisabled tblPLLD




Explanation

The code snippet above does the following:

  1. Uses sp_MSforeachdb to run the same simple discovery code across all databases
  2. Queries the sys.indexes table looking for
    • allow_page_locks equal to 0
  3. When found it captures the
    • Current SQL for preserving the current Index State
    • The SQL to revise the current state unto set allow_page_lcoks to 1 & allow_page_locks to 1

Output

identifyimpactedtables_20170222_0506pm-croppedup

 

Ran

Captured the script from the sqlStatementRevise column.

Image

jobhistory-optimizeplan-subplan-20170222-0524pm-croppedup

Explanation

All of twenty-seven minutes later, I and we are good.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s