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)
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