Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2003
    Location
    Middle of No where
    Posts
    7

    Question Unanswered: Backup SQL Server Failures

    I get the following errors when I use a maintenance plan to backup all of my databases on one of our servers. Note: When I run them manually they work as long as I put them in a different place on the same drive. The Everone group has full access on the NTFS drive that this is writing to so it can't be a rights issue. Also some of my databases are backed up while others fail. The account that I'm using to back these up is an administrator of the machine, I'm using the same account when I automate them through a maintenance plan that I do for doing them manually. The E:\ drive (where these are trying to be backed up) is Raid 5 with 30 gb free and 30gb used. These backups would only take up about 5gb of space (if they would work).


    Please help!!!


    BACKUP failed to complete the command BACKUP LOG [WebLogs] TO DISK = N'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731. TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

    Internal I/O request 0x2E0CB728: Op: Write, pBuffer: 0x17D20000, Size: 983040, Position: 328286720, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x13914200, OffsetHigh: 0x0, m_buf: 0x17D20000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.TR N

    BackupMedium::ReportIoError: write failure on backup device 'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.T RN'. Operating system error 2(The system cannot find the file specified.).

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try to redefine the Maint.plan and also try using BACKUP LOG statement individually for this database.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2003
    Location
    Middle of No where
    Posts
    7

    Question Response

    Thanks for your reply.

    I've redefined my Maintenance Plan several times, as you could imagine, but to no avail.

    Also, I've ran the BACKUP LOG statement on all of my databases in the place of the maintenance plan and received the same error.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What is the SP[service pack] level on the SQL Server & OS?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Apr 2003
    Location
    Middle of No where
    Posts
    7

    SP Reply

    Windows 2000 Server SP3

    SQL Server 2000 SP3 (Standard Edition not Enterprise)

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Then how about RECOVERY MODEL on these DBs.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Apr 2003
    Location
    Middle of No where
    Posts
    7

    Question Recovery Model Reply

    All of the user databases on this server have the Recovery Model option set to FULL.

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Looks like issues with NTFS permissions, check whether the SQL Services have necessary privileges and also under REGISTRY keys for these accounts.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Apr 2003
    Location
    Middle of No where
    Posts
    7

    Question NTFS Reply

    Since this is an internal machine and not our Internet SQL Server, the SQL Server Services (Main, Agent, etc.) all run under an account that is an administrator of the machine.

    Security rights for both logical drives on the machine have the Everyone group having all rights locally. (The network shares only allow administrators in)

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    As you'd mentioned the disk is 60GB, whereas 30Gb is free and another 30 is used.

    Could you point out exact size of databases and free space available on the disk. It looks like when the Maint.plan is running typically the error refers to Free space on the server.

    For a test try to redesing maint.plan for each database and see the results.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  11. #11
    Join Date
    Apr 2003
    Location
    Middle of No where
    Posts
    7

    Question

    The drive has 21.0 GB used. It has 38.7 GB free.

    There are 16 databases to be backed up. The error happens randomly upon the 3 largest databases.

    Email database = 263.88 mb (data and log)
    ESP database = 316.25 mb (data and log)
    WebLogs database = 5,069.76 gb (data and log)

    (note 5 datafiles in the WebLogs and one log file... low amount of transactions and the database and the log is shrunk periodically throughout the day. We backup and truncate the log after a main import process is executed each day.)

    I've ran the backup by hand using BACKUP DATABASE and it seldom works on these databases. Sometimes it will actually complete, others it doesn't. Also, the WebLogs database never completes and it always seems to fail at 24 percent (stats = 1 on the backup database command).

    The backup log doesn't work at all on these databases whether I run it by hand or through the maintenance plan.

  12. #12
    Join Date
    Apr 2003
    Location
    Middle of No where
    Posts
    7

    Question RESOLUTION!!!!

    Sorry for wasting your time.

    I finally became fed up and went throught the security check points and actually went to the physical machine and looked in the event viewer of windows.

    There I found that one of the Raid disks was going bad. That explains a lot.

    Thanks for all of your help.

    You had a lot of great ideas. If I had the ability to "dial in" to the machine, I would have spotted the problem much earlier.

    I'm betting you agree that a bad disk could cause all of the quirky backup errors?

  13. #13
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Dont' be sorry..... Glad to hear your resolution and efforts, keep it up.
    No second thought for these failures, but anytime if you've them again then follow the listings above.

    Good luck.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •