Archive for the ‘Troubleshooting’ Category

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

Today, I have found one of the index maintenance jobs in a SQL Server 2005 instance has failed. The history shows a helpful message:

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

This error is caused by the index reorganization step in the attempt to defragment the index in question but the index has the page level locking option disabled. The job fails and the rest of the indexes will not get worked on. On the next subsequent scheduled run of the job, the same scenario will happen until you enable page locking for this index and the indexes with page level locking disabled. Yes. It is really a royal pain in the behind. You can’t just run away from it and hoping the problem will go away. You have to man up to understand and resolve the problem.

By default, page level locking is enabled for indexes. Usually, the page level locking for the indexes get disabled unintentionally either by third-party tools or by using RTM version of SQL Server 2005 and creating indexes via the GUI in the Object Explorer. In case, you know of any other way the page level locking get disabled for the indexes, please let me know.

To find all the indexes in the database that have page level locking disabled, you can run the following query:

-- indexes that have page level locking disabled
SELECT OBJECT_NAME(i.object_id) as TableName
, name as IndexName
, allow_page_locks
FROM sys.indexes as i
WHERE ALLOW_PAGE_LOCKS = 0

You can then submit a change request to have these indexes modified to enable page level locking.

To generate the code to alter the indexes to enable page level locking for all the required indexes, you can run the following query. This query will omit the internal system tables that do not have the page level locking for the indexes:

-- generate the TSQL Statments to enable page level locking for the required indexes
SELECT 'ALTER INDEX ' +i.Name+ ' ON ['
	+ts.TABLE_CATALOG+ '].[' +ts.TABLE_SCHEMA+ '].[' +ts.TABLE_NAME+']'
	+' SET (ALLOW_PAGE_LOCKS = ON)' as TSQL_Statement
FROM sys.indexes i LEFT OUTER JOIN sys.tables t
		ON i.object_id = t.object_id
	JOIN INFORMATION_SCHEMA.TABLES ts
		ON ts.TABLE_NAME = t.name
WHERE i.allow_page_locks = 0
	AND t.Name IS NOT NULL

Save the output and run it on the database in question and then try the index maintenance job again. This will solve the page level locking is disabled error.

 

REFERENCES:

sys.indexes
http://msdn.microsoft.com/en-us/library/ms173760(v=sql.105).aspx

sys.tables
http://msdn.microsoft.com/en-us/library/ms187406(v=sql.105).aspx

INFORMATION_SCHEMA.TABLES
http://msdn.microsoft.com/en-us/library/ms186224(v=sql.105).aspx

Problems with Service Pack 4 Upgrade for a SQL Server 2005 Clustered Instance on Windows Server 2008 due to lower case in the names of the clustered nodes…

Earlier this week, as a security best practices we had been approved to install service pack 4 for a SQL Server 2005 clustered instance on Windows Server 2008. After we had successfully upgraded to the latest service pack 4, we encountered database mail failure and replication LogReader Agent error just to name two of the issues. After spending hours of researching online, we had found that the root cause of the problem is that the Resource database did not get updated along all the required objects for SQL Server 2005 because the Windows Server 2008 clustered nodes are in lower case. As per http://blogs.msdn.com, “…The datastore of the SQL Server setup presents the node names in the same case, however Windows Installer presents them in the upper case. The setup thinks that we are running it from the passive node and it does not upgrade Resource Database and other system databases.” http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/16/unable-to-create-maintenance-plans-after-service-pack-2-is-applied-on-sql-server-2005-fail-over-cluster-instance-running-on-windows-2008-cluster.aspx. The workaround involves evicting the nodes, changing the names to upper case, joining the nodes back to the cluster, and then re-running the service pack 4 upgrade and the cumulative update package.

The Resource database contains all the system objects for the SQL Server instance. It is read-only. To find the service pack and the Resource database version, you can run the following query:

SELECT
@@SERVERNAME AS InstanceName
,SERVERPROPERTY('Edition') AS 'Edition'
,SERVERPROPERTY('ProductVersion') AS 'ProductVersion'
,SERVERPROPERTY('ProductLevel') AS 'ProductLevel'
,SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime'
,SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'

UPDATED March 16, 2013:  In our testing environment, we did not have to evict the nodes and then change the node names to UPPER CASE. All we did was to install the Cumulative update package 3 for SQL Server 2005 Service Pack 4 http://support.microsoft.com/kb/2507769. That resolved the issue.

It is critical to be aware that patching for a SQL Server 2005 clustered instance is different from SQL Server 2008 plus clustered instance. In SQL Server 2005 clustered instances, you will need to patch only the active node. The passive node will get updated as well. But, in SQL Server 2008 and R2 clustered instances, you will need to patch both nodes. You will first need to patch the passive node, do a failover, and then patch the current passive node. In addition, don’t forget the hot fixes for the latest service packs. For example, for SQL Server 2005 service pack 4, which is the latest service pack, if you are not careful you will be left out the cumulative update package 3 for SQL Server 2005 service pack 4.

Let it be warned, before upgrading to service pack 4 and the cumulative update package for a SQL Server 2005 clustered instances on Windows Server 2008, make sure you check the cases for the clustered nodes. Make a request to your System Administration team to correct the nodes to upper case before you work on the service pack upgrades and the cumulative update package

REFERENCES:

Unable to create Maintenance Plans or DBMail Fails after service pack/Hotfix is applied on SQL Server 2005 fail over cluster instance running on Windows 2008 cluster
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/16/unable-to-create-maintenance-plans-after-service-pack-2-is-applied-on-sql-server-2005-fail-over-cluster-instance-running-on-windows-2008-cluster.aspx

Resource Database
http://msdn.microsoft.com/en-us/library/ms190940(v=sql.90).aspx

The job failed. Unable to determine if the owner (DOMAIN\Username) of job “JOB NAME” has server access…

Yesterday, I received a request to troubleshoot one of the SQL Server Agent job failures.  The job history reveals the message in the following format:

“The job failed. Unable to determine if the owner (DOMAIN\Username) of job “JOB NAME” has server access (reason: Could not obtain information about Windows NT group/user ‘DOMAIN\username”. [SQLSTATE 42000] (Error 8198))”

The quick fix is to set the job owner to the SQL Server Agent service account and restart the job if restarting is permitted. If you are not certain what the job does as it may contain critical business logics and functions, you will need to check with the application team, business owner(s) of the server, and all the required contacts before restarting the job and also get the business approval if needed. No kidding.

Let’s elaborate on more details on a more sustainable solution. By default, the job owner will be set to the login that had created the job. When a job executes, SQL Server will run the job as the SQL Server Agent account if the job owner belongs to the sysadmin server role. If the job owner does not belong to the sysadmin role, then SQL Server will impersonate the job owner login to execute the job. Even if the job owner login belongs to the sysadmin server role, if the password for the domain\user login for the job owner has expired or the domain\user account is disabled, then the job will also fail. If you need to tighten security for the job steps, Microsoft recommends you to create SQL Server Agent proxy accounts as the proxy accounts define the security context in which the job step can run. The steps are as follows:

  1. Crate dedicated user accounts for proxies.
  2. Grant the necessary permissions to the proxy user accounts.
  3. Assign the corresponding proxy accounts to the job steps.

 

REFERENCES:

Implementing SQL Server Agent Security
http://msdn.microsoft.com/en-us/library/ms190926(v=sql.90).aspx

How to: Create a Proxy
http://msdn.microsoft.com/en-us/library/ms190698(v=sql.90).aspx