Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IanBowes
Starting Member
1 Post |
 Posted - 2002-01-15 : 11:04:51
|
| 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 initbackup 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.bakThe 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. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-15 : 11:30:50
|
| A couple of points. Have you tried using the maintenance plan wizard? Why do you backup the log immediately after a full backup? The log is backed up as part of the full backup.Are there any messages in your Event log, or SQL error log?Does the hang still occur when you run the steps individually?Is the whole server hung, or just SQL? Can you ping it?I would start with the SQL errorlog, hopefully there will be a clue in there.-Chad |
 |
|
|
bm1000
Starting Member
37 Posts |
Posted - 2002-01-15 : 11:46:39
|
| What you have looks fine. I agree with the previous post about running each statement individually to see if you can isolate the statement(s) or database that is causing the job to hang. Also, run DBCC checks against the databases to ensure that they are not corrupt.Assuming you can reproduce the hang on a test server,I would apply the latest SQL Service Pack to the test server. If it still hangs, I would call Microsoft Premier Support. |
|
|
|
|
|
|
|
|
SQL2k: Freezing on Backup