question

Firdous avatar image
Firdous asked

sql server 2000 database maintenance plan failing

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.
sql-server-2000backupmaintenance-plansfailure
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please remember that this site runs off of voting. For all answers that were helpful you should indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution you can indicate that best answer by clicking on the check box next to it.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlaj 1 avatar image
sqlaj 1 answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Firdous avatar image
Firdous answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.