Backup database to device error

[Corran007]

Ok odd error here. We have a number of locations that have a sqlserver database in them. i have 2 full backups that run ever other night. a diff that runs at 2pm daily and trans log backups that run every 12 hours. these are all set to run to backup devices i created for each of them. 12 stores with 15 backups per store. For some reason some of them start failing. a total of 4 so far

Here is the error:
Cannot open backup device 'DeviceNameHere'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement is not prepared [SQLSTATE HY007] (Error 0) The backup set is valid. [SQLSTATE 01000] (Message 3262). The step failed.

When i look in the log i find this error.

BackupDiskFile::CreateMedia: Backup device 'D:\mssql7\backup\backupname.bak' failed to create. Operating system error = 32(The process cannot access the file because it is being used by another process.).

I tried deleteing the backup device and remaking. didnt work. If i try to manually delete the file, it will not let me telling me the source or destination file may be in use.

If i reboot the server, they should start working but i really do not want to do that. IF i have to i will, but id prefer not to. but i would like to prevent it from happening in the future if i can. Here is the code that created the backup job

BEGIN TRANSACTION
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Softwise Trans Bak 730')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Softwise Trans Bak 730'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Softwise Trans Bak 730'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Softwise Trans Bak 730', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'BACKUP LOG [Softwise] TO [SoftWiseTransBak730] WITH INIT , NOUNLOAD , NAME = N''Softwise Trans Bak 730'', SKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name=''Softwise''and type!=''F'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=''Softwise'')
RESTORE VERIFYONLY FROM [SoftWiseTransBak730] WITH FILE = @i', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Softwise Trans Bak 730', @enabled = 1, @freq_type = 4, @active_start_date = 20020909, @active_start_time = 73000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 12, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 72959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION

Any help is appreciated.

 

[krishmeena]

How did you get around this Corran007? Its been a
major concern for us now - for almost 4 months this
is happening on and off .... no clue

Any insight/solution that you drew from this problem
will be very very helpful

I will be very grateful for your reply - eagerly awaiting

Thanks

 

[Corran007]

It ended up being a problem with the backup software that comes with nt4. For what ever reason, it was causeing it to lock up when it backed up to tape. The tape backup would not realease the file so it would fail when i tried to backup up to it again. We also thing a problem was when it started backup up to tape when i was till doing the database backup. our network team wrote a script that killed the backup process if it ran excessivly long. It ususally only had problems on the larger database backups. havnt had a problem sense.

 

[krishmeena]

Thanks, Corran007. I will post my resolution when found

 

[gdgetgrl]

I'm having the same problem. I have a job that backs up to a device (with init) every evening. ArcServe doesn't begin its backup for at least a couple more hours after that. But when SQL tries to do a backup, it gets an OS 32 error. Is it possible that ArcServe NEVER realeases the file from the night before?

This has been really frustrating!

 

[SQLBill]

gdgetgrl,

First thing to do is see how long ARCSERVE is taking to complete the backup. What time does it finish and what time does SQL start doing it's backup?

I use Veritas Backup Exec and make sure I have at least 2 hours between the time BE finishes and my next SQL backup starts.

The issue is that SQL Server can't backup to an OPEN file (one that's in use).

OPTION: Have a script created that moves the SQL Server backup file to another file location or renames the file. Then set ArcServe to back up that file. (This would probably be something your OS sysadmin could do for you.)

-SQLBill

 

[gdgetgrl]

The ArcServe backup starts at 2:00am. The Database backup begins at 10:30pm. It should be finished in 22.5 hours, so I think the problem is a file is getting STUCK open, either by SQL Server or by Arcserve.

It's 26GB, too so we don't have a lot of room to be copying it around to another location, although that was a very good suggestion.

I have changed the backup job to not use a backup device. That is the only database we have that backs up to a device instead of to a Disk file. So, we shall see.

 

[SQLBill]

Yes, ArcServe SHOULD be done by then (10:30PM). But is it? Can you check ArcServe's records/logs to see when the job ACTUALLY ends?

Have you been working with your SYSADMIN/NETWORK ADMIN on this (or do you wear all those hats?)? It might be that the non-dba admin is doing a backup of the system/network's files at the time SQL is trying to access the backup file.

-SQLBill

 

[vnad]

I've been having the same problem with Vertias BackupExec. I run my SQL backup at 12AM and my tape backup at 2AM. What needs to be done to release the file so SQL can backup again?

 

[Corran007]

make sure that the tapebackup has finished running the backup. Ours would only occasionally have problems. it usually worked. IF your is regurally having issues, you may want to look into the program why it is having problems with such long backup times. If im reading correctly, its not finishing in 22 hours. Something is very wrong with that.

 

[SonOfEmidec1100]

When I first upgraded to sql 7 I had a similar problem.
I found the problem occurred whenever AUTOSHRINK was triggered at the same time as the DUMP was taken. As AUTOSHRINK wasnt really needed, it seemed a good idea at the time, I just turned it off.

 

[vnad]

The backup to tape runs at 2AM and is done by 4AM. The sql backup is successfull one night, then fails the next, then successfull....

I had a shrink job that ran but was always cancelled by 8AM. I took that out of the job. Still having issues. I do not have veritas scheduled to run any jobs.