Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Server 2008 R2 Enterprise (SP2) cluster (2 nodes Windows Server 2008 R2 SP1, NetApp Shared storage).

There are 5 instances all running on this cluster in an active passive configuration and we have found that occasionally during the time that the weekly antivirus scan runs, one of the instances starts to drop connections and becomes inaccessible for about an hour (the scan runs for 2 hours), all the other instances are fine.

In the error log, we see the following messages when the server is inaccessible:

TCP connection closed but a child process of SQL Server may be holding a duplicate of the connection's socket. Consider enabling the TcpAbortiveClose SQL Server registry setting and restarting SQL Server. If the problem persists contact Technical Support.

Error: 26073 Severity: 16 State: 1.

They are also accompanied by the following error (although not all the time):

Process 0:0:0 (0x15540) Worker 0x0000000E721041A0 appears to be non-yielding on Scheduler 20. Thread creation time: 13025544955370. Approx Thread CPU Used: kernel 0 ms user 0 ms. Process Utilization 0%. System Idle 91%. Interval: 119402 ms.

During this time there are also some warnings with IO operations taking longer than 15 seconds to complete.

What I'd like to know is:

We have excluded all the locations recommended by Microsoft from scanning, so why is this only happening during the scan, can these issues just be caused by the extra load of the scan, they're pretty powerful boxes so I wouldn't have thought that the extra load would cause a problem, and the time at which this runs, the server is not busy, also if it is caused by the scan why does the problem only stay for 1 hour rather than the full 2?

What is the TcpAbortiveClose registry setting and what are the implications of this setting, I know how to set it from the Microsoft KB article(http://support.microsoft.com/kb/2491214), but I can't find any documentation on this setting anywhere on what it does or whether it is a good idea.

share|improve this question
    
Like @AaronBertrand said ... stop using AntiVirus. Terrible practice. –  swasheck Nov 7 '13 at 15:53
1  
We're an antivirus vendor, we make the AV software and so are made to use it via company policy, not a lot I can do about that I'm afraid. –  steoleary Nov 7 '13 at 15:56
1  
You may want to raise this with the development staff (I assume you're using your company's own product), because it's probably causing clients a headache, too. –  Jon Seigel Nov 7 '13 at 17:49
1  
@steoleary if antivirus is necessary on your sql server (which is not a good practice - as many have pointed out), atleast have an exclusion in the scanning of .mdf, .ldf, .bak, .ndf and sql server binaries. –  Kin Jan 6 at 21:40
    
I would absolutely suggest running antivirus on your SQL Servers; however, definitely exclude the extensions for all your data, transaction log, error log, and backup files (.log, .txt, .mdf, .ndf, .ldf, .bak, .bkp, .trn, .hbc for Hyperbac, whatever else you use, etc.). I would keep scanning executables of all types, including SQL Server binaries - the scan on starting up is insignificant, and once they're running, AV scans of the binaries are largely irrelevant. Keep the AV away from the big, non-executable data files. –  Anti-weakpasswords Apr 8 at 3:40

1 Answer 1

I see that you are still running SQL Server 2008 R2 SP1, so I would suggest that you update your software. If you update to SQL Server 2008 R2 SP2 CU 2 there is a fix for this problem.

You can read about this particular problem at: http://support.microsoft.com/kb/2491214

I would recommend not applying the "workaround" but just getting up-to-date with SP2 CU2 (at least). You should know that SP2 CU8 is available.

share|improve this answer
    
We are using SQL Server 2008 R2 SP2 (10.50.4000) that article suggests that CU2 for SQL 2008 SP2 contains the fix for this issue and CU6 for SQL Server 2008 R2 RTM, so according to the article we shouldn't be seeing this issue with the patch levels we are on. –  steoleary Oct 8 '13 at 13:15
2  
@steoleary I think you are mixed up about R2, SP2 and CU2. 2008 R2 SP2 RTM is 10.50.4000. If you had CU2 installed, your build # would be 10.50.4263. At 4000 you could still be seeing this issue because it wasn't fixed until 4263 was published. Install that (or better). Or just stop using Anti-Virus junk against your SQL Server. –  Aaron Bertrand Oct 8 '13 at 13:32
    
Hi Aaron, I'm not mixed up about the versions, just the article says that it's CU2 for SQL Server 2008 SP2 that fixes it, not CU2 for SQL 2008 R2 SP2. Direct from the article: "Cumulative update package 6 for SQL Server 2008 R2 The fix for this issue was first released in Cumulative Update 6." and then again a bit later for SQL Server 2008 : "Cumulative update package 2 for SQL Server 2008 Service Pack 2 The fix for this issue was first released in Cumulative Update 2 for SQL Server 2008 Service Pack 2." –  steoleary Oct 8 '13 at 13:54
    
@steoleary It looks like I misread the KB. Apologies. I note that the article also mentions that the problem is related to SQL Server Native Client 10.0. –  RLF Oct 8 '13 at 15:58
    
No worries, I have checked the native clients used by all machines connecting to this instance and they are all either the patched version or v11, looks like I'll be raising a call with Microsoft tomorrow morning :-( –  steoleary Oct 8 '13 at 16:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.