October 22, 2008 at 5:04 pm
Hi Guys,
I've come across a strange issue. I had a backup plan running successful for the pass year running like a dream. Then last night we noticed that the backup drive required a defrag, so we set one up to run over night.
Come the morning i noticed that the backup plans have failed with the following message 'Unable to start execution of step 1 (reason: line(1): Syntax error'. I tried to run the plan manually i get the same, i create a brand new plan i get the same... I can backup up the db individually.
I can't find any doco regarding this.. can anyone help 🙂
Cheers,
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
October 23, 2008 at 8:08 am
I think the disk defrag is probably a coincidence. What service pack of SQL Server are you running? I had that same problem after applying SP1 to one of my virtuals. I looked but I couldn't find the KB article that describes it.
Can you post the specific/full error and the TSQL from the subplan? (From the Maintenance Plan editor, click on the properties and use [View TSQL] button)
Chris.
Chris.
October 23, 2008 at 4:28 pm
Hi Chris,
Thanks for your response, as your requested,
-Server version is SQL 2005 9.00.133.06
-Error statement
Date23/10/2008 8:30:00 PM
LogJob History (Daily Backup.Daily Backup)
Step ID1
Server--Name Withheld --
Job NameDaily Backup.Daily Backup
Step NameDaily Backup
Duration00:00:00
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
-T-SQL
EXECUTE master.dbo.xp_create_subdir N'F:\\--Name Withheld --'
GO
EXECUTE master.dbo.xp_create_subdir N'F:\\--Name Withheld --'
GO
EXECUTE master.dbo.xp_create_subdir N'F:\\--Name Withheld --'
GO
BACKUP DATABASE [--Name Withheld --] TO DISK = N'F:\\--Name Withheld --\--Name Withheld --_backup_200810240916.bak' WITH NOFORMAT, NOINIT, NAME = N'--Name Withheld --_backup_20081024091631', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [--Name Withheld --] TO DISK = N'F:\\--Name Withheld --\--Name Withheld --_200810240916.bak' WITH NOFORMAT, NOINIT, NAME = N'--Name Withheld --_backup_20081024091631', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [--Name Withheld --] TO DISK = N'F:\\--Name Withheld --\--Name Withheld --_backup_200810240916.bak' WITH NOFORMAT, NOINIT, NAME = N'--Name Withheld --_backup_20081024091631', SKIP, REWIND, NOUNLOAD, STATS = 10
If i run the T-SQL in a query it runs sweet, also i've created a maintenance plan to rebuild fragmented indexes and i'm recieving the same error, so it's unlikely to be an T-SQL issue. Gotta admit its startin to do my head in!!
Cheers,
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
October 23, 2008 at 5:40 pm
Mark,
Is that version correct? I can't find it listed anywhere.
Chris.
Chris.
October 23, 2008 at 5:43 pm
Hi Chris
select @@version returns the following
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Cheers,
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
October 23, 2008 at 5:57 pm
Thanks, that makes a big difference over the one you posted earlier.
I do not know what caused your error to start, but what I can tell you is that this is the RTM version, meaning it has no service packs applied at all. I have seen this happen on pre-SP2 servers. Any chance you could apply SP2 to it?
Chris.
Chris.
October 23, 2008 at 6:13 pm
Chris,
Yeah i'm pushing to apply the Service Packs but the powers that be are afriad that in doin so will cause failures in our existing systems, which is why it hasn't been done yet. With exception of applying a SPack can you offer any advice?
Cheers
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
October 23, 2008 at 6:36 pm
Hey Mark,
I figured as much. If I were absolutely stuck I would try a few things. (The first is preferred)
You could write your own backup database job. Just put the backup database commands as 1 big or separate steps in a SQL Agent job. (Latter is preferred) Use a maintenance cleanup task in a SSIS package to age the files, or grab forfiles.exe from the resource kit and write your own.
You could try removing the "GO"s, wrapping it all up in a stored procedure and calling the stored proc throught the SQL Agent.
Chris.
Chris.
October 23, 2008 at 6:56 pm
Chris,
I'm glad we're thinkin on the same level because i've already created T-SQL backup plan and have forfiles, copying and removing .bak across the network. Its just annoying that i can't figure out why it all the maintenance plans are coming up with the same error.
Cheers.
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
November 6, 2008 at 2:12 am
I am having issues with this also.
The syntax Error appears to be in the filepath to the SSIS package that is used to execute the maintenance plan.
If you go into the job that is used to call the package, edit the job step and select the appropriate SSIS package from within the maintenance plan folder, you will find that a '\' is added to the filepath.
Executing the job now should work.
Cheers,
November 6, 2008 at 4:00 am
Cool,
That's great to know, thanks for the update.
🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
November 23, 2008 at 4:42 pm
Cheers John,
That worked a treat 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply