SQLBackup 4 errors after a few days of running

AlanMullockAlanMullock Posts: 8
edited April 14, 2006 10:06AM in SQL Backup Previous Versions
I've recently upgraded one of my servers from SQLBackup v3 to SQLBackup v4.

After installation, I've configured a number of SQLServer scheduled tasks to back up my databases using calls to sqlbackup extended proc.

These all work fine for a few days, however after a varying period of time, all backups will start failing with the following message.

Execute interrupted by exception: Access violation at address 14B382C3 in module 'xp_sqlbackup.dll'. Write of address 00000000 [SQLSTATE 01000] (Message 0) BACKUP OF DATABASE msdb Failed. [SQLSTATE 42000] (Error 50000). The step failed.

When this starts to happen, attempting to use the user interface and connect to the offending server also results in no connection being made and the exception message appearing in the lower left hand pane of the the interface.

Attempted restarts of the SQLBackup service do not remedy the problem.
And the only resolution I've found so far is to remove the server components and re-install them. This results in the backups starting to work again, but only for a few days, after which the above symptoms return.

I have three questions:
1) Why is this occurring?
2) Is there a fix/reconfiguration available to cure it?
3) If it happens is there a better resolution?

Thanks,
Alan.

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls provide the following details:
    - frequency of backups, approximately how many backup tasks run before they start failing
    - are you backing up to a network share?
    - do you have one or more SQL Backup tasks running concurrently?
    - are you using SQL 2000 or SQL 2005?
    - are you running a 32-bit or 64-bit OS, and which version?

    The next time it fails, could you pls try running

    DBCC xp_sqlbackup(FREE)

    and see if it solves the problem?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • To give some more details on this backup implementation:


    Frequency
    Server has 13 databases. 2 Different backup regimes are used depending upon whether DB is in simple recovery mode or Full.

    Regime 1 (Simple) - Daily Full Backup - Used on 8 databases
    Regime 2 (Full) - Full Backup once/week - Differential once/day - Transaction Log Once/Hour - Used on 5 databases.

    Jobs had been running successfully from 16/3 to 21/3 before starting to fail again. So therefore 5days worth of backups had run around 670 backup events had occurred in between failures.

    All Backups occur to Local disk.

    Using the above schedule, the most backups that are running simulatenousely are 8.

    SQL Server is SQL2000 running on 32bit Windows 2000.

    Ran DBCC xp_sqlbackup(FREE) for 15 mins without it completing.

    Reboot of server (for unrelated reason) has enabled backups to commence running again.
  • peteypetey Posts: 2,358 New member
    The 8 full backups (regime 1), do they all kick off at the same time, or are they ran sequentially, one by one?

    Are there any backup failures, prior to the generic xp_sqlbackup error?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Yes, in Regime1, each backup is a seperate SQLAgent job, all of which have the same start time. Which means that we have 8 backups starting/running concurrently.

    What is interesting about the initial failures is that they where presented by the Transaction Log backups. To simplify the pattern, it went something along these lines:

    Hour 1 - All 4 concurrent transaction log tasks succeed.
    Hour 2 - 1 Transaction Log backups succeeds - 3 fail with the error message given.
    Hour 3 (onwards) - All transaction logs and all future backups fail.

    Thanks,
    Alan.
  • peteypetey Posts: 2,358 New member
    Hour 2 - 1 Transaction Log backups succeeds - 3 fail with the error message given.
    Could you pls send me (via private message) the contents of the log for the 1 trx log backup that succeeded? The log is found in the <system drive>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> folder.

    Could you also send me the trx log backup commands for the 5 trx log backups?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • We have seen this once using 4.0 on a server we restore transactions logs to hourly. We restarted the SQL server service to resolve it. We then updated to 4.1 and have not seen the problem come back.

    Looking at our Red-Gate logs showed a sucessfull restore of a transaction log prior to it stop working.

    Deleting database file 'e:\MSSQL\BACKUP\Datacenter_StandBy.bak'.
    RESTORE LOG successfully processed 0 pages in 1.336 seconds (0.000 MB/sec).

    We are running SQL Server 2000 SP4 on Windows 2003 Server SP1 32Bit
  • @version will tell you this).
    - Neil Davidson
    Red Gate Software Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    Can you please email us at [email protected]? We have fixed a problem in the extended stored procedure yesterday that should prevent the ACCESS VIOLATION errors. Please make sure you let us know the version of the xp_sqlbackup.dll that you currently have installed, and I'll reply with the fixed dll.

    Thanks!
Sign In or Register to comment.