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]
>.
>