Frustrating Ola Hallengren log file (ldf) backup issue

Question

Frustrating Ola Hallengren log file (ldf) backup issue

Alan Burstein

SSC Guru

Points: 61141
More actions
May 8, 2018 at 10:37 am

#410484

This is one of those - the DBA quit, so let's have the BI developer fix it situations. I'm the BI guy pulling my hair out on this...
On our production servers we do Full, Diff and Transaction log backups, we've been using Ola Hallengren for years across a couple dozen servers without issue. In the SQL agent job step we log the results to an output file. The format looks like this:

(<SAN> and <Folder> represent the actual names/locations)
\\<SAN>\<folder>\DatabaseBackup_USER_LOG_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

The command we use is:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d <ourDB> -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'\\<SAN Folder>\<backup folder>\', @BackupType = 'LOG', @verify = 'Y', @Compress = 'Y', @ChangeBackupType = 'Y', @CleanupTime = 168, @checksum = 'Y', @LogToTable = 'Y'" -b
This command and log file locations are uniform against all servers with the only differences being the @backuptype (Full, Diff or Log) and @databases (USER or SYSTEM). Everything else is exactly the same. All backups are all logged to the same SAN location into a folder with the same name as the SQL Instance running the job.
The transaction log backup on just one of our instances runs every 30 minutes and fails 2-3 times per day at random times with the error:
Message: Executed as user: <USER> Unable to open Step output file. The step failed. And that's it.
I've recreated the job, tried different folder locations (just to troubleshoot) which does not fix the problem. I can just have it stop logging to an output file as the DB's on this server aren't very important but I'd rather not. I thought I'd first see if anyone else has run into this and gotten around it. Thanks!
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001

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

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

I'm sure you've tried this already, but have you tried explicitly granting permissions (to the service account) to the folder containing the output file? Or have you compared its permissions with those of the other service accounts whose jobs are logging successfully?

Sorry if that's all a little obvious :).

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Beatrix Kiddo - Wednesday, May 9, 2018 6:12 AM
I'm sure you've tried this already, but have you tried explicitly granting permissions (to the service account) to the folder containing the output file? Or have you compared its permissions with those of the other service accounts whose jobs are logging successfully?
Sorry if that's all a little obvious :).

Thanks.
We're using the same account for each job on each server and, since the job is successful ~21/24 times/day I don't think it's related to permissions. 🙁

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

Is it failing consistently at a particular time? If so, what else is going on then?

Thomas Rushton
blog: https://thelonedba.wordpress.com

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Maybe that server is having problems contacting AD at the time of the failures? I would check the windows event log and see if you can find anything around the time of those failures.

Sue

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Thanks folks for the replies. After a little more research I discovered that the problem was related to periodic connectivity issues between the server and the SAN. I never saw problems with the Full and Diff backups because those are only run once/week for Full, daily for Diff. My SAN & network folks are dealing with this.

Cheers!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001