0

I have several SQL Server Jobs which successfully write the output of the job to a local drive on the server.

I created a new job today using the exact same path, the job is succeeding but I get the message

[SQLSTATE 01000] (Message 0) Unable to open Step output file. The step succeeded

and there is no file created or written to. I changed the output to a different directory and it works fine. I am really confused how one job works fine writing to the same directory, yet another doesn't, especially when I created both jobs myself.

Both jobs are executed by the same Service Account and have the same job owner. Any help would be appreciated. I am running SQL Server Enterprise on 2014

Dale K
  • 25,246
  • 15
  • 42
  • 71
Lee
  • 13
  • 1
  • 4
  • This sounds like a permissions problem on the location of the file. However, it is also possible that the drive has no free space. – Gordon Linoff Jul 27 '20 at 10:35
  • There is 2.6GB free on the drive. I thought permissions too but when other jobs can write to the same location it doesn't make sense to me – Lee Jul 27 '20 at 10:47
  • `2.6GB` isn't a lot of space in the real world any more. Also, jobs can run under different credentials, so perhaps the job is running under different permissions and that's why. – Thom A Jul 27 '20 at 10:51
  • It's being executed as the same user as the one that can write to the file (service account of the instance). I'm not using Execute As or anything like that, it's a simple script which writes a Print Statement to a file after it completes. The file should only be around 5KB so I dont think space is an issue – Lee Jul 27 '20 at 10:59
  • `EXECUTE AS` doesn't change the account the step is run under, it's a step setting. A step running a statement that includes an `EXECUTE AS...` statement will still start has the account that the step is been asked to run at. Normally using `EXECUTE AS` inside a step is an XY problem anyway, as you *should8 be running the step under a separate account using the steps/jobs settings. – Thom A Jul 27 '20 at 11:02
  • There is no EXECUTE AS in any parts of the 2 jobs or Stored Procedures that they call. When looking at the job output I can see that both jobs were executed as the same User. One can create the file, one cannot and both are pointing to the same directory – Lee Jul 27 '20 at 11:41
  • Got to the bottom of the issue. I created a blank file and compared the permissions that were on the successful file and a new file. Write permission was missing, so looks like at some point recently the permissions on the drive were changed at the folder level, but not reflected through the existing files. Which meant that the existing jobs were successful to writing to the file but any new ones were not, as they didnt have write access. – Lee Jul 27 '20 at 14:18

1 Answers1

0

I was appending to a log file that had grown to large, i.e. 2GB and sql qwas failing to open the file, archived the file and started a new one and everything was back to normal enter image description here

aharon
  • 59
  • 2