Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts

Wednesday, March 7, 2012

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...b;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...b;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...b;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...b;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...
> following
> in)
> steps
> was
> not
> a
>
>
|||> 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...b;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...b;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
>

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
>

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/defaul...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 step
s
7&8.
A contact refered me to Article
http://support.microsoft.com/defaul...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/defaul...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/defaul...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...
> following
> in)
> steps
> was
> not
> a
>
>|||> 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 followin
g
> 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/defaul...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 st
eps
> 7&8.
> A contact refered me to Article
> http://support.microsoft.com/defaul...kb;EN-US;272318
>
> * DBCC SHRINKFILE(Pxxxxxx_log, 2) - failed saying that the logfile wa
s
> in use.
> * BACKUP LOG Pxxxxxx WITH TRUNCATE_ONLY - ran successfully, but file n
ot
> 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
>

ReportIoError 2 - any ideas?

Problem:
The following SQL is scheduled to run via Enterprise
manager. It has been successfully running for months.
BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
\backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
Friday night the job failed with the log reporting:
BackupMedium::ReportIOError: write failure on backup
device 'D:\mssql7\backup\Proclaim_backup.bak'.
Operating system error 2 (error not found).
To add to the problem, the log file is now full and there
is limitted space in this D: on the server.
Configuration:
Operating System: Windows 2000 Server sp3
Full recovery mode.
SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
Hardward Raid (1) on C: (and 500MB free)
Hardware Raid (5) on D: (and 5Gb free)
Hardware Raid (1) on E: (and 6Gb free)
..mdf file = 4491MB allocated (autogrow)
..log file = 2101MB allocated (at limit)
..bak file = 4110MB
Attempted Action so far:
* Invoking log backup from Enterprise manager:
- to same location -> failed with same error
- to E:\ -> failed with ReportIoError 112
* Retried above after deleting existing file
D:\mssql7\backup\Proclaim_backup.bak' - no change
* Retried above after changing to bulk recovery mode.
- to same location -> failed with same error
- to E: -> successful, but log file still the same size
I've read Knowledge base article 325334; but since the
striping is hardware based on all available drives - see
no means of utilising this information.
Any suggestions would be greatly appreciated.
I don't know where D:\ and E:\ are, but have you tried backing up to a
different machine?
http://www.aspfaq.com/
(Reverse address to reply.)
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:2631501c46229$3f11d150$a401280a@.phx.gbl...
> Problem:
> --
> The following SQL is scheduled to run via Enterprise
> manager. It has been successfully running for months.
> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
> NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
> Friday night the job failed with the log reporting:
> BackupMedium::ReportIOError: write failure on backup
> device 'D:\mssql7\backup\Proclaim_backup.bak'.
> Operating system error 2 (error not found).
>
> To add to the problem, the log file is now full and there
> is limitted space in this D: on the server.
>
> Configuration:
> --
> Operating System: Windows 2000 Server sp3
> Full recovery mode.
> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
> Hardward Raid (1) on C: (and 500MB free)
> Hardware Raid (5) on D: (and 5Gb free)
> Hardware Raid (1) on E: (and 6Gb free)
> .mdf file = 4491MB allocated (autogrow)
> .log file = 2101MB allocated (at limit)
> .bak file = 4110MB
>
> Attempted Action so far:
> --
> * Invoking log backup from Enterprise manager:
> - to same location -> failed with same error
> - to E:\ -> failed with ReportIoError 112
> * Retried above after deleting existing file
> D:\mssql7\backup\Proclaim_backup.bak' - no change
> * Retried above after changing to bulk recovery mode.
> - to same location -> failed with same error
> - to E: -> successful, but log file still the same size
>
> I've read Knowledge base article 325334; but since the
> striping is hardware based on all available drives - see
> no means of utilising this information.
>
> Any suggestions would be greatly appreciated.
|||Sounds you are out of disk space on D drive. Take the
backup of transaction log and shrink the .ldf. Don't you
take frequent transaction log backups?. If you are
concerned only about FULL and DIFFERENTIAL backups , you
can set the Database to SIMPLE recovery, so the log will
be truncated automatically.
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318
Also look for some other pointers which tells, why the log
file grows rapidly.
Finally I think u may need to buy some Hard disks.
HTH
Regards
~Thirumal~
www.thirumal.com

