ReportIoError when performing backup - what can be done?

Our site is running SQL Server 2000 sp3 on Windows 2000 sp3
When attempting a full database backup to a local drive I get the following
error - and have not found any workable documentation.
BackupMedium::ReportIoError: write failure on backup device
'd:\mssql7\backup\Pxxxxxx_backup.bak'. Operating system error 2(error not
found).
Background:
On 2 previous occasions, and the last weekend, the transaction log on the
above database has become full over the weekend.
The main contributor seems to be a script provided as part of a vendor's
software package which reindexes the database.
As there is little spare space on our server, to avoid this problem the
recovery mode has been changed from 'full' to 'bulk logged' before the
re-index is run, and changed back after the event.
I was advised after previously posting to Microsoft's discussion groups to
perform a full database backup after each change of recovery mode. It is
this step that failed with the full log last night.
PREVIOUSLY this problem has been worked around using the following steps:
1. Increased log file size to 3000MB
2. Tried to backup transaction log (failed)
3. Tried to make dbo access only (failed - said that other users logged in)
4. Checked Current activity (no occurance of extra users)
5. Rebooted server.
6. Made Pxxxxxx database dbo access only
7. Ran database shrink (job successful, but logfile still the same size)
8. Ran backup transaction log (successful this time, but logfile still the
same size)
9. Ran full backup ( why not? successful)
10. Ran command: DBCC SQLPERF (LOGSPACE) which indicates that logfile is
only utilising 5% of allocated space.
11. Ran database shrink (job successful, but logfile still the same size)
12. Set log file autogrow to as small as possible (actual + 1)
13. Ran script based on
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650 (which was
used to shrink a full log on SQL 7)
14. Performed a full database backup
15. Removed dbo access only flag.
Unfortunately, this time, the above process has failed. Any attempts to
backup the transaction log were unsuccessful, and any shrinkfile commands
failed due to the log being full or in use - preventing progress beyond steps
7&8.
A contact refered me to Article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
* DBCC SHRINKFILE(Pxxxxxx_log, 2) - failed saying that the logfile was
in use.
* BACKUP LOG Pxxxxxx WITH TRUNCATE_ONLY - ran successfully, but file not
shrunk
*"Take a full backup of your database after you perform backup log with
truncate_only" ie:
BACKUP DATABASE [Pxxxxxx]
TO DISK = N'D:\mssql7\backup\Pxxxxxx_backup.bak'
WITH INIT ,
NOUNLOAD ,
NAME = N'Pxxxxxx backup',
NOSKIP ,
STATS = 10,
NOFORMAT
- fails with the same error (above) previously encountered when attempting a
log backup
--
Regards,
Kim.Hi!
Just a quick help: you can get description of the OS error messages if you
use NET HELPMSG from the command prompt. For your case,
net helpmsg 2
gives
The system cannot find the file specified.
Hpe this will lead you to a resolution!
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"KD-Mudgee" <KDMudgee@.discussions.microsoft.com> wrote in message
news:3646EB63-2227-4ED6-A0B7-83716826C916@.microsoft.com...
> Our site is running SQL Server 2000 sp3 on Windows 2000 sp3
> When attempting a full database backup to a local drive I get the
following
> error - and have not found any workable documentation.
> BackupMedium::ReportIoError: write failure on backup device
> 'd:\mssql7\backup\Pxxxxxx_backup.bak'. Operating system error 2(error not
> found).
>
> Background:
> On 2 previous occasions, and the last weekend, the transaction log on the
> above database has become full over the weekend.
> The main contributor seems to be a script provided as part of a vendor's
> software package which reindexes the database.
> As there is little spare space on our server, to avoid this problem the
> recovery mode has been changed from 'full' to 'bulk logged' before the
> re-index is run, and changed back after the event.
> I was advised after previously posting to Microsoft's discussion groups to
> perform a full database backup after each change of recovery mode. It is
> this step that failed with the full log last night.
> PREVIOUSLY this problem has been worked around using the following steps:
> 1. Increased log file size to 3000MB
> 2. Tried to backup transaction log (failed)
> 3. Tried to make dbo access only (failed - said that other users logged
in)
> 4. Checked Current activity (no occurance of extra users)
> 5. Rebooted server.
> 6. Made Pxxxxxx database dbo access only
> 7. Ran database shrink (job successful, but logfile still the same size)
> 8. Ran backup transaction log (successful this time, but logfile still the
> same size)
> 9. Ran full backup ( why not? successful)
> 10. Ran command: DBCC SQLPERF (LOGSPACE) which indicates that logfile is
> only utilising 5% of allocated space.
> 11. Ran database shrink (job successful, but logfile still the same size)
> 12. Set log file autogrow to as small as possible (actual + 1)
> 13. Ran script based on
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650 (which was
> used to shrink a full log on SQL 7)
> 14. Performed a full database backup
> 15. Removed dbo access only flag.
>
> Unfortunately, this time, the above process has failed. Any attempts to
> backup the transaction log were unsuccessful, and any shrinkfile commands
> failed due to the log being full or in use - preventing progress beyond
steps
> 7&8.
> A contact refered me to Article
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
>
> * DBCC SHRINKFILE(Pxxxxxx_log, 2) - failed saying that the logfile
was
> in use.
> * BACKUP LOG Pxxxxxx WITH TRUNCATE_ONLY - ran successfully, but file
not
> shrunk
> *"Take a full backup of your database after you perform backup log with
> truncate_only" ie:
> BACKUP DATABASE [Pxxxxxx]
> TO DISK = N'D:\mssql7\backup\Pxxxxxx_backup.bak'
> WITH INIT ,
> NOUNLOAD ,
> NAME = N'Pxxxxxx backup',
> NOSKIP ,
> STATS = 10,
> NOFORMAT
> - fails with the same error (above) previously encountered when attempting
a
> log backup
>
> --
> Regards,
> Kim.|||Dejan,
Thanks for the response. The explanation of the error code didn't leave me
much the wiser...
I increased the log file by another Gb and the backup succeeded.
Given that the data file is only 5.5GB, having a log file with so much space
allocated (over 3Gb) seems overkill, but... at least it works.
I've also now done a successful log file backup and shrunk the log - less
than 1% of the current log file in use.
"Dejan Sarka" wrote:
> Hi!
> Just a quick help: you can get description of the OS error messages if you
> use NET HELPMSG from the command prompt. For your case,
> net helpmsg 2
> gives
> The system cannot find the file specified.
> Hpe this will lead you to a resolution!
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "KD-Mudgee" <KDMudgee@.discussions.microsoft.com> wrote in message
> news:3646EB63-2227-4ED6-A0B7-83716826C916@.microsoft.com...
> > Our site is running SQL Server 2000 sp3 on Windows 2000 sp3
> >
> > When attempting a full database backup to a local drive I get the
> following
> > error - and have not found any workable documentation.
> >
> > BackupMedium::ReportIoError: write failure on backup device
> > 'd:\mssql7\backup\Pxxxxxx_backup.bak'. Operating system error 2(error not
> > found).
> >
> >
> > Background:
> >
> > On 2 previous occasions, and the last weekend, the transaction log on the
> > above database has become full over the weekend.
> >
> > The main contributor seems to be a script provided as part of a vendor's
> > software package which reindexes the database.
> >
> > As there is little spare space on our server, to avoid this problem the
> > recovery mode has been changed from 'full' to 'bulk logged' before the
> > re-index is run, and changed back after the event.
> >
> > I was advised after previously posting to Microsoft's discussion groups to
> > perform a full database backup after each change of recovery mode. It is
> > this step that failed with the full log last night.
> >
> > PREVIOUSLY this problem has been worked around using the following steps:
> > 1. Increased log file size to 3000MB
> > 2. Tried to backup transaction log (failed)
> > 3. Tried to make dbo access only (failed - said that other users logged
> in)
> > 4. Checked Current activity (no occurance of extra users)
> > 5. Rebooted server.
> > 6. Made Pxxxxxx database dbo access only
> >
> > 7. Ran database shrink (job successful, but logfile still the same size)
> > 8. Ran backup transaction log (successful this time, but logfile still the
> > same size)
> > 9. Ran full backup ( why not? successful)
> > 10. Ran command: DBCC SQLPERF (LOGSPACE) which indicates that logfile is
> > only utilising 5% of allocated space.
> >
> > 11. Ran database shrink (job successful, but logfile still the same size)
> > 12. Set log file autogrow to as small as possible (actual + 1)
> > 13. Ran script based on
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;256650 (which was
> > used to shrink a full log on SQL 7)
> > 14. Performed a full database backup
> > 15. Removed dbo access only flag.
> >
> >
> > Unfortunately, this time, the above process has failed. Any attempts to
> > backup the transaction log were unsuccessful, and any shrinkfile commands
> > failed due to the log being full or in use - preventing progress beyond
> steps
> > 7&8.
> >
> > A contact refered me to Article
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> >
> >
> > * DBCC SHRINKFILE(Pxxxxxx_log, 2) - failed saying that the logfile
> was
> > in use.
> > * BACKUP LOG Pxxxxxx WITH TRUNCATE_ONLY - ran successfully, but file
> not
> > shrunk
> > *"Take a full backup of your database after you perform backup log with
> > truncate_only" ie:
> >
> > BACKUP DATABASE [Pxxxxxx]
> > TO DISK = N'D:\mssql7\backup\Pxxxxxx_backup.bak'
> > WITH INIT ,
> > NOUNLOAD ,
> > NAME = N'Pxxxxxx backup',
> > NOSKIP ,
> > STATS = 10,
> > NOFORMAT
> >
> > - fails with the same error (above) previously encountered when attempting
> a
> > log backup
> >
> >
> > --
> > Regards,
> >
> > Kim.
>
>|||> Thanks for the response. The explanation of the error code didn't leave
me
> much the wiser...
> I increased the log file by another Gb and the backup succeeded.
> Given that the data file is only 5.5GB, having a log file with so much
space
> allocated (over 3Gb) seems overkill, but... at least it works.
> I've also now done a successful log file backup and shrunk the log - less
> than 1% of the current log file in use.
Thank you for sharing your experience, although I have to say the same as
you - do not feel much wiser :-) But not because of your post, it is because
I can't understand what's going on.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||KD-Mudgee wrote:
> Our site is running SQL Server 2000 sp3 on Windows 2000 sp3
> When attempting a full database backup to a local drive I get the following
> error - and have not found any workable documentation.
> BackupMedium::ReportIoError: write failure on backup device
> 'd:\mssql7\backup\Pxxxxxx_backup.bak'. Operating system error 2(error not
> found).
>
> Background:
> On 2 previous occasions, and the last weekend, the transaction log on the
> above database has become full over the weekend.
> The main contributor seems to be a script provided as part of a vendor's
> software package which reindexes the database.
> As there is little spare space on our server, to avoid this problem the
> recovery mode has been changed from 'full' to 'bulk logged' before the
> re-index is run, and changed back after the event.
> I was advised after previously posting to Microsoft's discussion groups to
> perform a full database backup after each change of recovery mode. It is
> this step that failed with the full log last night.
> PREVIOUSLY this problem has been worked around using the following steps:
> 1. Increased log file size to 3000MB
> 2. Tried to backup transaction log (failed)
> 3. Tried to make dbo access only (failed - said that other users logged in)
> 4. Checked Current activity (no occurance of extra users)
> 5. Rebooted server.
> 6. Made Pxxxxxx database dbo access only
> 7. Ran database shrink (job successful, but logfile still the same size)
> 8. Ran backup transaction log (successful this time, but logfile still the
> same size)
> 9. Ran full backup ( why not? successful)
> 10. Ran command: DBCC SQLPERF (LOGSPACE) which indicates that logfile is
> only utilising 5% of allocated space.
> 11. Ran database shrink (job successful, but logfile still the same size)
> 12. Set log file autogrow to as small as possible (actual + 1)
> 13. Ran script based on
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650 (which was
> used to shrink a full log on SQL 7)
> 14. Performed a full database backup
> 15. Removed dbo access only flag.
>
did you shrink the transaction log after truncate? how much space do you
have on that backup drive?
> Unfortunately, this time, the above process has failed. Any attempts to
> backup the transaction log were unsuccessful, and any shrinkfile commands
> failed due to the log being full or in use - preventing progress beyond steps
> 7&8.
> A contact refered me to Article
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
>
> * DBCC SHRINKFILE(Pxxxxxx_log, 2) - failed saying that the logfile was
> in use.
> * BACKUP LOG Pxxxxxx WITH TRUNCATE_ONLY - ran successfully, but file not
> shrunk
> *"Take a full backup of your database after you perform backup log with
> truncate_only" ie:
> BACKUP DATABASE [Pxxxxxx]
> TO DISK = N'D:\mssql7\backup\Pxxxxxx_backup.bak'
> WITH INIT ,
> NOUNLOAD ,
> NAME = N'Pxxxxxx backup',
> NOSKIP ,
> STATS = 10,
> NOFORMAT
> - fails with the same error (above) previously encountered when attempting a
> log backup
>