SQL Backup ending incompletely
bbrown
Posts: 5
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.
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
thanks,
Helen
SQL Backup Project Manager
Red Gate Software
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8