>--Original Message--
>I don't know where D:\ and E:\ are, but have you tried
backing up to a
>different machine?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2631501c46229$3f11d150$a401280a@.phx.gbl...
NOFORMAT[vbcol=seagreen]
there
>
>.
>
|||I've only ever backed up to the local PC. I use the
Enterprise Manager GUI to generate the SQL as a scheduled
job - then modify the schedule as required. The GUI will
not list mapped network drives - so I'd assumed that it
wasn't possible to back up to an external machine.
In any case, Our only other Windows 2000 server has
exactly the same RAID settings.
I have got around the immediate problem by:
* increasing the ldf file by 500MB
* backing up the transaction log to the E: (which has Raid
1).
* using DBCC SHRINKFILE to reduce the ldf file size.
* Deleting the file 'D:\mssql7\backup\Proclaim_backup.bak'
* Rerunning the backups through Enterprise Manager.
I still don't really understand what ReportIoError 2
means. I can only assume that it is related somehow to
the striping because of Q325334.
As a consequence I've no idea whether the problem will
continue and in what timeframe. (I work in an IT
department of 3, and we support a number of disparate
platforms. My colleagues know even less about SQL Server
than I do - so I want to avoid this situation recurring in
my absence).
Thanks for your assistance.
Kim.

>--Original Message--
>I don't know where D:\ and E:\ are, but have you tried
backing up to a
>different machine?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2631501c46229$3f11d150$a401280a@.phx.gbl...
NOFORMAT[vbcol=seagreen]
there
>
>.
>
|||Thanks for the suggestions. It's hard to think of all the
relevant info to post when asking the question.
* The job that should back up the transaction log is the
job that failed. Attempts to back up the transaction log
via other means (as listed) also failed.
* We have limited space on all 3 drives. On both D: and
E: there is the equivalent of the existing database size
free however (figures in original post).
* The transaction log is backed up 6 times during a
working day, and once each weekend.
* Recovery mode is "full" except for when one scheduled
script is run each weekend. It rebuilds indices so before
it is run recovery mode is changed to "bulk logged". The
log backup following this task was the first to fail.

>--Original Message--
>Sounds you are out of disk space on D drive. Take the
>backup of transaction log and shrink the .ldf. Don't you
>take frequent transaction log backups?. If you are
>concerned only about FULL and DIFFERENTIAL backups , you
>can set the Database to SIMPLE recovery, so the log will
>be truncated automatically.
>INF: Shrinking the Transaction Log in SQL Server 2000
with
>DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;EN-
>US;272318
>Also look for some other pointers which tells, why the
log[vbcol=seagreen]
>file grows rapidly.
>Finally I think u may need to buy some Hard disks.
>HTH
>--
>Regards
>~Thirumal~
>www.thirumal.com
>backing up to a
>message
>NOFORMAT
>there
ago)[vbcol=seagreen]
see
>.
>

ReportIoError 2 - any ideas?

