Server locks upon execution of scheduled command line backup
LyndyB
Posts: 3
We are reseller of SQLBackup application. This problem has occured at the first client site we have installed SQLBackup at. I created the following command line backup entry and set to run on a Windows 2000 server every night at 22:15. It is designed to segment the 4 GB RES@SQL database into 3 separate files:
C:\PROGRA~1\REDGAT~1\SQLBAC~1\SQLBackupC.exe -SQL "BACKUP DATABASE [RES2SQL] TO DISK = 'C:\Hudson\Backups\A1limo-1', DISK = 'C:\Hudson\Backups\A1limo-2', DISK = 'C:\Hudson\Backups\A1limo-3' WITH NAME = 'Database (RES2SQL), 8/3/2005 9:12:14 AM', DESCRIPTION = 'Backup on 8/3/2005 9:12:14 AM Database: RES2SQL Instance: (local) Server: SEQUEL', INIT, VERIFY, COMPRESSION = 1" -E
Microsoft Sequel Server does a scheduled backup (single file) of the same RES2SQL database nightly at 22:00 and never seems to fail. It then backs up the Transaction Log nightly at 22:30.
The command line SQLBackup procedure will work for up to 10 days or so, and then will fail, at the time it tries to start. When this happens, the Server will "freeze" and reboot is required in order to return server to service. When the freeze-up occurs, there is no error in the log indicating what may have transpired at the moment of failure.
This Log shows successful Enterprise Manager backups at 22:00 and 22:30 and successful SQLBackup at 22:15 on November 11 and 12:
2005-11-11 22:02:09.17 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 333906, first LSN: 90486:2346:1, last LSN: 90486:2427:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_db_200511112200.BAK'}).
2005-11-11 22:16:00.46 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 334704, first LSN: 90486:2757:1, last LSN: 90486:2775:1, number of dump devices: 3, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_195436317', 'SQLBACKUP_19543631701', 'SQLBACKUP_19543631702'}).
2005-11-11 22:30:07.85 backup Log backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), first LSN: 90366:1528:1, last LSN: 90486:3197:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_tlog_200511112230.TRN'}).
2005-11-12 22:02:08.38 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 334073, first LSN: 90581:275:1, last LSN: 90581:296:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_db_200511122200.BAK'}).
2005-11-12 22:16:01.39 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 335488, first LSN: 90581:818:1, last LSN: 90581:831:1, number of dump devices: 3, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_196484893', 'SQLBACKUP_19648489301', 'SQLBACKUP_19648489302'}).
2005-11-12 22:30:05.39 backup Log backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), first LSN: 90486:3197:1, last LSN: 90581:1343:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_tlog_200511122230.TRN'}).
This is the last entry in the log from a night when the SQLBackup application locks the server when it tries to start at 22:15:
2005-11-22 22:02:20.57 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 347179, first LSN: 91896:4965:1, last LSN: 91896:5082:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_db_200511222200.BAK'}).
The next log entry shows server being restarted...
Do you see any problem with the command line backup string?
Have you seen any compatibility problems with Server 2000 previously when trying to split the backup files?
Any suggestions would be greatly appreciated as our client is losing faith in our ability to resolve the situation and has temporarily disabled the RedGate application.
C:\PROGRA~1\REDGAT~1\SQLBAC~1\SQLBackupC.exe -SQL "BACKUP DATABASE [RES2SQL] TO DISK = 'C:\Hudson\Backups\A1limo-1', DISK = 'C:\Hudson\Backups\A1limo-2', DISK = 'C:\Hudson\Backups\A1limo-3' WITH NAME = 'Database (RES2SQL), 8/3/2005 9:12:14 AM', DESCRIPTION = 'Backup on 8/3/2005 9:12:14 AM Database: RES2SQL Instance: (local) Server: SEQUEL', INIT, VERIFY, COMPRESSION = 1" -E
Microsoft Sequel Server does a scheduled backup (single file) of the same RES2SQL database nightly at 22:00 and never seems to fail. It then backs up the Transaction Log nightly at 22:30.
The command line SQLBackup procedure will work for up to 10 days or so, and then will fail, at the time it tries to start. When this happens, the Server will "freeze" and reboot is required in order to return server to service. When the freeze-up occurs, there is no error in the log indicating what may have transpired at the moment of failure.
This Log shows successful Enterprise Manager backups at 22:00 and 22:30 and successful SQLBackup at 22:15 on November 11 and 12:
2005-11-11 22:02:09.17 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 333906, first LSN: 90486:2346:1, last LSN: 90486:2427:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_db_200511112200.BAK'}).
2005-11-11 22:16:00.46 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 334704, first LSN: 90486:2757:1, last LSN: 90486:2775:1, number of dump devices: 3, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_195436317', 'SQLBACKUP_19543631701', 'SQLBACKUP_19543631702'}).
2005-11-11 22:30:07.85 backup Log backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), first LSN: 90366:1528:1, last LSN: 90486:3197:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_tlog_200511112230.TRN'}).
2005-11-12 22:02:08.38 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 334073, first LSN: 90581:275:1, last LSN: 90581:296:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_db_200511122200.BAK'}).
2005-11-12 22:16:01.39 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 335488, first LSN: 90581:818:1, last LSN: 90581:831:1, number of dump devices: 3, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_196484893', 'SQLBACKUP_19648489301', 'SQLBACKUP_19648489302'}).
2005-11-12 22:30:05.39 backup Log backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), first LSN: 90486:3197:1, last LSN: 90581:1343:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_tlog_200511122230.TRN'}).
This is the last entry in the log from a night when the SQLBackup application locks the server when it tries to start at 22:15:
2005-11-22 22:02:20.57 backup Database backed up: Database: RES2SQL, creation date(time): 2003/03/04(10:36:54), pages dumped: 347179, first LSN: 91896:4965:1, last LSN: 91896:5082:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\RES2SQL\RES2SQL_db_200511222200.BAK'}).
The next log entry shows server being restarted...
Do you see any problem with the command line backup string?
Have you seen any compatibility problems with Server 2000 previously when trying to split the backup files?
Any suggestions would be greatly appreciated as our client is losing faith in our ability to resolve the situation and has temporarily disabled the RedGate application.
Comments
Sorry this has happened.
In addition to the application logs, SQL Backup keeps its' own (text) logs for every backup job in %ALLUSERSPROFILE%\application data\red gate\sql backup\logs. You may want to check there for more information about why the backup has failed.
There are lots of fixes that have been done to SQL Backup 3.2. You may want to upgrade to 3.2 if you are on a lesser version. If you still have problems, please email us at support@red-gate.com and we will send an update for 3.2 (3.2.10) which has many fixes.
Currently using version 3.2.0.1.
I did locate the logs - thanks for the suggestion. Curiously, on the times when the server freezes, no log is started, so no error is reported.
I am not sure that the problem is caused by SQLBackup because the same problem occurs (occasionally) when Enterprise Manager goes to create the multiple backup files. I am trying to eliminate causes of the problem.
As you suggested, I have sent request to Support for the latest update. I will install and test, and think happy thoughts....
I've sent you 3.2.0.10, which should hopefully sort these problems out.