What are the challenges you face when working across database platforms? Take the survey
Options

Backup Jobs Hanging

qsac226qsac226 Posts: 22
edited July 11, 2007 11:37PM in SQL Backup Previous Versions
Has anyone ever encountered an issue where the backup job just hangs?

The only way i have found to fix this is to restart the SQL Backup Service.

Some Background:
I have this installed on many different OS's and versions of SQL, both 32 and 64 Bit. We are strictly using the Professional version of SQL Backup. This problem is not consistent on one version of the OS or SQL, but random. Also to note, I run the script as a SQL Job.

Any thoughts?

Thanks
Q

Comments

  • Options
    Which version of SQL Backup are you running?

    Thanks,

    - James
    James Moore
    Head of DBA Tools
    Red Gate Software Ltd
  • Options
    4.6.0.815
  • Options
    I'm having this problem as well and I'm using the same version. It seems to only be happening on differential and full backup jobs though. My tlogs seem to run just fine.

    I currently have the jobs setup as follows:

    TLogs - every 15mins
    Differential - every 3 hours
    Full - every night

    Does it matter that the jobs overlap each other?
  • Options
    peteypetey Posts: 2,358 New member
    Would you guys be willing to run a debug version of SQL Backup that can help us to identify where exactly the process has hung? Backups and processes will run unchanged. The only addition is an extra function that will tell us what each SQL Backup thread is doing.

    Overlapping jobs should just cause the later job to reattempt a 2nd backup. E.g.

    - full backup starts at 1 a.m., takes 35 minutes
    - log backup starts at 1:15 a.m
    - log backup is blocked until 1:35, reports a failure, then reattempts the backup
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Peter,

    I seem to be experiencing a similar problem with backups hanging at random. I am running SQL Backup 4.6.0.815 Professional on on many different OS's and versions of SQL, both 32 and 64 Bit with both 32 and 64 bit editions of SQL Server.

    I don't seem to be able to recover from these issues until i restart the sqlbackup agent.

    Michelle
  • Options
    peteypetey Posts: 2,358 New member
    We found an issue with the MAILTO function in version 4.x, that may cause SQL Backup to hang, when the mail server does not respond/process SMTP commands in a timely fashion. Are you using the MAILTO option in your backups?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    HI Peter,

    We aren't using the mailto command
  • Options
    Hi An example of the The command we are running (taken from the redgate logfiles directory) is:


    12/07/2007 10:48:19 AM: BACKUP DATABASE [DBNAME] TO DISK = 'g:\backup\DBNAME\DBNAME_db_200707121048.BAK.SQB' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES = 2, COMPRESSION = 3
  • Options
    peteypetey Posts: 2,358 New member
    Could you pls download a debug version of SQL Backup from http://www.yohz.com/downloads/sqb4_6_debug.zip?

    In that archive, you will find SQBCoreService.exe and xp_sqlbackup.dll for Win32 and x64 platforms. Please replace the existing copies of the above files on a server that encounters the hanging issue most frequently. The xp_sqlbackup.dll file needs to be placed in the SQL Server Binn folder. You may need to unload the DLL from SQL Servers' memory space first, using
    DBCC xp_sqlbackup&#40;FREE&#41;
    
    The next time that a backup hangs, run the following from Query Analyzer/SSMS:
    EXEC master..sqbutility 9997
    
    This will generate a file named SQBCoreService_bugreport.txt in the SQL Backup folder. Send me that file. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    thanks peter. I have installed on three servers experiencing the problem and will send you the output when/if it happens again.
Sign In or Register to comment.