Syntax Error Executing Maintenance Plan Job
Dec 6, 2007
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.
Thanks,
Tom
View 14 Replies
ADVERTISEMENT
Apr 27, 2001
Hi,
I am getting the following message from one of my scheduled Maintenance plan, anyone know what this mean.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
Martin
View 6 Replies
View Related
Apr 19, 2006
Below is the Error ( I have SSIS installed ; when I open Business
Intelligence Studio it lists SSIS as installed)
Maintenance Plan Wizard Progress
- Creating maintenance plan "Backup xxxDatabases" (Error)
Messages
* Create maintenance plan failed.
------------------------------
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=...
------------------------------
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The specified '@subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=...
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "Backup SCS Databases" (Stopped)
Please advise
View 1 Replies
View Related
Apr 24, 2007
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?
View 1 Replies
View Related
Jun 5, 2007
I'm getting the following error whenever trying to create a new maintenance plan in the Management Studio:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
ADDITIONAL INFORMATION:
Cannot find folder "Maintenance Plans".
------------------------------
Cannot find folder "Maintenance Plans".
------------------------------
BUTTONS:
OK
------------------------------
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)
View 1 Replies
View Related
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)).
please help us in this issue
View 13 Replies
View Related
May 22, 2008
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.
I need someone's help!!!!
View 6 Replies
View Related
Feb 2, 2006
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.
Thanks!
Daniel
View 17 Replies
View Related
Aug 30, 2007
Hello, I get the error
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)
View 3 Replies
View Related
Nov 13, 2007
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.
Any ideas what the problem could be?
Thanks!
David
View 6 Replies
View Related
Aug 23, 2007
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
Thanks
Marek
View 2 Replies
View Related
Apr 9, 2001
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
View 4 Replies
View Related
Jan 2, 2007
OS: Win 2003 Service Pack I on 2 boxes
SQL: SQL Server 2005 Clustered System
Situation: I have created a maintenance plan to backup my transaction log every hour. This runs fine for some time and then later that day it stops working. This could happen at any anytime, and I end up having to manually kick it off again. I read the SQL error logs and this is what it points to:
[298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
[298] SQLServer Error: 2, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[382] Logon to server 'JAXNVSQL1CLS' failed (ConnAttemptCachableOp)
[177] Job MaintenancePlan Insight Transaction Logs has been requested to run by Schedule 10 (Insight Transaction Log Backup)
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
I have already used SAC to confirm that remote connections is enabled using TCP/IP and Named Pipes. I have used the server configuration tool as well to check this. I have used the Studio Management and checked on the remote connections as well.
I know these protocols are enabled and working because I have used the Studio Management to check on the activity connections type and seen both are working. Plus the fact this maintenance plan does work, it just so happens something causes it later to fail. When this does fail, I log onto the server but can not login using the Studio Management , I get [298] errors. I can login using the Studio Management on my laptop remotely but can not manually kick off the maintenance plan. I end up having to log on to the server and restart the MSSQL Server service and manually kick off my plans. (By the way these services do not show shutting down in the Windows event viewer). After restarting the MSSQL Server service and manually executing the maintenance plan everything is fine until the next time it hits a glitch.
I do have SQL Browser up and running, all of the MSSQL services running, I need some help as to what may be causing this problem!!!!!!!!! HELP!!!!! I am losing my mind!!!!
View 5 Replies
View Related
Jan 23, 2008
Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
View 12 Replies
View Related
Aug 17, 2007
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.
View 1 Replies
View Related
Mar 5, 2001
I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan.
Thanks!
View 1 Replies
View Related
Oct 23, 2001
Hello All
I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.
I would be very grateful for any ideas
View 1 Replies
View Related
Aug 31, 2000
I have a strange thing in one of our Maintenance plans.
On the first tab where you check which databases you're including in the plan I have (say my database name is CAT) a 'CAT' and 'cat' database listed and the one chosen is 'cat'. However my database in all other views shows up in all caps. (even when I do an sp_helpdb)
The backups look like they're working, etc. but it just seems weird. If I go to create a new plan it only gives me the one option 'CAT' which is really what's there. I'm new and I'm thinking the database at one time was 'cat' and this is when the maintenance plan was created. Then it was renamed to 'CAT' and there's the two db's showing in the old mainenance plan.
What would you do? Create a new plan with "CAT" and just get rid of the old one with the weird 'cat' and 'CAT'?
Any other suggestions or ideas on what happened..
ann
View 1 Replies
View Related
Oct 4, 1999
I've created a database maintenance plan to backup a database, but it just
isn't happening, am i missing something. The maintenance plan appears to be
created successfully.
responses appreciated.
thanks
Todd Minifie
View 6 Replies
View Related
Oct 1, 2004
Can you generate script for a maintenance plan?
I know how to script a job, I was wondering about a plan.
If not, whats the best way to record the configuration?
Thanks
Lystra
View 3 Replies
View Related
Apr 7, 2008
hi everyone..
this is a little bit weird ..
i am trying to make a backup strategy. i am using sql2005.
when i go to maintenance plan. right click >> new maintenance plan...
nothing happens..
if i go with the maintenance plan wizard everything goes normally.
after doing the backup, if i right click on it and press
modify , nothing happens too.
what i mean by nothing happens is that it doesn't open the "design view".
the back up is doing normally.. but i need to set a range of 5 days before overwriting the oldest backup.
any idea what is going on or what am i missing?!
thank you
View 2 Replies
View Related
Apr 9, 2008
Windows Server 2003R2 w/ Sp2
SQL 2005 w/ Sp2
Created weekly (full backup) and a daily (differential backup) Maintenance Plans using the wizard. I formatted the server, installed the OS and SQL. Restored the full backup (No Recovery Mode), then restored the differential backup (Recovery Mode), tested and all worked well.
Then I noticed the original Maintenace Plans I created (Full and Differential) were gone; makes sense as I had formatted the server.
Is there a way to create a Maintenance Plan file or script that I can save and just add back to the server??
Hope that makes sense.
Any help appreciated.
Kerry
View 3 Replies
View Related
Feb 26, 2006
Hello 2 all,
Could someone advise and/or correct me with my thoughts on how I would do my db maintenance plans?
(db's on SQL2000 as 'full' model)
Backups:
1) Daily Transaction log backups scheduled frequently enough.
2) Full Backup scheduled daily. Good way to start I presume ;)
Maintenance: Would be scheduled daily if possible, on non-production hours and if not colliding with daily full BU schedule.
3) Full DB reorg data&indexes.
4) Update Query Optimizer Stats (although 'Auto Update Stats' is on)
5) Shrink the logfile (ldf) as I presume this will have grown due to previous maintenance jobs.
6) If 5 ok, alter ldf filesize back to new allocated size.
Rgds,
T.
View 6 Replies
View Related
Mar 9, 2006
Hi
As per my backup policy I need to do a full backup dialy & transactional backup every 30 minutes , can i use the Maintenance plan to do this
or should I write T-sql statements & create jobs.....
I mean whats the difference between the two methods.
Thanks
View 5 Replies
View Related
Jul 22, 2006
Hi folks.
Our products are VB6-based interacting with MSDE2000 i.e. none of our clients have EM. We have auto-backups performed twice a day by default and we encourage people to keep it to a least 2 per day.
Yesterday, one of our clients reported a problem. Upon investigation, I did a DBCC CheckDB WITH ALL_ERRORMSGS which returned the following:
Object ID 1461580245, forward row page (1:159), slot 50 points to page (1:234), slot 43. Did not encounter forward row. Possible allocation error.
So after reading a lot of posts and blogs from Paul Randal, I proceeded cautiously to copy the DB then perform a DBCC CheckDB REPAIR_REBUILD which had no effect, then a DBCC CheckDB REPAIR_ALLOW_DATA_LOSS which also had no effect. Then, I determined which table was at fault via DBCC CheckTable and I exported its data to a blank table where I discovered the missing data row and corrected for it manually. End of story.
Not a funny situation. The worst part is that this defect may have been there for a very long time, meaning that restoring the latest backup would not have helped the situation. I now realize that relying on backups alone is a huge no-no.
So, having been scarred into reality, I would like to install an automated maintenance plan. I'm used to doing it on my personal station using EM but, as stated above, I can't do that for the clientele. I was thinking about simply shrinking the databases and then doing a DBCC CheckDB WITH ALL_ERRORMSGS on all of them before performing a backup. Would this be a complete enough procedure or should I be doing something else? My understanding is that this will verify index structure and data integrity, and not attempt to repair anything which is uncovered.
Any thoughts of wisdom would be greatly appreciated.
Thanks!
View 19 Replies
View Related
Nov 8, 2007
Hi,
I have created maintenance plan. It was working fine. When I tried to edit it gives me error. How to sollve this error?
Please help me.
Cannot show the editor for this task.
ADDITIONAL INFORMATION:
Value of '11/8/2007 12:00:00 AM' is not valid for 'Value'. 'Value' should be between 'MinDate' and 'MaxDate'.
Parameter name: Value (System.Windows.Forms)
View 1 Replies
View Related
Feb 1, 2008
Hi All,
As a part of creating a maintenance plan i want to copy the backfiles from the server to another machine.How can i achieve this .Please help its very urgent.
Thanks in Advance
RKNAIR
View 5 Replies
View Related
Feb 7, 2008
Hello everyone!
I was wondering if there were any best practices for creating maintenance plans?
Im just getting started into the DBA world and have been delegated the task of creating maintenance plans for our 8 SQL servers.
Right now, our backup policy is Fulls on Saturday, differentials Monday-Friday.
Also, since im new, if you defragment the database and rebuild the index, does that have the possibility of "breaking" anything?
Just looking for some good articles, or anything to get me started on best practices.
Much appreciated.
TCG
View 10 Replies
View Related
Mar 13, 2008
I am relatively new to SQL Server 2005. I have gone ahead and created a maintenance plan which backs up all our databases. This plan is scheduled to run every night. The problem is that each time the plan runs, new backup files are created which quickly uses up valuable disk space.
How do I set up SQL Server 2005 to only create one set of backup files and overwrite any existing files when the maintenance plan is run? I tried playing with the "backup set will expire" settings, this did not seem to do anything.....
Thanks
View 4 Replies
View Related
Dec 5, 2006
I have a SQL 2000 server that has a small but very important database (about 5GB). The current maintenance plan does trans logs every hour and full every day. Currently they are to file on the same drive array. I would like to send them to a share on another server just to be really safe.
Would it be better to
1. Redirect the maintenance plan so that trans logs and backups go directly to the share
or
2. Keep the maintenance plan back ups to the current location and write a script that runs every hour and copies the .bak files to the share.
Also, since the database is so small should I just do full backups every hour instead of transaction?
View 4 Replies
View Related
Mar 24, 2005
In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued.
Question:
If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?
-h
View 1 Replies
View Related
Jan 24, 2002
Are there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0
Thanks
View 1 Replies
View Related
Mar 12, 2002
Hi there,
We are using a database maintenance plan to backup and reindex our db's. Up until the end of last month this was working perfectly - however now it has stopped deleting the old backups (even though we have checked 'Delete files over 1 day old').
Does anyone have any ideas as to why they are now being deleted - and how we can remove them automatically - has something been corruped? Would it be a case of creating a new maintenance plan?
thanks in advance,
Matt
View 1 Replies
View Related
