SQL Backup ending incompletely

bbrownbbrown Posts: 5
edited August 31, 2007 9:17PM in SQL Backup Previous Versions
I posted this problem in the SQLServerCentral forums. Someone from Red Gate suggested I post it here.

We have recently changed the backup on servers 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 all databases(system and user) or a single database, 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 all databases(system and user) are backed up every night. There are two user 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. I have been checking the SQL Server logs, the event viewer and the SQL backup logs. In all cases, there are no error messages and no messages that a retry was attempted. I have recieved messages on other servers that a log backup failed and was retried, but not in this case. I only see a message that the first database has been backed up.

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?

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.

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

Comments

  • peteypetey Posts: 2,358 New member
    Can you post the SQL Backup command that's generated by your stored procedure?

    If two backup requests are submitted for the same database, one backup will run, while the other will be blocked until the first backup ends. If the first backup ends within 30 seconds, the second backup will then run. Otherwise, the second backup will end with a timeout error, and the backup is reattempted.

    Each backup request uses a different virtual device name (using GUIDs'), so there should be no collisions.

    I do not understand your statement re 'stored procedure execution plan'. Could you pls explain?

    Which version of SQL Backup are you using?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • We are running SQL Backup 4 on a SQL Server 2005 box.

    The stored procedure producte the following commands:
    EXEC (master..sqlbackup N' -SQL "BACKUP DATABASE [DBAShared] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\DBAShared\RedGate_FULL_DDPOKVC1_DBAShared_20070829_140122.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, VERIFY, ERASEFILES_ATSTART = 2, COMPRESSION = 3"')
    EXEC (master..sqlbackup N' -SQL "BACKUP DATABASE [master] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\master\RedGate_FULL_DDPOKVC1_master_20070829_140122.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, VERIFY, ERASEFILES_ATSTART = 2, COMPRESSION = 3"')
    EXEC (master..sqlbackup N' -SQL "BACKUP DATABASE [model] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\model\RedGate_FULL_DDPOKVC1_model_20070829_140122.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, VERIFY, ERASEFILES_ATSTART = 2, COMPRESSION = 3"')
    EXEC (master..sqlbackup N' -SQL "BACKUP DATABASE [msdb] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\msdb\RedGate_FULL_DDPOKVC1_msdb_20070829_140122.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, VERIFY, ERASEFILES_ATSTART = 2, COMPRESSION = 3"')
    EXEC (master..sqlbackup N' -SQL "BACKUP DATABASE [VirtualCenter] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\VirtualCenter\RedGate_FULL_DDPOKVC1_VirtualCenter_20070829_140122.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, VERIFY, ERASEFILES_ATSTART = 2, COMPRESSION = 3"')

    What I mean by execution plan is the compiled executable that is cached for each stored procedure.

    Also, as I mentioned, I have seen a Log backup and a Database backup attempt to execute at the same time and I have seen the Log backup abort, then retry and execute ok. However, these are on SQL Server 2000 boxes and also give error messages.
  • peteypetey Posts: 2,358 New member
    Your stored procedure appears to be generating individual backup commands for each database. I take it that only the backup of DBAShared succeeded, while the rest were not ran. Did the backup of DBAShared end with a warning? If it did, what would your stored procedure do? What about the transaction log backup that was set to run at the same time, did it succeed for both DBAShared and VirtualCenter?

    From your trace, can you tell if backup commands were issued to SQL Server for the rest of the databases? They look similar to the regular backup commands, but use VIRTUAL_DEVICEs instead.

    As regards to the SQL Backup log files (default folder is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>) for the full database backups, was there only one generated for DBAShared, or were there more, generated for the other databases?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I've moved this post from the version 5 forum to the SQL Backup v4 forum.
    thanks,
    Helen
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
  • All the databases are backed up, and log files were generated, except for the last database(VirtualCenter). There are no error messages in the SQL Logs, the Event viewer, or the Red Gate logs. My trace showed backup commands were issued for all databsaes except the last one. The backup job started at 7:45 PM. The first four databases took less than a minute to backup. At 7:46 PM, a transaction log backup for the last databse was started. All I see in the logs for the last database, is the log job.
  • peteypetey Posts: 2,358 New member
    If possible, could you pls try running the full database backup script at a time when the transaction log backup script is not running? Do all 5 database then get backed up? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.