Problem:
--
The following SQL is scheduled to run via Enterprise
manager. It has been successfully running for months.
BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
\backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
Friday night the job failed with the log reporting:
BackupMedium::ReportIOError: write failure on backup
device 'D:\mssql7\backup\Proclaim_backup.bak'.
Operating system error 2 (error not found).
To add to the problem, the log file is now full and there
is limitted space in this D: on the server.
Configuration:
--
Operating System: Windows 2000 Server sp3
Full recovery mode.
SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
Hardward Raid (1) on C: (and 500MB free)
Hardware Raid (5) on D: (and 5Gb free)
Hardware Raid (1) on E: (and 6Gb free)
.mdf file = 4491MB allocated (autogrow)
.log file = 2101MB allocated (at limit)
.bak file = 4110MB
Attempted Action so far:
--
* Invoking log backup from Enterprise manager:
- to same location -> failed with same error
- to E:\ -> failed with ReportIoError 112
* Retried above after deleting existing file
D:\mssql7\backup\Proclaim_backup.bak' - no change
* Retried above after changing to bulk recovery mode.
- to same location -> failed with same error
- to E: -> successful, but log file still the same size
I've read Knowledge base article 325334; but since the
striping is hardware based on all available drives - see
no means of utilising this information.
Any suggestions would be greatly appreciated.I don't know where D:\ and E:\ are, but have you tried backing up to a
different machine?
http://www.aspfaq.com/
(Reverse address to reply.)
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:2631501c46229$3f11d150$a401280a@.phx
.gbl...
> Problem:
> --
> The following SQL is scheduled to run via Enterprise
> manager. It has been successfully running for months.
> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
> NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
> Friday night the job failed with the log reporting:
> BackupMedium::ReportIOError: write failure on backup
> device 'D:\mssql7\backup\Proclaim_backup.bak'.
> Operating system error 2 (error not found).
>
> To add to the problem, the log file is now full and there
> is limitted space in this D: on the server.
>
> Configuration:
> --
> Operating System: Windows 2000 Server sp3
> Full recovery mode.
> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
> Hardward Raid (1) on C: (and 500MB free)
> hardware Raid (5) on D: (and 5Gb free)
> hardware Raid (1) on E: (and 6Gb free)
> .mdf file = 4491MB allocated (autogrow)
> .log file = 2101MB allocated (at limit)
> .bak file = 4110MB
>
> Attempted Action so far:
> --
> * Invoking log backup from Enterprise manager:
> - to same location -> failed with same error
> - to E:\ -> failed with ReportIoError 112
> * Retried above after deleting existing file
> D:\mssql7\backup\Proclaim_backup.bak' - no change
> * Retried above after changing to bulk recovery mode.
> - to same location -> failed with same error
> - to E: -> successful, but log file still the same size
>
> I've read Knowledge base article 325334; but since the
> striping is hardware based on all available drives - see
> no means of utilising this information.
>
> Any suggestions would be greatly appreciated.|||Sounds you are out of disk space on D drive. Take the
backup of transaction log and shrink the .ldf. Don't you
take frequent transaction log backups?. If you are
concerned only about FULL and DIFFERENTIAL backups , you
can set the Database to SIMPLE recovery, so the log will
be truncated automatically.
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318
Also look for some other pointers which tells, why the log
file grows rapidly.
Finally I think u may need to buy some Hard disks.
HTH
--
Regards
~Thirumal~
www.thirumal.com

>--Original Message--
>I don't know where D:\ and E:\ are, but have you tried
backing up to a
>different machine?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2631501c46229$3f11d150$a401280a@.phx
.gbl...
NOFORMAT[vbcol=seagreen]
there[vbcol=seagreen]
>
>.
>|||I've only ever backed up to the local PC. I use the
Enterprise Manager GUI to generate the SQL as a scheduled
job - then modify the schedule as required. The GUI will
not list mapped network drives - so I'd assumed that it
wasn't possible to back up to an external machine.
In any case, Our only other Windows 2000 server has
exactly the same RAID settings.
I have got around the immediate problem by:
* increasing the ldf file by 500MB
* backing up the transaction log to the E: (which has Raid
1).
* using DBCC SHRINKFILE to reduce the ldf file size.
* Deleting the file 'D:\mssql7\backup\Proclaim_backup.bak'
* Rerunning the backups through Enterprise Manager.
I still don't really understand what ReportIoError 2
means. I can only assume that it is related somehow to
the striping because of Q325334.
As a consequence I've no idea whether the problem will
continue and in what timeframe. (I work in an IT
department of 3, and we support a number of disparate
platforms. My colleagues know even less about SQL Server
than I do - so I want to avoid this situation recurring in
my absence).
Thanks for your assistance.
Kim.

