September 13, 2010 at 4:51 pm
Switching to one of the new 6-core processors on a SQL Server 2000 installation will prevent the SQL Server service from starting.
There appears to be a bug in the code that counts the processor cores (I think) and the result will be a non-startable DB service.
You'll be able to see the error in the Application log:
17066 :
SQL Server Assertion File: <serverma.c>, line 2833
Failed Assertion = '(numSMT & (numSMT - 1)) == 0'.
We lost a server installation, and I spent several long days isolating the issue. We felt that we'd replace the failing motherboard, CPU and memory with something a little more like the current state, and couldn't get the SP4 install to complete.
The existing disk image would not start, the image backup of the server would not start, and a fresh installation would crash during the SP4 update.
I can confirm the issue with Win XP x64 & Win Server 2003, and at all memory levels from 2GB to 16GB. Finally, replacing the processor with an X4 model allowed the DB service to restart.
(Thanks to all here, whose expertise has been so valuable. This is my chance to contribute something I hope will help others.)
September 13, 2010 at 4:57 pm
Awesome thanks for the post... Why are you not going with a newer version of MSSQL? I am sure this problem will go away and... =] here is your chance to do a full on upgrade/migration to a better version of MSSQL as a whole...
Also you can use the hardware as good reasoning to upgrade now.. heheh thanks again..
DHeath
September 13, 2010 at 5:10 pm
Yeah - we use 2008 R2 for all our production, but we have a ton of complex DTS packages on this still mission-critical box. Migrating everything to SSIS is beyond our bandwidth at the moment.
April 26, 2011 at 7:04 pm
The problem is that SQL Server 2000 executes the CPUID instruction instead of using the Win32 API. So limiting the number of processors at boot time (BCDEDIT /set {current} numproc 4) won't work.
To fix:
1. Temporarily boot with 4 processors.
a. BCDEDIT /set {current} numproc 4
b. Reboot.
2. Patch the executable after the CPUID instruction to cap the test at 4 processors:
a. cd /d "C:\Program Files (x86)\Microsoft SQL Server\MSSQL\Binn"
b. copy sqlservr.exe sqlservr.exe.ORIG
c. XXD.EXE sqlservr.exe sqlservr-xxd.txt
d. Apply the patch. The patch shown below is for SQL Server 2000 version 8.00.2282 (file version 2000.080.2282.00):
*** sqlservr-xxd.txt 2011-04-26 19:25:58.949163300 -0500
--- sqlservr-xxd-patch.txt 2011-04-26 19:26:08.714512900 -0500
***************
*** 103539,103546 ****
0194720: 0c03 b400 50ff 1510 03b4 008d 4dd8 894d ....P.......M..M
0194730: f4c7 45f0 0100 0000 8b45 f08b 75f4 0fa2 ..E......E..u...
0194740: 8906 895e 0489 4e08 8956 0c8b 45e4 a900 ...^..N..V..E...
! 0194750: 0000 108b d874 708b 55dc c1ea 100f b6f2 .....tp.U.......
! 0194760: 85f6 0f84 207b 0800 8d46 ff85 c60f 8534 .... {...F.....4
0194770: 7b08 008b 45f8 85c0 7448 3bc6 7549 f7c3 {...E...tH;.uI..
0194780: 0000 0002 0f84 3d7b 0800 a170 dec4 0047 ......={...p...G
0194790: 3bf8 0f82 6bff ffff 5e5b 8b4d ec51 ff15 ;...k...^[.M.Q..
--- 103539,103546 ----
0194720: 0c03 b400 50ff 1510 03b4 008d 4dd8 894d ....P.......M..M
0194730: f4c7 45f0 0100 0000 8b45 f08b 75f4 0fa2 ..E......E..u...
0194740: 8906 895e 0489 4e08 8956 0c8b 45e4 a900 ...^..N..V..E...
! 0194750: 0000 108b d874 708b 55dc c1ea 1080 fa04 .....tp.U.......
! 0194760: 7602 b204 0fb6 f290 8d46 ff85 c60f 8534 v........F.....4
0194770: 7b08 008b 45f8 85c0 7448 3bc6 7549 f7c3 {...E...tH;.uI..
0194780: 0000 0002 0f84 3d7b 0800 a170 dec4 0047 ......={...p...G
0194790: 3bf8 0f82 6bff ffff 5e5b 8b4d ec51 ff15 ;...k...^[.M.Q..
e. Convert the patched dump-file back to binary:
XXD.EXE -r sqlservr-xxd.txt sqlservr.exe
If you have Ida Pro or OllyDbg, you can apply the following patch instead:
.text:594738 8B 45 F0 mov eax, [ebp+var_10] ; Set EAX=1
.text:59473B 8B 75 F4 mov esi, [ebp+var_C]
.text:59473E 0F A2 cpuid ; Execute CPUID EAX=1
.text:594740 89 06 mov [esi], eax ; var_28
.text:594742 89 5E 04 mov [esi+4], ebx ; Bits 16..23 = numproc, var_24
.text:594745 89 4E 08 mov [esi+8], ecx ; var_20
.text:594748 89 56 0C mov [esi+0Ch], edx ; var_1C
.text:59474B 8B 45 E4 mov eax, [ebp+var_1C] ; CPUID EDX
.text:59474E A9 00 00 00 10 test eax, 10000000h ; (HyperThreading bit)
.text:594753 8B D8 mov ebx, eax
.text:594755 74 70 jz short loc_5947C7
.text:594757 8B 55 DC mov edx, [ebp+var_24] ; EBX result of CPUID
.text:59475A C1 EA 10 shr edx, 10h
#ifdef OLD_INSTRUCTIONS
.text:59475D 0F B6 F2 movzx esi, dl
.text:594760 85 F6 test esi, esi
.text:594762 0F 84 20 7B 08 00 jz loc_61C288
#else // NEW_INSTRUCTIONS
.text:59475D 80 FA 04 cmp dl, 4 ; Move than 4 processors?
.text:594760 76 02 jbe L1
.text:594762 B2 04 mov dl, 4 ; Cap at 4 processors
.text:594764 L1:
.text:594764 0F B6 F2 movzx esi, dl
.text:594767 90 nop
#endif // NEW_INSTRUCTIONS
.text:594768 loc_594768: ; CODE XREF: CheckHardwareProperties(void)+87C20
.text:594768 8D 46 FF lea eax, [esi-1]
.text:59476B 85 C6 test esi, eax
.text:59476D 0F 85 34 7B 08 00 jnz loc_61C2A7 ; ASSERT FAIL
3. Set the executable's CPU affinity mask to 0xf (00001111 binary) in the executable's LOADCONFIG section, using IMAGECFG.EXE:
IMAGECFG.EXE -a 0xF sqlservr.exe
IMAGECFG will also recompute the header checksum.
4. Test your handiwork. Start SQL Server from the console.
.\sqlservr.exe
If you patched it correctly you should see the normal startup messages.
5. While SQL Server is running, set the maximum processors to 4.
a. Click on SQL Server Enterprise Manager -> SQL Server Group -> Server.
b. Right-click and select Properties.
c. Click on the tab Processor.
d. Under 'Parallelism' select 'Use [4] processor(s)', click OK.
e. Go back to the command console and press ^C to halt SQL Server.
6. Restore the BCD to boot with all 6 processors.
BCDEDIT /deletevalue {current} numproc
Reboot.
7. Start the MSSQLSERVER service and be happy :-).
May 12, 2011 at 3:19 pm
Alank, thanks for the post! Awesome! Could you clarify what test is this performed by SQL Server 2000, or more specific what is it used for? I applied your patch to a cloned machine that we moved from 4 to 6 core processors. SQL Server 2000 wasn't coming up, but the patched version started ok and acknowledged the 24 processors on the box. I didn't mess with BCEDIT or processor affinity using IMGCFG.EXE. Apparently, everything went fine but I'm not sure if the patch would bring undesired effects in production.
Additionally, I patched the SP4 version of the binary (based on your patch I could find the code). Since the original build of SQL Server 2000 installation doesn't trigger the problem, patching the SP4 executable I could manage to do a fresh install in the 6 core server and bring it up to the last build.
So, do you think it's ok to go on production with this patched version of 2282?
Thanks agains!
ps, below I provide the diff file for sp4. Just patch sqlservr.exe (using xxd procedure above and replace the file bundled in sp4 install):
*** sqlservr-sp4-ori.txt 2011-05-12 00:00:00.000000000 -0200
--- sqlservr-sp4-patched.txt 2011-05-12 00:00:01.000000000 -0200
***************
*** 111227,111234 ****
01b27a0: b300 50ff 1510 93b3 008d 4dd8 894d f4c7 ..P.......M..M..
01b27b0: 45f0 0100 0000 8b45 f08b 75f4 0fa2 8906 E......E..u.....
01b27c0: 895e 0489 4e08 8956 0c8b 45e4 a900 0000 .^..N..V..E.....
! 01b27d0: 108b d874 708b 55dc c1ea 100f b6f2 85f6 ...tp.U.........
! 01b27e0: 0f84 529b 0600 8d46 ff85 c60f 8566 9b06 ..R....F.....f..
01b27f0: 008b 45f8 85c0 7448 3bc6 7549 f7c3 0000 ..E...tH;.uI....
01b2800: 0002 0f84 6f9b 0600 a190 93c4 0047 3bf8 ....o........G;.
01b2810: 0f82 6bff ffff 5e5b 8b4d ec51 ff15 0c93 ..k...^[.M.Q....
--- 111227,111234 ----
01b27a0: b300 50ff 1510 93b3 008d 4dd8 894d f4c7 ..P.......M..M..
01b27b0: 45f0 0100 0000 8b45 f08b 75f4 0fa2 8906 E......E..u.....
01b27c0: 895e 0489 4e08 8956 0c8b 45e4 a900 0000 .^..N..V..E.....
! 01b27d0: 108b d874 708b 55dc c1ea 1080 fa04 7602 ...tp.U.........
! 01b27e0: b204 0fb6 f290 8d46 ff85 c60f 8566 9b06 ..R....F.....f..
01b27f0: 008b 45f8 85c0 7448 3bc6 7549 f7c3 0000 ..E...tH;.uI....
01b2800: 0002 0f84 6f9b 0600 a190 93c4 0047 3bf8 ....o........G;.
01b2810: 0f82 6bff ffff 5e5b 8b4d ec51 ff15 0c93 ..k...^[.M.Q....
June 15, 2011 at 11:36 am
Just a quick, follow up:
1 - based on what I've been told, patching the binary invalidates the license.
2 - there a different workaround activating trace flags 3601 and 3603. Details on:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply