|
SQL_Girl
Starting Member
2 Posts |
 Posted - 2004-10-26 : 11:36:08
|
| Urgent help/advice required please! I'm running 5 instances of SQL 2000 sp3 SE on Windows Advanced Server 2003 in a SAN. One of my scheduled backup jobs started failing 2 days ago for below-mentioned error: Database Genome: Verifying Backup...[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'Q:\Genome Backups\Genome\Genome_db_200410260909.BAK'. Device error or device off-line. See the SQL Server error log for more details.[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally.Deleting old text reports... 0 file(s) deleted.End of maintenance plan 'Backup all User DB's' on 2004/10/26 09:09:35SQLMAINT.EXE Process Exit Code: 1 (Failed)[/blue]It is only the larger (6gb) db in this MP that's failing. The smaller ones seems to be successful... same drive its being backup to enough disk space etc. Know MP's not advisable (time constraints at the moment calls for it!)... but tried it manually as well via Query analyser (different drives on the box, different file-name, you name it, I tried it). Not luck, keeps on giving Network connection error. Came across the below-mentioned hotfix, which to the T describes our situation and symptoms... however not recommended. We do have both protocols (named piped as well as TCP/IP) enabled.... network config as well as Client Configuration. Client side, TCP/IP listed first. So, I thought the work-around to be useless. But, I gave it a try under Query analyser.... specifying the tcp:server name.... and wharra... it worked. So, now I have at least got a back up, but 1) still dont know what caused the problem, and 2) still don't know how to force my scheduled jobs to use the required protocol.Any thoughts, advise, help will be highly appreciated! Thanks a million! FIX: General network error when you try to back up or restore a SQL Server database on a computer that is running Windows Server 2003Article ID:827452Last Review:September 3, 2004Revision:5.0On this PageSYMPTOMSRESOLUTIONWORKAROUNDSTATUSSYMPTOMSWhen you try to back up a Microsoft SQL Sever database on a computer that is running Microsoft Windows Server 2003, you may receive an error message that is similar to the following: Processed <Number of Pages> pages for database '<databaseName>', file '<databaseName>' on file 1. 100 percent backed up. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. Processed 1 pages for database '<databaseName>', file '<fileName>' on file 1.However, the backup device file is created successfully and the backup data is valid.A similar problem may also occur when try to restore a SQL Server database on a computer that is running Microsoft Windows Server 2003, and you may receive an error message that is similar to the following: Processed <Number of Pages> pages for database '<databaseName>', file '<fileName>' on file 1. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. Processed 1 pages for database '<databaseName>', file '<fileName>' on file 1. ODBC: Msg 0, Level 16, State 1 Communication link failureGenerally, the backup device file is still created. However, the file may not be valid depending on when the failure occurred. Also, when this problem occurs, an error such as the following is typically logged in the SQL Server error log: 2004-05-26 09:44:16.77 backup BACKUP failed to complete the command backup database testdb to disk='c:\temp\testdb.bak' with init, stats=10Note This problem may occur when the SQL Server connection uses the Named Pipes Net-Library.When the Sqlmaint.exe utility runs the RESTORE VERIFYONLY command, you may notice an error message that is similar to the following in the SQL Server error log: BackupDiskFile::OpenMedia: Backup device 'C:\MSSQL\db_200401261900.BAK' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).Also, you may notice an error message that is similar to the following in the SQL Server maintenance plan output file: Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\MSSQL\db_200401261900.BAK'. Device error or device off-line. See the SQL Server error log for more details. [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally. End of maintenance plan 'ABC Maintenance Plan' on 1/26/2004 7:00:08 PM SQLMAINT.EXE Process Exit Code: 1 (Failed)"RESOLUTIONA supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next Microsoft Data Access Components 2.8 service pack that contains this hotfix.To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question. The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel. Date Time Version Size File name----------------------------------------------09-17-2003 2000.85.1022.42 28,672 bytes dbnmpntw.dllNote Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.Note For a list of all the hotfixes available for MDAC 2.8, click the following article number to view the article in the Microsoft Knowledge Base: 839801 FIX: Hotfixes are available for MDAC 2.8 WORKAROUNDTo work around the problem, use the TCP/IP Net-Library instead of the Named Pipes Net-Library to connect to the SQL Server database, and then back up or restore the database.For the exact steps to set the TCP/IP network library on the client where you are performing the backup or the restore operation, see the "How to configure a client to use TCP/IP (Client Network Utility)" chapter in SQL Server 2000 Books Online.When you connect to an instance of SQL Server by using SQL Query Analyzer, you can force the connection to use the TCP/IP Net-Library. To do this, type the name of the instance of SQL Server with the tcp prefix in the SQL Server text box in the Connect to SQL Server dialog box. This appears as follows: tcp:SQL Server NameSTATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.APPLIES TO•Microsoft Data Access Components 2.8•Microsoft SQL Server 2000 Standard Edition, when used with: Microsoft Windows Server 2003 Standard Edition Microsoft Windows 2000 Enterprise Edition |
|
Sudden network connect failure for scheduled jobs.