SQL Server Performance Forum – Threads Archive

Sudden network connection errors 4 scheduled jobs

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 BackupsGenomeGenome_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:35 SQLMAINT.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<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />erver 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 : 827452 Last Review : September 3, 2004 Revision : 5.0 On this Page SYMPTOMS RESOLUTION WORKAROUND STATUS SYMPTOMS When 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 failure Generally, 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: emp estdb.bak’ with init, stats=10 Note 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:<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />penMedia: Backup device ‘C:MSSQLdb_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:MSSQLdb_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)" RESOLUTION A 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</a> 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.dll Note 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 WORKAROUND To work around the problem, use the TCP/IP Net-Library inst ead 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<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Server Name STATUS Microsoft 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 Top of Page Keywords:Â kbnetwork kbclientserver kbtsql kbbackup kbserver kbdatabase kbfix KB827452 Top of Page

As the KBA refers the hotfix for set of configuration used at your end, you must deploy it to fix the issue. You cannot force any protocol to certain job, ensure the similar network protocol is used between the sQL servers and check event viewer for any network issues that might be contributing to this issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I am getting the same error as the first poster.
Win 2000 Server with SQL Server 2000 Sp3a. Was working fine all these years and suddenly for the past 5 days, One of my DB restores is failing with this error. But if i try to restore a smaller database, it succeeds and there is an entry in sysdatabases. If it was a MDAC bug, can it show up suddenly like this…
Could this be a space issue and SQL is not able to show it proper.
Thanks
Thanks,
Anand.K
[email protected] "What would you attempt to do if you knew that you could never fail"
-Robert Schuller

It’s a bit long ago. But I do recall us applying the fix, and it did resolve the problem. In our case, it was definitely not a space issue. Hope it helps. Ciao.

Try to apply hotfix and differneitate big databases from the plan to backup the database. Also try to purge history from MSDB database using sp_purge_jobhistory & sp_purgehistory stored procedures. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Had a call with MS yday and the analyst also recommended applying the MDAC fix asap.
We did and it resolved the issue for us. It seems to have made the restore process faster (though that might very well be a coincidence ) Strange that a MDAC bug struck us suddenly after 3 years of normal functioning ! Thanks all Thanks,
Anand.K
[email protected] "What would you attempt to do if you knew that you could never fail"
-Robert Schuller

Are there any recent changes in the infrastructure, such as any hotfix or service pack to operating system too. If so there is difference of MDAC version between sQL & Windows process. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>