SQL Backup 8 - Thread Errors

jcricegpjcricegp Posts: 18 Bronze 3
edited August 25, 2016 2:31PM in SQL Backup
I recently started deploying the new SQL Backup 8 application to my development environment. I am getting RedGate Error 790 (Thread 0 error) on several servers. These errors occur on different databases. These errors occur on different backup days. The errors have occured on both physical hardware and VM Guests.

The last SQL Error can be one of the following:
SQL error 5901: One or more recovery units belonging to database '<DBName>' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database
corruption. Examine previous entries in the error log for more detailed information on this failure.

SQL error 1222: Lock request time out period exceeded.

My Activity Log shows the following parameter values:
BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE = 'SQLBACKUP_64D49B7F-BE59-4E78-8D85-A6FDD62CC71F' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (msdb), 7/23/2016 5:29:10 AM', DESCRIPTION = N'Backup
on 7/23/2016 5:29:10 AM Server:<SQLServer> Database: msdb', FORMAT, CHECKSUM

I believe that BLOCKSIZE & MAXTRANSFERSIZE are the defaults. Should these be changed in SQL Backup 8?

Thanks,

Comments

  • peteypetey Posts: 2,358 New member
    That is a SQL Server error. You might want to google for additional information on that error code (SQL error 5901) as there are a few possibilities as to the cause.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • jcricegpjcricegp Posts: 18 Bronze 3
    The backup error does show that this is an SQL Error. However, these errors did not occur in SQL Backup Editions 7.7 and earlier. Now with SQL Backup 8.0, I have 1 - 4 failures each night from different servers. The backup job will backup several databases successfully, then one database backup will fail with a RedGate 790, then the remaining databases will backup successfully.

    I have also conducted additional testing and have found that setting Minimum Memory for the SQL instance has eliminated most of these errors, but I still have errors on several servers during the week. And even this change does not eliminate the problem on some servers.
  • peteypetey Posts: 2,358 New member
    Could you please send me (peter.yeoh@red-gate.com) a complete log of a backup run that generated the error? I would like to see the backup command and options used, and also the events leading up to the error. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • jcricegpjcricegp Posts: 18 Bronze 3
    Here is the Backup Log from one database that has failed 5 times since Version 8.0 was installed on 19 JUL 16. Not sure what you mean about the events leading up to the issue. We have a job that selects all online databases and backs them up one by one. This is the fourth backup of 20. All database before this one and all databases after this one was successful.

    SQL Backup log file 8.0.0.814

    -SQL "BACKUP DATABASE [BI_TRN_NA_ODS] TO DISK = '\GPGBYDBDUMP02I$BACKUPSTier4CPGBYTRAND04_DEV@BI_TRN_NA_ODS@FULL@20160730@030122.sqb' WITH CHECKSUM, COMPRESSION = 2 "

    ERRORS AND WARNINGS


    7/30/2016 3:01:22 AM: Backing up BI_TRN_NA_ODS (full database) on DEV instance to:
    7/30/2016 3:01:22 AM: \GPGBYDBDUMP02I$BACKUPSTier4CPGBYTRAND04_DEV@BI_TRN_NA_ODS@FULL@20160730@030122.sqb

    7/30/2016 3:01:23 AM: BACKUP DATABASE [BI_TRN_NA_ODS] TO VIRTUAL_DEVICE = 'SQLBACKUP_D0A530AB-8F87-4843-BFC5-76859CB2BD7B' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (BI_TRN_NA_ODS), 7/30/2016 3:01:22 AM',
    DESCRIPTION = N'Backup on 7/30/2016 3:01:22 AM Server: CPGBYTRAND04DEV Database: BI_TRN_NA_ODS', FORMAT, CHECKSUM

    7/30/2016 3:01:25 AM: Thread 0 error:
    Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
    7/30/2016 3:01:25 AM:
    7/30/2016 3:01:26 AM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
    7/30/2016 3:01:26 AM: SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_D0A530AB-8F87-4843-BFC5-76859CB2BD7B:" 995(The I/O operation has been aborted because of either a thread exit or an application request.).
    7/30/2016 3:01:26 AM: SQL error 1222: Lock request time out period exceeded.
    7/30/2016 3:01:26 AM: SQL error 5901: One or more recovery units belonging to database 'BI_TRN_NA_ODS' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database
    corruption. Examine previous entries in the error log for more detailed information on this failure.
  • peteypetey Posts: 2,358 New member
    Thanks for sending the logs over. There is indeed a bug which is causing the lock timeout error, which will be addressed in a update that will be released soon.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi jcricegp,

    This issue has been resolved in the latest version of SQL Backup 8, 8.0.1.405.

    Please upgrade and let me know if this problem is still occurring.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • jcricegpjcricegp Posts: 18 Bronze 3
    Dan,

    I have deployed the change to several of the servers that were failing consistently. So far I have not had any recurrence of the thread issue. If I have no problems in the next few days, I will be redeploying to my development environment next week.

    Thanks,
  • jcricegpjcricegp Posts: 18 Bronze 3
    Dan,

    I have run several days with the SQL Backup Patch (8.0.1.405) and have not had any errors. I consider this problem fixed.

    Thanks,
Sign In or Register to comment.