PAGE LOCKS on INDEX

I have one of the maintenance job failed this morning. The failed step is:

ALTER INDEX ALL ON [table_name] REORGANIZE


The job was failed with the following error message:

The index "CX_ztbl_dm_os_memory_clerks_CreatedDate" (partition 1) on table "ztbl_dm_os_memory_clerks" cannot be reorganized because page level locking is disabled. [SQLSTATE 42000] (Error 2552). The step failed.

Reasons:
Be default when creating index, the option "Use page locks when accessing the index" option is checked. But for some reason this index was created without this option.

Solution:

Alter index [index_name] on table [table_name]
set (ALLOW_PAGE_LOCKS=ON)

Later I will provide the detail reason ... It should be start with how index created ...

Check the job status--the scripting solution

Yeah...I know I know, you could done that using Management Studio. But what if you want to incorporate this function as one part of your batch job? That's why I always say scripting everything you could.

here's how, note depends on the parameter(s) you passed to .sp_help_jobhistory , the order of the columns of the result set are different.


IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'dbo_mnt_get_latest_job_status'
)
DROP PROCEDURE dbo.dbo_mnt_get_latest_job_status
GO

CREATE PROCEDURE dbo.dbo_mnt_get_latest_job_status
@job_name sysname,
@run_date int = 0,
@run_time int =0

AS
declare @tbl_job_hitory table(

job_id uniqueidentifier,
job_name sysname,
run_status int,
run_date int,
run_time int,
run_duration int,
operator_emailed nvarchar(20),
operator_netsent nvarchar(20),
operator_paged nvarchar(20),
retries_attemped int,
server nvarchar(30))


if @run_date = 0
begin
set @run_date = convert(int,convert(varchar(20), getdate(),112))
end
if @run_time = 0
begin
declare @cur_dt char(8)
set @cur_dt = convert(char(8), getdate(), 108)
set @run_time = substring(@cur_dt,1,2)+substring(@cur_dt,4,2)+substring(@cur_dt,7,2)
end


insert @tbl_job_hitory
exec msdb.dbo.sp_help_jobhistory
@job_name = 'DBS5_PROD_DB_Log_Restore'
,@start_run_time = @run_time,
@start_run_date = @run_date

select job_name,run_date, run_time, run_status from @tbl_job_hitory
where run_date = @run_date and run_time > @run_time


GO


--exec dbo_mnt_get_latest_job_status @job_name=N'DBS5_PROD_DB_Log_Restore',@run_time='190000'

Find object name using sys.objects

sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.

Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of foreign key, name of the table it FK belongs and the schema owner name of table.

USE AdventureWorks;
GO
SELECT name AS ObjectName,
      OBJECT_NAME(schema_id) SchemaName,
      OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE type = ‘F’
GO

You can use any of the following in your WHERE clause and retrieve necessary information.

Object type:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table