SQL Agent job failing after backing up 3-4 databases.

Question

SQL Agent job failing after backing up 3-4 databases.

TheoRyan

SSC Rookie

Points: 34
More actions
October 28, 2019 at 6:46 pm

#3692507

Currently running SQL Agent jobs to create Back-ups for HPStoreOnce on a 2008 R2 server. When the 2-Hour log back up runs it backs up 3-4 Databases then the step fails. The “Log in table” and “Include step output in history are checked” in Steps Advanced. When I check view History all it gives me is “step failed”. I check the SQL Server Logs and shows what databases got back up and the “step fails” statement. The agent step is set to T-SQL statement that runs on 5 other 2008 R2 servers with complete success. I have copied the code from the servers that are running successfully. Using this code I still get the same results.
I have ran the T-SQL code manually and it works find backing up all databases that meet the criteria. I have tried to limit the tables back-up to see if it is one of my databases causing this issue. When I set up the code to start halfway down my list of databases it repeats the same behavior of backing up 3-4 databases then gives a step fail. I have limit the run to include just 3 databases and it runs success. If I raise that number to 4 or higher the job fails at the fourth database. It is set up for just databases with recovery model of “Full”. I also have created entire new SQL job. When it ran the results was the same. Has anybody else ever ran into similar issue with a SQL Agent job?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

Have all of the databases had a FULL backup made of them? I am not sure if SQL 2008 R2 is this way, but transaction logs are not kept, until a FULL backup is done.

Also, were the first three databases master, model, and msdb, or were they user databases?

TheoRyan SSC Rookie Points: 34 More actions · Answer 2

Ran a FULL database backup and then ran the agent for the Log Backup with the same results. It always goes in alphabetical order as the script creates a temporary table that is filled with a insert statement from sys.databases. the only table excluded is the 'tempdb'. Master, model, and msdb do not get reached with this current issue of stopping after 3-4 databases.

Lowell SSC Guru Points: 323518 More actions · Answer 3

I have had some scripts like that error int he middle do that, and it was because my \\unc path was out of space. i could do the first few databases, but the estimate for one Whopper database was more than the space available, and it reported failure.

could that be the issue here?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

In the Advanced tab for the job step, specify an output file for the job step. That is better than the job history which has a limit in number of chars.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

TheoRyan SSC Rookie Points: 34 More actions · Answer 5

Created a work around with using .bat file. Created a stored procedure that gives all the information the original TSQL script for the job agents that were failing. Created a .bat file that executes the new stored procedure and tested in cmd with complete success. Created new job agent that runs on schedule that calls on the .bat file. So far all back ups are now working without errors. Still do not know what is causing the original error in SQL Agent. Very weird. thank you for all your input. If I ever figure out what is causing this I will post it here.