0

An SSA database restore job fails sporadically with this error: String data, right truncation [SQLSTATE 01004]

This job runs daily, and succeeds most of the time. When it fails it is on a Friday and it leaves the database in a restoring state. The code inside the job is the exact same code used for other database restore jobs where this is not a concern.

My solution each time this happens is to delete the database and manually restore it. After manually restoring, I run the job and it runs fine. It continues to run fine every day from that point on until it doesn't.

I checked the SQL logs and I do see this error: "spid75,Unknown,BackupDiskFile::OpenMedia: Backup device '\the_path_to_the_backups' failed to open. Operating system error 5(Access is denied.).,,,,". So, I checked to ensure the user has permission to access to the .BAK file at the folder level and the file level. It does have full access.

Any thoughts on how I can stop this job from failing randomly?

EDIT: This is the code from that job.

  USE DBA_Tools;
GO

EXECUTE
    DBMaintenance.restoreBackupChain
        @BackupFolder = '\\servername\sqlbackups2$\instancename\Database_Backups\DSOG',
        @Database = 'DSOG';

USE [DSOG]
GO
EXEC sp_change_users_login 'Auto_Fix', 'reportingServicesUser'
GO
EXEC [DSOG].dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
USE [master] ;
ALTER DATABASE [DSOG] SET RECOVERY SIMPLE ;
GO
Unnamed DBA
  • 35
  • 6
  • *"the user has permission"* which user? SQL Server runs under a special service user, does that have permissions? Please also show the code for the job – Charlieface Feb 24 '22 at 00:27
  • The SQL Server user has the correct permissions and so does the SQL Server Agent user. – Unnamed DBA Feb 28 '22 at 14:38
  • How does it have correct permissions? Normally those users are local to the SQL Server machine, they are not normally domain accounts. Backup and restore over a network share is often going to cause trouble, both because of permissions and because of intermittent connections – Charlieface Feb 28 '22 at 14:42
  • In our case, they are indeed domain accounts. FYI - I didn't set it up that way and I don't have the power to change it (junior DBA here). I have been asked to troubleshoot it. With that in mind, any ideas on what I can do to reduce or stop the failures? – Unnamed DBA Feb 28 '22 at 16:47
  • Possibly `servername` is relying on DNS and that is failing for whatever reason? Hard to say, I suggest you speak to your server team – Charlieface Mar 01 '22 at 11:06

0 Answers0