>--Original Message--
>I don't know where D:\ and E:\ are, but have you tried
backing up to a
>different machine?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2631501c46229$3f11d150$a401280a@.phx
.gbl...
NOFORMAT[vbcol=seagreen]
there[vbcol=seagreen]
>
>.
>|||Thanks for the suggestions. It's hard to think of all the
relevant info to post when asking the question.
* The job that should back up the transaction log is the
job that failed. Attempts to back up the transaction log
via other means (as listed) also failed.
* We have limited space on all 3 drives. On both D: and
E: there is the equivalent of the existing database size
free however (figures in original post).
* The transaction log is backed up 6 times during a
working day, and once each weekend.
* Recovery mode is "full" except for when one scheduled
script is run each weekend. It rebuilds indices so before
it is run recovery mode is changed to "bulk logged". The
log backup following this task was the first to fail.

>--Original Message--
>Sounds you are out of disk space on D drive. Take the
>backup of transaction log and shrink the .ldf. Don't you
>take frequent transaction log backups?. If you are
>concerned only about FULL and DIFFERENTIAL backups , you
>can set the Database to SIMPLE recovery, so the log will
>be truncated automatically.
>INF: Shrinking the Transaction Log in SQL Server 2000
with
>DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;EN-
>US;272318
>Also look for some other pointers which tells, why the
log
>file grows rapidly.
>Finally I think u may need to buy some Hard disks.
>HTH
>--
>Regards
>~Thirumal~
>www.thirumal.com
>
>backing up to a
>message
>NOFORMAT
>there
ago)[vbcol=seagreen]
see[vbcol=seagreen]
>.
>

ReportIoError 2 - any ideas?

