March 20, 2003 at 11:29 am
Hello,
We use a software package (sorry, I can get a fix on the name of it at the moment) to perform nightly backups of the actual Transaction Log file of one of our SQL Server 2000 databases, in addition to the SQL Server backups of the Transaction Log.
I recently moved the Transaction Log of the database to a new location. The network administrator let me know that at 7:14pm every evening since I moved the log, that this other software backup package is not able to backup the log file because "the resource is in use" at that time. The administrator has not indicated that he has the same problem with the Primary data file of the database (which is the only data file for this particular database).
I can think of several reasons why this may be happening, but I wondered if one of the SQL Server utilities might be able to help me pin down the cause(s). Perhaps I should use the Profiler? I have not used any of the SQL Server 2000 utilites before. I need to know what activities are going on at this time with the Log file that would be causing this problem.
Please pass along any suggestions, and thanks for your help.
CSDunn
March 20, 2003 at 12:10 pm
Sounds like you're using offline TSM or ARC Server backups. When the backup software is setup it looks at dir/sub dir and files that you want to backup.
The file is no longer there and the software is complaining.
Deselect the file that was being backed up and then add the new directory / file if it's not being backed up already. If I'm mistaken and this is an online backup deselect the database and then add it back in.
Either way this is not a SQL Server problem and Profiler wouldn't help. Your lucky that you have a network admin that actually reviews backup logs.
On a side note I would try restoring your backups on a scratch server and verify that you can use what you have backed up.
cheers
John Zacharkan
John Zacharkan
March 20, 2003 at 1:10 pm
Thanks for your input. Our network administrator is using ARC. He had already dropped the old dir/file and added the new dir/file for the Log file, and was still getting the message, "The file cannot be backed up because it is being used by other processes". He has not dropped and re-added the Primary data file for the database. He is not backing up the MDF and LDF files at the same time. The LDF file is backed up at about 7:15pm every night, the MDF file at about 7:30pm every night. He is backing up the MDF file at the same time a SQL Server Job attempts to Shrink the database, but is still able to back up the MDF file without problems.
Maybe changing the Shrink Job time for the database would solve the problem(?).
I have tested the restore of our SQL backups on this database before and they work fine, though I have not tested them since I moved the log file. The database and log backup jobs are still working fine.
CSDunn
quote:
Sounds like you're using offline TSM or ARC Server backups. When the backup software is setup it looks at dir/sub dir and files that you want to backup.The file is no longer there and the software is complaining.
Deselect the file that was being backed up and then add the new directory / file if it's not being backed up already. If I'm mistaken and this is an online backup deselect the database and then add it back in.
Either way this is not a SQL Server problem and Profiler wouldn't help. Your lucky that you have a network admin that actually reviews backup logs.
On a side note I would try restoring your backups on a scratch server and verify that you can use what you have backed up.
cheers
John Zacharkan
March 20, 2003 at 1:58 pm
If you are using ARC with the SQL Server hook/add on installed then you shouldn't be backing up the .ldf directly.
Your only other choice to backup up the ldf is by taking the database off line, by either EXEC sp_dboption 'YourDBNameHere', 'offline', 'TRUE'
shutting down SQL Server
or detaching the DB.
If you're creating backups already then why have a seperate process? Just let ARC pickup the backups.
quote:
"The file cannot be backed up because it is being used by other processes".
John Zacharkan
John Zacharkan
March 21, 2003 at 10:18 am
quote:
If you are using ARC with the SQL Server hook/add on installed then you shouldn't be backing up the .ldf directly.
Do you mean backing up the .LDF file directly with ARC or with SQL Server? I can't confirm it at the moment, but I'm very certain that the ARC backup of the file was working before I moved the file. At the time the file was moved, I also upgraded Windows 2000 Advanced Server to SP3, installed the Critical Updates that were available up to 03/12/03, and upgraded the particular instance of SQL Server 2000 Standard to SP3.
quote:
If you're creating backups already then why have a seperate process? Just let ARC pickup the backups.
We have a history of reliability problems with ARC data restores, and not because of the software. Too little staff with far too many responsibilites have created more problems than just ARC data restore issues. Anyway, we have had other ARC restores of other data fail in the past. I do the SQL backups (which have been tested)to cover myself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply