1

I have a job which runs every 30 minute and takes t-log backup of the database suing Ola's script. Every Thursday and Friday at 22:45 I get an error:

enter image description here

11/07/2019 22:45:00,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
11/07/2019 22:45:00,spid60,Unknown,BackupDiskFile::CreateMedia: Backup device 'B:\SDSOFT-DB\EXPRESS\LOG\SDSOFT-DB_EXPRESS_LOG_20191107_224500.trn' failed to create. Operating system error 5(Access is denied.).
11/07/2019 22:45:00,spid60,Unknown,Error: 18204<c/> Severity: 16<c/> State: 1.

enter image description here

I asked sysadmin to check all settings regarding NAS storage and he said that everything ok and he doesn't know what is the cause of this problem. From my side, I mapped NAS storage network path into 'B' using xp_cmdshell(SQL SERVER 2014) by the help of the following command:

EXEC xp_cmdshell 'net use B: \\192.*.*.*\DBbackups /user:<username> <password> /persistent:yes'

I would like to point out again that I get no error during the rest of the time, only on Thursday and Friday at 22:45. Maybe someone had faced with this issue before and help me to tackle this problem.

  • Can you please post your error message as text rather than an image? – George.Palacios Nov 13 '19 at 10:56
  • I posted as a text along with the image – RaufDBA Nov 13 '19 at 11:09
  • If access is denied on a regular basis, it should be an automated process running. Probably one that is changing the security settings temporarily. I would try and run a powershell command to return the security settings at that time. Such as (Get-Acl -Path "backuplocation").Access. More on that here – Randi Vertongen Nov 13 '19 at 11:20
3

Operating system error 5(Access is denied.)

This error is somewhat cryptic, but it is helpful. This error is coming from Windows when it tries to write to file. Windows then passes the error message along back to SQL Server, who dutifully tells you exactly what it was told--unfortunately without a better explanation.

"Operating system error 5" generally means that you have access to that location, but not write access. For example, if your SQL Agent service account is only granted read permission on a folder, and you try to write backups there, you would see this message.

Except in your case, permissions work most of the time.

Your backups only fail late at night, then recover and start working again. That means it's probably not a "normal" permissions issue. Your target folder is on a NAS, so probably there is a scheduled process on the NAS that is impacting your backups.

Given the timing, I might suspect that your NAS is being backed up at that time, and doing something that is affecting your ability to write there. A lot of commercial file system backup software will do something to momentarily pause writes so that it can have a consistent image to back up, then quickly snapshot the storage and begin allowing writes again. It's plausible that you are hitting a backup process like this, or similar.

If that's the case, then I'd first evaluate adjusting the backup schedule to minimize impact. Depending on your RPO/RTO requirements, it may be perfectly ok to have one transaction log backup fail, so long as the next execution runs ok. If that's the case, just document the reason, and start ignoring the 22:45 errors.

| improve this answer | |
  • Thank you sir. Great explanation and suggestion. – RaufDBA Nov 13 '19 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.