We are using sql server 2000 which contains databases ranging from 10 GB to 100 GB. The sql server maintenance plan that has been running successfully since several years has suddenly started failing/become very slow. The databases which are less than 20 GB in size, which earlier used to take 10 minutes are now taking 2-3 hrs. Also, databases above 30 GB are reporting the following error. 2013-09-27 15:48:15.04 spid372 BackupMedium::ReportIoError: write failure on backup device 'D:\backup\xxx\xxxx_db_201309271445.BAK'. Operating system error 2(The system cannot find the file specified.). 2013-09-27 15:48:15.06 spid372 Internal I/O request 0x78908C30: Op: Write, pBuffer: 0x06C80000, Size: 983040, Position: 12578002432, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xEDB51600, OffsetHigh: 0x2, m_buf: 0x06C80000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: D:\backup\xxx\xxxx_db_201309271445.BAK 2013-09-27 15:48:15.06 backup BACKUP failed to complete the command BACKUP DATABASE [icfaimail] TO DISK = N'D:\backup\xxxx\xxxx_db_201309271445.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT When the maintenance plan starts, the backup file is created in the designated backup folder showing 0 kb size. The file is created and it exists. However, about 30-40 minutes into the backup, the error above is displayed and the backup fails. We have already run check disk and it has reported back zero errors. Please advise.
Things I would be thinking about: 1. Fragmentation - how heavily fragmented is the server? and the databases? 2. what else is running against the HDD? Antivirus software? Something else? 3. what's the output from `fltmc instances`? 4. Is the D: drive compressed? Are you running out of space on it? 5. Can you run the backup to another disk? If so, does that have the same problem?
I am not a fan of maintenance plans for this particular reason. They work and don't work. T-SQL code is much easier to troubleshoot (in my opinion). That being said... Can you manually run a backup and it succeed? I don't mean manually starting the job. I mean going through the GUI and manually running a backup. Does it fail as well? Using the maintenance plan, does it consistantly fail at 30-40 minutes?
Further to the original query, I have done the following: Checked the index fragmentation and re-organized all the indexes wherever necessary. However, the backup still continued to fail. Uninstalled the anti-virus. No effect. As suggested by Thomas Rushton, ran the backup to another disk. The backup was Successful!!! Also, it got executed over network at good speed. I have requested our system administrator to check for any hardware issues again as the check disk did not throw up any errors. Will post any further developments. Any further inputs would be appreciated.