Post reply

Backup job finishes incomplete

  • August 22, 2007 at 8:36 am

    #180327

    We have recently changed the backup on server with large databases to use Red Gate SQL Backup.  In order to automate the scheduling of backups, we created a stored procedure which accepts parameters of the type of backup(database or log), the backup retension period, and the compression level to use.  The stored procedure the executes the master..sqlbackup stored procedure with these parameters.

    One server has a very high activity volume, and has it's transaction logs offloaded every 15 minutes.  The databases are backed up every night.  There are two databases on this server.  We have recently found that the database backup is backing up the first database and then quitting with no errors.  During the backup job run, a transaction log backup is being submitted and executing.  Both these jobs are running through the SQL Agent service. 

    Is it possible that, because both jobs are executing the same stored procedures, that when the transaction log job ends, it is causing the system to believe the database job has also ended or is it due to both jobs using the same virtual backup device? 

     I thought that each would have it's own copy of the stored procedure execution plan, but is that only for users?

     

  • August 23, 2007 at 5:49 am

    #728859

    A few troubleshooting ideas:

    - Run the sproc that backups up the databases in a query window to see if any errors are generated

    - Have you tried (as a test) to run the same backups via the SQLBackup interface to see if the behavior changes?

     

    Have a good day,

    Norene Malaney

  • August 23, 2007 at 7:17 am

    #728896

    We tested the sproc on out test and development servers before we placed it into production.  This sproc is running on several production servers with no problems.  We have also ran backups thru the interface( I assume you mean the GUI) when the product was installed, in order to test the installation.

  • August 23, 2007 at 8:04 am

    #728935

    Hi Brian,

    You might be better off in our dedicated support forums - http://www.red-gate.com/messageboard/viewforum.php?f=64. It will then be tracked by our support department properly rather than being answered by a passing developer

    However as you have posted here somthing worth looking at would be the actual sql passed to master..sqlbackup - can you put a trace on the server when the backup should be running and let us have a copy of the actual SQL executed.

    Second if you check the SQL Backup logs it should let you know what is going on in each backup - you can find these in the logs path specified in the UI's option dialog.

    Microsoft limits us to running a single backup against each database at once - if you run a transaction log backup and a full backup at the same time then, even though we will retry the backup several times, one of them might fail with a timeout.

    Regards,

    James

     

    --
    James Moore
    Red Gate Software Ltd

  • August 24, 2007 at 8:06 am

    #729277

    I am trying to put a trace on the server to capture the sql execution. 

    I have been checking the SQL Server logs, the event viewer and the SQL backup logs.  In all cases, there are no error messages.  Only a message that the first database has been backed up.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply