MS SQL Server Maintenance Plan Failed: Error Code 0x534. [SQLSTATE 42000] (Error 15404))
May 3, 2008
hello,
we have changed the name of MS SQL server 2005 from XYZ to ABC using
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error
Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)
Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. Unable to determine if the owner (XYZSQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZSQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).
We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message: "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem. Does anyone have any ideas on how to de-bug this error? regards Stuart Ainsley
I'm having trouble running jobs with my active directory (ADS) account. I've setup my SQL services to run under an ADS account, but jobs cannot seem to query ADS for user information. We're running Windows Server 2003 and SQL Server 2005 SP2.
Here is the error message:
==
The job failed. Unable to determine if the owner (ADSme) of job eFASRtest has server access (reason: Could not obtain information about Windows NT group/user 'ADSme', error code 0x5. [SQLSTATE 42000] (Error 15404)).
==
also this message in log:
==
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'ADSme, error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
==
I already tested the suggested:
execute as login='adsme' and I get the same error on both (my local installations and production)
When trying to execute a maintenance plan I have setup (which contains a shrink command on all databases and then a backup to file command on all databases) i get the following error message from SQL agent:
Date 23/08/2007 10:02:17 Log Job History (Shrink and Backup v4)
Step ID 0 Server 0127-0323-DB01 Job Name Shrink and Backup v4 Step Name (Job outcome) Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message The job failed. Unable to determine if the owner (0127-0323-DB01IIPAdmin) of job Shrink and Backup v4 has server access (reason: Could not obtain information about Windows NT group/user '0127-0323-DB01IIPAdmin', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).
I have searched both online help and the forums and have not been able to find a solution, any help would be appreciated
I have log shipping set up between 2 SQL 2000 SP1 Servers on Win 2000. The db is small 10 meg, and when the restore job on the backup server fails I am getting "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." as the message, the maint paln on the primary server show no error. Anyone seen this before? The restore has worked 3 out of 5 times
I have a re-indexing stored procedure,yester day night it got failed "Executed as user: CONNECTSRVmyadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." what may be the reason..
Our company wanted to try out SQL Server 2005 Enterprise Edition (64 Bit). So, we were on free trial of the Enterprise Edition for past 5 months. After which we decided to go for SQL SERVER 2005 Standard Edition (64 Bit). And, Last week, we installed the SQL Server Standard Edition (64 Bit) on our server. After installation, everything was restored as before.
The version we are on right now is:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Strangely, a job which ran fine till then is failing with the following error: And, to be more specific, when this job is run manually in the form of a stored procedure using the query optimizer it runs fine. But, when its executed as a scheduled job on SQL Agent, it fails. The History logs record the following error.
Error :
The OLE DB provider "SQLNCLI" for linked server "LV-SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LV-SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LV-SQL2" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
I tried everything possible, even recreated the job, but, no avail.
I also considered the possibility if SQL Agent login account did not have enough permissions. So, I changed the SQL Agent login to windows authentication, but the job still fails. So, its the problem has nothing to do with login accounts either.
For the record, I have cross checked all these too.
1. Enabled the remote connections in Surface Area Config
2. Added local server login to remote server login mappings.
3. Checked 'Rpc' and 'Rpc Out' under server options. Also, the Connection Timeout and Query Timeout have been set to zero (0).
Any suggestions pointing towards problem solution appreciated.
I'm not sure if this is the correct group for this messages, but here it is anyway.
I have a job that has 3 steps to, periodicly the job errors out on Step 1. Following is the message (from Job History).
--------------------
Executed as user: SMIsqladmin. The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300). The step failed. --------------------
Can someone please explain this message, I have no idea how to fix it or what the cause is.
I am having a problem with a Job Step that is failing. The actual Step runs a Stored Procedure, but the Job keeps failing and returning the following error
"sqlmaint.exe failed. [SQLSTATE 42000](Error 22029). The step failed."
during maintenance plan i get following error : Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'AJAX' [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed. [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed. [8] Database AJAX: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
This is my first posting and actually I have big problem which I need to resolve immediately:
I have a 38 GB Database on my SQL Server which I want to full-backup. Until now backup was created on the same volume where the database resides. But now the volume is out of disk space. So what we did is that we connected an external USB disk drive with 500 GB to the SQL Server and try to backup there.
But our first and all consecutive backup attemps failed with error SQL-DMO (ODBC SQLState: 42000). It's working fine the first few minutes as we can see that the backup file is continously growing. But then it abruptly aborts with above error message.
Can anyone figure out why ? Any help is appreciated.
Good morning all. I need some help with a stored proc that is driving me up a wall. It's probably something stairing me right in the face but I can't see it!
I keep getting the following error on the procedure that I'm working on: SQL-DMO (ODBC SQLSTATE: 42000) ERROR 156: Incorrect syntax near keyword 'AS' Must declare the variable '@signid' Must declare the variable '@signid'
Here's the code:
CREATE PROCEDURE [dbo].[ws_savesignature2db] @xml as text='', @image as image='', @imageformat as varchar(20)='', @imagename as varchar(40)='', @imagesize as int=0, AS insert into signaturetable ([image], imageformat, imagename, imagesize) values (@image, @imageformat,@imagename,@imagesize)
/* if your XML field datatype is Text or nTEXT, use the code below in Sql Server 2000. If you use Sql Server 2005, you can use varchar(max) or nvarchar(max) which will be much easier and you do not need the code. */ select @signid = @@IDENTITY DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR([xml]) FROM ws_signature WHERE signid= @signid if @ptrval is not null WRITETEXT ws_signature.xml @ptrval @xml GO
Hi, I run 2 steps viz with NORECOVERY and with RECOVERY in sequence as SQP jobs. The 2nd step fails some times. When it starts failing, I run full backup of the production server again and the 2nd step starts succeding. Unless I run the full backup the 2nd step keeps on failing.
What are the conditions which generate for [SQLSTATE 42000] (Error 3013) and how to tackle this problem.?
The 2 steps are :- 1.RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup' WITH Replace , MOVE 'arsystem' TO 'd:ardataarsystem.mdf' , MOVE 'arsystem_log' TO 'D:ARLOGARsystem' , NORECOVERY GO
2.RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionSQL daily diff back up' WITH Recovery
The status of the 2 steps is : 1 Executed as user: BPOTESTREMEDY emedybackup. Processed 104480 pages for database 'ARSYSTEM', file 'ARSystem' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'ARSYSTEM', file 'ARSystem_log' on file 1. [SQLSTATE 01000] (Message 4035) RESTORE DATABASE successfully processed 104481 pages in 49.452 seconds (17.307 MB/sec). [SQLSTATE 01000] (Message 3014)
2. Cannot apply the backup on device 'E:SQL backup from productionSQL daily diff back up' to database 'ARSYSTEM'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
== I asked this question directly to Remus and wanted to share the response to all of those people using this forum == We recently moved our database server from SQL Server 2000 to SQL Server 2005. All applications on our intranet development server stay the same [VS.NET 2003], but recently resources in our Dev DB server ran out of space. While doing a thorough investigation, I noticed ERRORLOG file was occupying about 35 Gig of HDD space. I immediately checked SQL Server error log and noticed an entry which says €“ =========================================================================================== Date 7/7/2006 4:45:37 PM Log SQL Server (Current - 7/7/2006 4:45:00 PM)
Source spid77s
Message The activated proc [dbo].[SqlQueryNotificationStoredProcedure-5eaf8465-d0cb-4be7-93b6-44bb979dd41c] running on queue BW_Content.dbo.SqlQueryNotificationService-5eaf8465-d0cb-4be7-93b6-44bb979dd41c output the following: 'Could not obtain information about Windows NT group/user 'BWCINCHoffK', error code 0x534.' ===========================================================================================
What is this SqlQueryNotificationService in my database? Is it a SQL Server 2005 thing? Why the same kind of stored procedure does not exist in other databases, but BW_Content? This error is getting repeated most probably every second and is filling up our server. I believe our corporate IT people removed our domain accounts from BWCINC domain to BWCORP domain and probably some application which is using BWCINCHoffK credential is getting errored out. I tried to locate this application and was not successful. Is there anyway that I can stop this ERRORLOG from growing? How can I delete these log entries so that I can make space on our Hard Drive? Is there an easy way in SQL Server 2005 to locate which application is creating this error? Response from Remus: The 'SqlQueryNotificationService-...' is the service created by SqlDependency when you call SqlDependency.Start (). The problem you describe appears because the 'dbo' user of the database is mapped to the login that originally created this database. The SqlDependency created queue has an EXECUTE AS OWNER clause, owner is 'dbo' and therefore this is equivalent to an EXECUTE AS USER = 'dbo'. The error you see is reported by the domain controller when asked to give information about the original account 'dbo' mapps to (that is, BWCINCHoffK'): Error code: (Win32) 0x534 (1332) - No mapping between account names and security IDs was done.
To solve the issue, change 'dbo' to match a correct login, using either sp_changedbowner or ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somavalidlogin] To find the databases that have this problem, run this query:
select name, suser_sname(owner_sid) from sys.databases The databses that have the problem will show NULL on the second column. A similar problem is described here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1
To remove the entries, use sp_cycle_errorlog to force a new errorlog file, then delete the huge log file. ---------------------------------------
I executed ALTER AUTHORIZATION ON DATABASE::[BW_Content] TO [sa];
I got this error in SQL Error Log once and the growth of ERRORLOG was stopped. =============================================================== Date 7/10/2006 1:16:55 PM Log SQL Server (Current - 7/10/2006 1:17:00 PM) Source spid20s
Message
The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a' because it does not exist.</Description></Error>'. ===============================================================
When i m trying to retrieve data from a table named DAY1(USDCHF1998) , i get this error saying " Sql state 42000, sql error code:155 'USDCHF1998' is not a recognized OPTIMIZER LOCK HINT option ".
But its OK, when i use the table as DAY1USDCHF1998
Hi,I have a Scheduled Job on my PC that runs against a Linked Server. Ihave scheduled the Job to run when SQL Server Agent starts and I haveconfigured my SQL Server and SQL Server Agent to start when the OSstarts. The connection to the Linked Server is via my Broadbandinternet connection.I have some suspicions as to the cause and hence a possible solutionbut I want to run my ideas past anyone else who may have experiencedthe same.1. Should I have my DSL modem turned on before I start the PC so thatthe Network connection is available when the OS starts?2. Are the Network connections only made available after you havelogged in and hence not established quickly enough before SQL Serverand SQL Server Agent are started?Yesterday, when I rebooted the PC with the modem still on theScheduled Job ran without failure but this morning again it failed. Myusual routine is to turn on the modem just before I login.If No. 2 is the case then maybe I just need to set the "Retryattempts" parameter.Thanks,Michael HolbertonHospedaje Los Jardines & Sacred Valley Mountain Bike ToursCusco Database Development and Cycling Serviceshttp://www.machawasi.com/http://machawasi.blogspot.com/http://databaseservices.blogspot.comhttp://serviciosdeciclismodelperu.googlepages.com
I created a database maintenance plan on sql server 2005 (standard SP2, cluster environment). The plan created successfully on scheduled successfully. But when execute the plan, it fails. Here is the information in the log:
The last step to run was step 1 (TranLog backup).,00:00:01,0,0,,,,0 09/19/2007 12:06:38,Tranlog backups.TranLog backup,Error,1,LAIWWORKSITE1LA,Tranlog backups.TranLog backup,TranLog backup,,Executed as user: STROOCKSQLSRV. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:06:38 PM Warning: 2007-09-19 12:06:39.27 Code: 0x80012017 Source: Tranlog backups Description: The package path referenced an object that cannot be found: "PackageTranLog backup.Disable". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set PackageTranLog backup.Disable value to false. Started: 12:06:38 PM Finished: 12:06:39 PM Elapsed: 0.688 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Step3.Alter database <xxxxx> set recovery full GO Use XXXXX GO dbcc shrinkfile (XXXXX_Log, 200)
Up to yester day my Job ran fine today job got failed at Step1
Executed as user: CLUSTURsa_admin. The log file for database '<xxxxx>' is full. Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002) Could not write a CHECKPOINT record in database ID 3 because the log is out of space. [SQLSTATE 01000] (Error 3619). The step failed
some body help me what to do now, changing recovery model also needs space?(Simple to Full/Full to Simple)
I am trying to learn replication and using Agent jobs while attached to my corporate domain. Trying to use my personal domain account to run jobs or replication, I keep getting:
SQL Server Error 15404, Could not obtain information about Windows NT groupuser 'MyNameMyDomain', error code 0x5 [SQLSTATE 42000] (ConnIsLoginSysAdmin)
What might be preventing SQL Server from getting my account information from the domain? Group ploicy?
I am gettnig following error when performing a backup using a maintenance plan:
Date 4/24/2007 11:01:23 AM Log Job History (MaintenancePlan1.Subplan_1)
Step ID 1 Server 006-DEVSQL2005 Job Name MaintenancePlan1.Subplan_1 Step Name Subplan_1 Duration 00:00:09 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message Executed as user: tcssvcDBASQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:24 AM. The return value was unknown. The process exit code was -1073741819. The step failed.
I am able to use the transact sql generated by the maint plan and create a backup. is this a 64 bit issue?
I'm running SQL 2005 SP2 Enterprise Edition, and I have database mirroring configured for all of my non-system databases (3 total). My primary database server is a 2-node cluster. I've tried this from both a remote machine and directly on the SQL server itself, with the same results. Could this have anything to do with the data mirroring? I didn't have this problem previous to setting that up, although I haven't tried creating a maintenance plan in quite a while either.
Here are the error message details:
===================================
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------ Program Location:
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.GetExistingPackageNames(String serverName, String userName, SqlSecureString securePassword) at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.Invoke()
===================================
Cannot find folder "Maintenance Plans".
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.GetPackageInfos(String strFolder, String serverName, String serverUserName, String serverPassword)
===================================
Cannot find folder "Maintenance Plans".
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetPackageInfos(String bstrPackageFolder, String bstrServerName, String bstrServerUserName, String bstrServerPassword) at Microsoft.SqlServer.Dts.Runtime.Application.GetPackageInfos(String strFolder, String serverName, String serverUserName, String serverPassword)
Using the full SQL Server 2005 patched up to build 9.0.3054.
Creating a simple maintenance plan to backup specific databases.
As soon as i try to save the plan i get the popup error :"no such interface supported". It won't allow me to save any plan at all regardless of what I put in there.
Some body have this error : Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter. When i execute my maintenance plan.
Creating maintenance plan "MaintenancePlan" (Error)
when I try to create a maintenance plan. I checked and the Microsoft Web site and other forums said it may be that integration services is not installed so I tried installing it and it said that it is already installed.
The accompanying messages with the report are:
ADDITIONAL INFORMATION:
The connection type "ADO.NETQL" specified for connection manager "{C72T6E0B-6CE7-4138-A8D9-BFE8897900700}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. (MaintenancePlan)
------------------------------
The connection type "ADO.NETQL" specified for connection manager "{C72CT60B-6CE7-4138-A8D9-BFE8897900700}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. (MaintenancePlan)
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "MaintenancePlan" (Stopped)
I'm running SQL 2005 SP2 on a cluster. I have a maintenance plan that backs up databases. When I execute the job associated with the plan, I get the following error:
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
This only occurs when node 1 is active. When node 2 is active, the job runs with no problem.
I edited the job, and it references the package
Maintenance PlansPlanName
If I add a leading "" to the package path so that is reads
Maintenance PlansPlanName
then the job runs successfully on both nodes. So the question is why would this fail on one node and not the other? I know there are SSIS issues when applying SP2 on a cluster, so we applied it on each node. SSIS is running version 9.0.3042 on each node.
Error: Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.Supply either @id or @name.
I am trying to send a email when a maintenance plan completes. I selected the logging option and checked "Send report to an email recipient". I selected the recipient I created in database mail. Sending test emails out from database mail works.
I have inherited a server on which is a maintenance plan with two subplans on different schedules. In each subplan there are Execute T-SQL tasks with scripts for index rebuilds. Each task is set up with a Completion arrow to the next task and a Failure arrow to a Notify Operator Task. I was asked to add a task for index rebuilds to a specific subplan for a specific database, which is what the other tasks also do. I discovered that my task was failing but the others were fine. No notification was sent about my task failing even though the job is marked in MSDB as a failed job. I have sent a test email using the "Send Test Email..." option when right clicking Database Mail in SSMSand I receive an email so I know Database Mail works.
I set up a test job to model the index job that I can't get notifications from. I have two T-SQL tasks that just select the top row from a small table. The first task has a syntax error that I did so it would fail. I have a failure arrow to a Notify Operator Task and a Completion arrow to another T-SQL task with no syntax error which has a Success arrow to a Notify Operator task. As expected, when I execute this job I receive one failure email and one success email.
The only other troubleshooting step I know to try is to add a Notify Operator task before my failing task. That Notify Operator task will hopefully fire to tell me that the previous step was successful. I am not having problems with the other steps so I was just thinking I would try to get the subplan to send me a success email about one of the steps that has been working fine.
When our administrators login on the workstation the application works well. But when ordinary users login they get the following error. (We only have 1 domain)
SQLState: 28000
SQL Server Error: 18452
Login failed for user ". The user is not associated with a trusted SQL Server connection.
Then the standard SQL Server Login window pops up asking for the Login ID and Password. On the window the 'Use Trusted Connection' is checked and the name of the user on the workstation appears on the LoginID. What we do is uncheck the 'Use Trusted Connection' then login using the credentials above.
