Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Fixing Maintenance Plan Error code 0x534

By Ganapathi varma Chekuri,

Have you ever changed Server name on which SQL Server instance is installed? One of my friends changed the hostname of a Windows server with SQL Server already installed. After this, the SQL Server maintenance plan jobs started to fail.  As we know, internally SQL Server still shows the old hostname this must be dropped manually. Otherwise your SQL Server maintenance plan jobs fail with this error.

The Job failed: Could not obtain information about Windows NT group/user 'XXXXXX\Administrator', error code 0x534. [SQLSTATE 42000] (Error 15404))

In this post, I will show you the procedure to resolve the errors and execute the SQL Server Agent Maintenance Plan jobs successfully. Below is the error screenshot showing job failure in the SQL Server agent logs. The error is highlighted in the image in red.

First, connect to your SQL Server instance with SQL Server Management Studio and run the below queries to check SQL Server name:

use master
select @@SERVERNAME -- The current hostname SQL Server recorded
select SERVERPROPERTY('machinename') -- The hostname the operating system recorded

In the below screenshot, the server name and machine name are different.

Run the below shown T-SQL scripts to drop the old server name, and then it add back the SERVERNAME to match the operating system’s hostname.

In the below screenshot, first we dropped old server name.

In the below screenshot, we have added new server name using T-SQL.

Now, log into the SQL Server with a “sysadmin” privileged user. Go to SQL Server logins, and you can still see the oldServername\administrator login bound with the SQL Server engine.

Drop the login “OldServername\administrator” and create a new windows login as “NewServername\administrator”, adding the sysadmin Server role.

CREATE LOGIN [NewServername\administrator] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'NewServername\administrator', N'sysadmin';

In the below screenshot, we have added “DB01\administrator” login.

The owner of the job associated with maintenance plan is OldServername\administrator. We need to reset the ownerid using the below T-SQL Update query.

Now, We need to reset the owner of the job associated with the maintenance plan by running the below T-SQL query. In below screenshot, reset the owner of the job.

Right click on SQL Server job and select properties and change the owner of job to “sa” login.

Delete old maintenance plan and re-create the maintenance plan. Right click and click execute maintenance plan. You can see maintenance plan executed successfully. J

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

Linkedin

Email: Gana20m@gmail.com

 
Total article views: 1271 | Views in the last 30 days: 6
 
Related Articles
ARTICLE

Multi-Server Administration

Multi server administration allows you to create jobs and maintenance plans once. You can then monit...

FORUM

Login failed for WINDOWS Administrator login. (Microsoft SQL Server, Error: 18456)

Login failed for WINDOWS Administrator login. (Microsoft SQL Server, Error: 18456)

ARTICLE

How Maintenance Plans are a Big Plus for SQL Server Administration

SQL Server 2012 makes your database maintenance jobs easy by proving an effective Maintenance Plan W...

FORUM

General SQL Administration

Administration Good Practice Maintenance Plans

BLOG

Be a good administrator

Below are the topics every Administrator has to keep up with. Backup / Restore: Understand the b...

Tags
maintainance plans    
sql jobs    
sqlserver    
 
Contribute