We have a SQL2k server running on Windows NT Server v4.0 (build 1381) service pack 6. I have a SQL Agent Job to back up several databases to disk. I created a backup device pointing to a disk file, two for each database, one for the database and one for the transaction log.
We have 15 databases, so initially I created a backup step for each database in one job. Setting each step to move onto the next one regardless of success or failure.
Each step is coded similarly:
backup database [database1] to [database1_backup] with init
backup log [database1] to [database1_log_backup] with init
[database1_backup] points to D:\Backup\database1.bak
[database1_log_backup] points to D:\Backup\database1_log.bak
The backup job was set to fire off 1am in the morning. Initially I seemed to have no problem with the backup procedure. All the databases were backed up to disk and the next morning I would archive them to tape. Recently, the whole machine seems to freeze, keyboard locked out - no response. It seems that this freeze is occurring whilst the backup is being run. The databases are not huge, in fact the whole backup takes a few minutes to run. But now we are experiencing fairly frequent hangs if the backup job is run. I have tried splitting the steps into separate jobs and have ran them individually to see if one particular procedure is causing the trouble - but it seems to hang without pattern, when a backup job is run.
I would appreciate any pointers on how to tackle such a problem. Since one or two of our databases are live, I am concerned I haven't got a reliable backup procedure in place. Any advice on running backups would also be greatly appreciated.
We have 15 databases, so initially I created a backup step for each database in one job. Setting each step to move onto the next one regardless of success or failure.
Each step is coded similarly:
backup database [database1] to [database1_backup] with init
backup log [database1] to [database1_log_backup] with init
[database1_backup] points to D:\Backup\database1.bak
[database1_log_backup] points to D:\Backup\database1_log.bak
The backup job was set to fire off 1am in the morning. Initially I seemed to have no problem with the backup procedure. All the databases were backed up to disk and the next morning I would archive them to tape. Recently, the whole machine seems to freeze, keyboard locked out - no response. It seems that this freeze is occurring whilst the backup is being run. The databases are not huge, in fact the whole backup takes a few minutes to run. But now we are experiencing fairly frequent hangs if the backup job is run. I have tried splitting the steps into separate jobs and have ran them individually to see if one particular procedure is causing the trouble - but it seems to hang without pattern, when a backup job is run.
I would appreciate any pointers on how to tackle such a problem. Since one or two of our databases are live, I am concerned I haven't got a reliable backup procedure in place. Any advice on running backups would also be greatly appreciated.