Problem:
--
The following SQL is scheduled to run via Enterprise
manager. It has been successfully running for months.
BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
\backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
Friday night the job failed with the log reporting:
BackupMedium::ReportIOError: write failure on backup
device 'D:\mssql7\backup\Proclaim_backup.bak'.
Operating system error 2 (error not found).
To add to the problem, the log file is now full and there
is limitted space in this D: on the server.
Configuration:
--
Operating System: Windows 2000 Server sp3
Full recovery mode.
SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
Hardward Raid (1) on C: (and 500MB free)
Hardware Raid (5) on D: (and 5Gb free)
Hardware Raid (1) on E: (and 6Gb free)
.mdf file = 4491MB allocated (autogrow)
.log file = 2101MB allocated (at limit)
.bak file = 4110MB
Attempted Action so far:
--
* Invoking log backup from Enterprise manager:
- to same location -> failed with same error
- to E:\ -> failed with ReportIoError 112
* Retried above after deleting existing file
D:\mssql7\backup\Proclaim_backup.bak' - no change
* Retried above after changing to bulk recovery mode.
- to same location -> failed with same error
- to E: -> successful, but log file still the same size
I've read Knowledge base article 325334; but since the
striping is hardware based on all available drives - see
no means of utilising this information.
Any suggestions would be greatly appreciated.I don't know where D:\ and E:\ are, but have you tried backing up to a
different machine?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:2631501c46229$3f11d150$a401280a@.phx.gbl...
> Problem:
> --
> The following SQL is scheduled to run via Enterprise
> manager. It has been successfully running for months.
> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
> NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
> Friday night the job failed with the log reporting:
> BackupMedium::ReportIOError: write failure on backup
> device 'D:\mssql7\backup\Proclaim_backup.bak'.
> Operating system error 2 (error not found).
>
> To add to the problem, the log file is now full and there
> is limitted space in this D: on the server.
>
> Configuration:
> --
> Operating System: Windows 2000 Server sp3
> Full recovery mode.
> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
> Hardward Raid (1) on C: (and 500MB free)
> Hardware Raid (5) on D: (and 5Gb free)
> Hardware Raid (1) on E: (and 6Gb free)
> .mdf file = 4491MB allocated (autogrow)
> .log file = 2101MB allocated (at limit)
> .bak file = 4110MB
>
> Attempted Action so far:
> --
> * Invoking log backup from Enterprise manager:
> - to same location -> failed with same error
> - to E:\ -> failed with ReportIoError 112
> * Retried above after deleting existing file
> D:\mssql7\backup\Proclaim_backup.bak' - no change
> * Retried above after changing to bulk recovery mode.
> - to same location -> failed with same error
> - to E: -> successful, but log file still the same size
>
> I've read Knowledge base article 325334; but since the
> striping is hardware based on all available drives - see
> no means of utilising this information.
>
> Any suggestions would be greatly appreciated.|||Sounds you are out of disk space on D drive. Take the
backup of transaction log and shrink the .ldf. Don't you
take frequent transaction log backups?. If you are
concerned only about FULL and DIFFERENTIAL backups , you
can set the Database to SIMPLE recovery, so the log will
be truncated automatically.
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318
Also look for some other pointers which tells, why the log
file grows rapidly.
Finally I think u may need to buy some Hard disks.
HTH
--
Regards
~Thirumal~
www.thirumal.com
>--Original Message--
>I don't know where D:\ and E:\ are, but have you tried
backing up to a
>different machine?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2631501c46229$3f11d150$a401280a@.phx.gbl...
>> Problem:
>> --
>> The following SQL is scheduled to run via Enterprise
>> manager. It has been successfully running for months.
>> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
>> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
>> NAME = N'Proclaim backup', SKIP , STATS = 10,
NOFORMAT
>> Friday night the job failed with the log reporting:
>> BackupMedium::ReportIOError: write failure on backup
>> device 'D:\mssql7\backup\Proclaim_backup.bak'.
>> Operating system error 2 (error not found).
>>
>> To add to the problem, the log file is now full and
there
>> is limitted space in this D: on the server.
>>
>> Configuration:
>> --
>> Operating System: Windows 2000 Server sp3
>> Full recovery mode.
>> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
>> Hardward Raid (1) on C: (and 500MB free)
>> Hardware Raid (5) on D: (and 5Gb free)
>> Hardware Raid (1) on E: (and 6Gb free)
>> .mdf file = 4491MB allocated (autogrow)
>> .log file = 2101MB allocated (at limit)
>> .bak file = 4110MB
>>
>> Attempted Action so far:
>> --
>> * Invoking log backup from Enterprise manager:
>> - to same location -> failed with same error
>> - to E:\ -> failed with ReportIoError 112
>> * Retried above after deleting existing file
>> D:\mssql7\backup\Proclaim_backup.bak' - no change
>> * Retried above after changing to bulk recovery mode.
>> - to same location -> failed with same error
>> - to E: -> successful, but log file still the same size
>>
>> I've read Knowledge base article 325334; but since the
>> striping is hardware based on all available drives - see
>> no means of utilising this information.
>>
>> Any suggestions would be greatly appreciated.
>
>.
>|||I've only ever backed up to the local PC. I use the
Enterprise Manager GUI to generate the SQL as a scheduled
job - then modify the schedule as required. The GUI will
not list mapped network drives - so I'd assumed that it
wasn't possible to back up to an external machine.
In any case, Our only other Windows 2000 server has
exactly the same RAID settings.
I have got around the immediate problem by:
* increasing the ldf file by 500MB
* backing up the transaction log to the E: (which has Raid
1).
* using DBCC SHRINKFILE to reduce the ldf file size.
* Deleting the file 'D:\mssql7\backup\Proclaim_backup.bak'
* Rerunning the backups through Enterprise Manager.
I still don't really understand what ReportIoError 2
means. I can only assume that it is related somehow to
the striping because of Q325334.
As a consequence I've no idea whether the problem will
continue and in what timeframe. (I work in an IT
department of 3, and we support a number of disparate
platforms. My colleagues know even less about SQL Server
than I do - so I want to avoid this situation recurring in
my absence).
Thanks for your assistance.
Kim.
>--Original Message--
>I don't know where D:\ and E:\ are, but have you tried
backing up to a
>different machine?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2631501c46229$3f11d150$a401280a@.phx.gbl...
>> Problem:
>> --
>> The following SQL is scheduled to run via Enterprise
>> manager. It has been successfully running for months.
>> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
>> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
>> NAME = N'Proclaim backup', SKIP , STATS = 10,
NOFORMAT
>> Friday night the job failed with the log reporting:
>> BackupMedium::ReportIOError: write failure on backup
>> device 'D:\mssql7\backup\Proclaim_backup.bak'.
>> Operating system error 2 (error not found).
>>
>> To add to the problem, the log file is now full and
there
>> is limitted space in this D: on the server.
>>
>> Configuration:
>> --
>> Operating System: Windows 2000 Server sp3
>> Full recovery mode.
>> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
>> Hardward Raid (1) on C: (and 500MB free)
>> Hardware Raid (5) on D: (and 5Gb free)
>> Hardware Raid (1) on E: (and 6Gb free)
>> .mdf file = 4491MB allocated (autogrow)
>> .log file = 2101MB allocated (at limit)
>> .bak file = 4110MB
>>
>> Attempted Action so far:
>> --
>> * Invoking log backup from Enterprise manager:
>> - to same location -> failed with same error
>> - to E:\ -> failed with ReportIoError 112
>> * Retried above after deleting existing file
>> D:\mssql7\backup\Proclaim_backup.bak' - no change
>> * Retried above after changing to bulk recovery mode.
>> - to same location -> failed with same error
>> - to E: -> successful, but log file still the same size
>>
>> I've read Knowledge base article 325334; but since the
>> striping is hardware based on all available drives - see
>> no means of utilising this information.
>>
>> Any suggestions would be greatly appreciated.
>
>.
>|||Context
--
The first of the failed log backups was run after a series
of indexes on the database were rebuilt.
Recovery mode is changed to "bulk-logged" before rebuiding
indexes. This is followed by the log backup and then
recovery mode is changed back to "full".
Additional Error Messages in SQL Server Log
---
BackupMedium::ReportIoError: write failure on backup
device 'D:\mssql7\backup\Proclaim_backup.bak'. Operating
system error 2(error not found).
Internal I/O request 0x2E45D1A8: Op: Write, pBuffer:
0x3BE50000, Size: 65536, Position: 4293497344, UMS:
Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFE99200,
OffsetHigh: 0x0, m_buf: 0x3BE50000, m_len: 65536,
m_actualBytes: 0, m_errcode: 2, BackupFile: D:\mssql7
\backup\Proclaim_backup.bak
BACKUP failed to complete the command BACKUP LOG
[Proclaim] TO DISK = N'D:\mssql7
\backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT|||Thanks for the suggestions. It's hard to think of all the
relevant info to post when asking the question.
* The job that should back up the transaction log is the
job that failed. Attempts to back up the transaction log
via other means (as listed) also failed.
* We have limited space on all 3 drives. On both D: and
E: there is the equivalent of the existing database size
free however (figures in original post).
* The transaction log is backed up 6 times during a
working day, and once each weekend.
* Recovery mode is "full" except for when one scheduled
script is run each weekend. It rebuilds indices so before
it is run recovery mode is changed to "bulk logged". The
log backup following this task was the first to fail.
>--Original Message--
>Sounds you are out of disk space on D drive. Take the
>backup of transaction log and shrink the .ldf. Don't you
>take frequent transaction log backups?. If you are
>concerned only about FULL and DIFFERENTIAL backups , you
>can set the Database to SIMPLE recovery, so the log will
>be truncated automatically.
>INF: Shrinking the Transaction Log in SQL Server 2000
with
>DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;EN-
>US;272318
>Also look for some other pointers which tells, why the
log
>file grows rapidly.
>Finally I think u may need to buy some Hard disks.
>HTH
>--
>Regards
>~Thirumal~
>www.thirumal.com
>>--Original Message--
>>I don't know where D:\ and E:\ are, but have you tried
>backing up to a
>>different machine?
>>--
>>http://www.aspfaq.com/
>>(Reverse address to reply.)
>>
>>
>>"Kim" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:2631501c46229$3f11d150$a401280a@.phx.gbl...
>> Problem:
>> --
>> The following SQL is scheduled to run via Enterprise
>> manager. It has been successfully running for months.
>> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
>> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
>> NAME = N'Proclaim backup', SKIP , STATS = 10,
>NOFORMAT
>> Friday night the job failed with the log reporting:
>> BackupMedium::ReportIOError: write failure on backup
>> device 'D:\mssql7\backup\Proclaim_backup.bak'.
>> Operating system error 2 (error not found).
>>
>> To add to the problem, the log file is now full and
>there
>> is limitted space in this D: on the server.
>>
>> Configuration:
>> --
>> Operating System: Windows 2000 Server sp3
>> Full recovery mode.
>> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months
ago)
>> Hardward Raid (1) on C: (and 500MB free)
>> Hardware Raid (5) on D: (and 5Gb free)
>> Hardware Raid (1) on E: (and 6Gb free)
>> .mdf file = 4491MB allocated (autogrow)
>> .log file = 2101MB allocated (at limit)
>> .bak file = 4110MB
>>
>> Attempted Action so far:
>> --
>> * Invoking log backup from Enterprise manager:
>> - to same location -> failed with same error
>> - to E:\ -> failed with ReportIoError 112
>> * Retried above after deleting existing file
>> D:\mssql7\backup\Proclaim_backup.bak' - no change
>> * Retried above after changing to bulk recovery mode.
>> - to same location -> failed with same error
>> - to E: -> successful, but log file still the same size
>>
>> I've read Knowledge base article 325334; but since the
>> striping is hardware based on all available drives -
see
>> no means of utilising this information.
>>
>> Any suggestions would be greatly appreciated.
>>
>>.
>.
>|||Hi Kim,
When ever you perform a recovery model change, it is always suggested to do
a full database backup and
then goahead with Transaction log backup.
This I/O issue can araises due to free space in hard disk, permissions on
the disk.
Thanks
Hari
MCDBA
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:25bce01c46248$42024970$a601280a@.phx.gbl...
> Context
> --
> The first of the failed log backups was run after a series
> of indexes on the database were rebuilt.
> Recovery mode is changed to "bulk-logged" before rebuiding
> indexes. This is followed by the log backup and then
> recovery mode is changed back to "full".
>
> Additional Error Messages in SQL Server Log
> ---
> BackupMedium::ReportIoError: write failure on backup
> device 'D:\mssql7\backup\Proclaim_backup.bak'. Operating
> system error 2(error not found).
> Internal I/O request 0x2E45D1A8: Op: Write, pBuffer:
> 0x3BE50000, Size: 65536, Position: 4293497344, UMS:
> Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFE99200,
> OffsetHigh: 0x0, m_buf: 0x3BE50000, m_len: 65536,
> m_actualBytes: 0, m_errcode: 2, BackupFile: D:\mssql7
> \backup\Proclaim_backup.bak
> BACKUP failed to complete the command BACKUP LOG
> [Proclaim] TO DISK = N'D:\mssql7
> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
> NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT|||Thanks for the suggestion.
I'll add a full backup to the scheduled job before and
after the recovery mode change and see if the issue
persists.