August 9, 2005 at 12:04 pm
I recently started having problems on one of my db servers with SQL Server Agent scheduled jobs that have worked without fail for years. A month ago, our Network Admin guys were in here one weekend applying some MS Updates and installing LiveVault (a real time, remote backup product). They tell me that the server in question rebooted during a tape backup effort (one guy was applying MS Updates while the other one was running tape backups) otherwise the installations went fine. LiveVault is no longer running on our network.
For 3 of the databases on the server, the nightly scheduled full backup to disk immediately began to fail with a "ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message 258) General network error.". This problem happens consistently, IOW it hasn’t worked ever since that weekend. This problem has the following characteristics:
1) The jobs fail regardless of whether I attempt to write the backup file to the database server or across the network to another server.
2) The scheduled backups for the other 6 databases on the server are working fine.
3) Newly created Agent jobs backing up those same 3 databases also fail.
4) The backups succeed without any problems when I run the command from query analyzer - and they succeed to either server.
5) Those backups done in 4) above, restore flawlessly.
6) DBCC CHECKDB shows no problems with any of the 3 databases.
7) DBCC CHECKDB shows no problems with the MSDB database.
8) DBCC CHECKDB shows no problems with the MASTER database.
9) The db's in question are behaving in a comletely normal way other than this issue.
10) The 3 db's are a variety of sizes, ranging from 7 Gig to 133 mb.
I appreciate any hunches that anyone might have for tracking this problem down.
August 9, 2005 at 12:45 pm
In case you haven't already, I would check what account the SQL Server Agent job is executing under (Job Properties, Owner). If it is set to an account that has been deleted or doesn't have access to the target directory, it would error out.
August 9, 2005 at 12:49 pm
Sorry, forgot to mention in my list that the Account is correct and has the proper access also.
Thanks.
August 10, 2005 at 9:21 am
Can you run the backup interactively ? If this is a maintenance plan, have you tried to drop/recreate it ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2005 at 9:25 am
I can run a backup successfully through Query Analyzer (per item 4). I cannot run it by manually starting the job in the Agent. It isn't a maintenance plan, I setup my backups myself.
August 10, 2005 at 10:27 am
By executing via QA I assume that you cut out the exact script from the Job Step. Are you backing up to a device or a file ? if a device, try dropping and recreating it. if a file, try a different location (drive/directory) ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2005 at 10:35 am
I back it to a file. I have already experimented with going to a different location. I have also both cut the exact script out of the job and written it from scratch. It always works in Query Analyzer and always fails from the Agent.
August 10, 2005 at 11:18 am
Are you performing 'VERIFY' in the backup ? If so try removing it. If it works without the 'VERIFY' then there is a fix I know of.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2005 at 11:27 am
Nope. No Verify. And the backup command is identical to that on the databases that aren't failing.
August 10, 2005 at 12:24 pm
I'm out of options except the very obvious ...
Could you post the scritp for one backup ?
Also, what is your current SQL version ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2005 at 12:46 pm
SQL Server 2000 SP3a.
As I said, this has worked without fail for over 2 years until the last month.
backup command is:
BACKUP DATABASE [MyDBApplicationname] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDBApplicationNameBkup' WITH INIT , NOUNLOAD , NAME = N'Backup, Full, MyDBApplicationName - Nightly', NOSKIP , STATS = 10, NOFORMAT
Thanks for all the input.
August 10, 2005 at 12:53 pm
This one is a real puzzler ... I'll continue to follow it until you've got a solution or until I think of something else ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2005 at 1:49 pm
Thanks. I'll keep digging and post the solution if I find one. Maybe I'll just keep doing the backups in Query Analyzer until I replace this machine with SQL Server 2005. <Grin>
September 8, 2005 at 1:45 pm
I'm having a very similar problem trying to execute a stored procedure using a job. It runs fine from query analyzer, but fails with Agent. I've checked permissions, and tried rebuilding the job, but no success. Could these two problems be related? Our servers were also recently upgraded wit the new security patches.
September 8, 2005 at 2:06 pm
Excellent. Finally, some evidence I can use to try to convince my wife and kids that I'm not crazy after all. It's not just me! <Grin>
I haven't found any solution yet. I've been backing up the dbs by "hand" every night in the interim, which will work fine for the company unless I get hit by a bus or something. I did a bunch of searching and read through the info about the security updates we installed and haven't found anything yet that's obviously relevant.
Can you successfully schedule other similar stored proc jobs? Perhaps on different databases on the same server?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply