INFO: SQL 2000 MSDE installation will fail if you have number of CPUs on a box which is not in power of 2.
Consider a scenario where you are doing a new installation of SQL Server 2000 MSDE on a computer and you get messages like:
Setup failed to configure the server. Refer to the server error logs and setup error logs for more information
Normally, we enable verbose logging for the MSDE setup and try to figure out the reason of the failure. In case you don’t know how to enable verbose logging while doing MSDE setups please refer to following command that needs to be run from command prompt:
%Root of the MSDE setup files%>setup INSTANCENAME="InstanceName" SAPWD="AStrongSAPwd" /L*v C:/MSDELog.log
In one of the peculiar scenarios on which I worked, I found following messages in the verbose log:
Starting custom action ConfigServer
Executing "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\cnfgsvr.exe -V 1 -M 1 -U sa -I "PMTS" -Q "SQL_Latin1_General_CP1_CI_AS""
MSI (c) (2C:FC) [18:28:07:532]: Font created. Charset: Req=0, Ret=0, Font: Req=MS Shell Dlg, Ret=MS Shell Dlg
Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.
MSI (s) (68!60) [18:28:16:797]: Product: Microsoft SQL Server Desktop Engine (PMTS) -- Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.
Action ended 18:28:16: InstallFinalize. Return value 3.
This error message will tell you that the issue was with the configuration of the server and we need to review SQL Error Logs and Setup logs [verbose logs]. This seems to be the case wherein the services of SQL Server are not getting started.
After setup would fail you will find C:\Program Files\Microsoft SQL Server\MSSQL\LOG directory. Under this directory you will see number of stack dump text files which will be in a readable format. You might see something like the one given below:
00FCF720: 00FCFECC 00A999BA C000001D 00000000 [................]
00FCF730: 00000000 00000000 006E0028 006D0075 [........(.n.u.m.]
00FCF740: 004D0053 00200054 00200026 006E0028 [S.M.T. .&. .(.n.]
00FCF750: 006D0075 004D0053 00200054 0020002D [u.m.S.M.T. .-. .]
00FCF760: 00290031 00200029 003D003D 00300020 [1.).). .=.=. .0.]
00FCF770: 00A90000 00650073 00760072 00720065 [....s.e.r.v.e.r.]
00FCF780: 0061006D 0063002E 00A90000 00000000 [m.a...c.........]
00FCF790: 00000003 178BFBFF 00000000 00000060 [............`...]
00FCF7A0: 00FCF774 FFFFFFFF 00000000 00000001 [t...............]
00FCF7B0: 61636F4C 6E6F6974 7320093A 65767265 [Location:. serve]
00FCF7C0: 2E616D72 38323A63 450A3333 65727078 [rma.c:2833.Expre]
00FCF7D0: 6F697373 20093A6E 6D756E28 20544D53 [ssion:. (numSMT ]
00FCF7E0: 6E282026 4D536D75 202D2054 20292931 [& (numSMT - 1)) ]
00FCF7F0: 30203D3D 4950530A 09093A44 500A3020 [== 0.SPID:.. 0.P]
00FCF800: 65636F72 49207373 20093A44 32333931 [rocess ID:. 1932]
00FCF810: 00000000 00FCF770 7C90E900 00FCF930 [....p......|0...]
00FCF820: 7C90E900 7C910040 FFFFFFFF 7C91003D [...|@..|....=..|]
00FCF830: 7C91E1B2 001C0000 00000000 001C2B40 [...|........@+..]
00FCF840: 00FCF854 7C91E18C 001C2B40 001C2B40 [T......|@+..@+..]
00FCF850: 100046FC 7C9175D6 7C97B178 7C9175B4 [.F...u.|x..|.u.|]
00FCF860: 00C458D8 00000000 10000000 00000014 [.X..............]
If you see the above stack clearly, setup is actually verifying a following condition:
(.n.u.m.S.M.T. .&. .(.n.u.m.S.M.T. .-. .1.).). .=.=. .0. s.e.r.v.e.r.m.a...c t...
Location:. serverma.c:2833.Expression:. (numSMT & (numSMT - 1))== 0.SPID:.. 0.Process ID:. 1932
The problem is in number of CPUs, SQL Server cannot be started if number of CPUs on a box not in power of 2. So, this machine has some strange CPU number, something like 3 CPUs. In my case I confirmed that the machine in fact had Tri Core Processor.
We have somewhat similar issue in SQL 2005 as well:
954835 You cannot install SQL Server 2005 on a server when the number of processors of the server is not a power of 2
Start the installation in debug mode from command prompt by browsing to the location of setup.exe and using the following command:
<Installation media path>setup.exe k=dbg
In case of SP4 installation :
Start the installation of SP4 in debug mode from command prompt by browsing to the location of sqlsetup.exe and using the following command:
<Installation media path>sqlsetup.exe k=dbg
Note: When you run setup in debug mode, the setup will prompt you for user action to proceed on each step. Only when you click “OK” will it continue. This is exactly what we want to do. We are pausing setup at the configuration step, while we add the following trace flags in the registry and then continuing.
Before configuring the server go ahead and added the trace flag –T3601 & -T3603 in the registry by following the below steps:
1) Use trace flags 3601 and 3603 while the setup would be going on. Trace flags 3601 and 3603 can be tried to avoid SMT checks and bypass problematic code which checks the number of processors.
2) Open regedit. Browse to the following location (for named instance, refer method #2 for key location)
x86 servers: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters\
X64 servers: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432node\Microsoft\MSSQLServer\MSSQLServer\Parameters\
3) Then add the following String Values (REG_SZ):
"SQLArg3". Set the value to " -T3601" (without the double-quotes)
"SQLArg4". Set the value to " -T3603” (without the double-quotes)
4) Then, go back to the setup screen and click OK to continue with the sp4 installation.
To resolve the issue, follow these steps:
1 Use trace flags 3601 and 3603 while the setup would be going on. Trace flags 3601 and 3603 can be tried to avoid SMT checks and bypass problematic code which checks the number of processors.
2. Pre-create the following registry hive. Save the content in a .reg file and double-click to import(create) into the registry
i. For Default Instance
Windows Registry Editor Version 5.00
ii. For named instance
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Name_of_the_Instance\MSSQLServer\Parameters]
3. This will create the two trace flags as startup parameter in the registry key along with -d;-l;-e arguments. Since we used TF 3601 and 3603 SQL Server will neglect the number of processors on the box and will go ahead with the installation completely.
For the same issue on SQL Server 2005, please refer to http://support.microsoft.com/kb/954835
PLEASE NOTE THAT THE ABOVE RESOLUTION IS APPLICABLE FOR SQL Server MSDE 2000 and SQL Server 2000 ISSUES ONLY.
For SQL Server 2005 issues you may consult http://blogs.msdn.com/psssql/archive/2008/07/23/sql-server-2005-encounters-exception-during-install-when-system-has-odd-number-of-processors-or-logical-processors-per-core.aspx
T3601 is used to Disable cache prefetch instruction and T3603 is used to disable Simultaneous multithreading processor check . Since SQL Server 2000 does not understand processors when not a power of 2, we NEED TO HAVE these 2 trace flags permanently added as startup parameters to get SQL Service started and running. With the advent of hex core processors, we have fixed this issue in SQL Server 2005 SP2. For more details on this please refer http://support.microsoft.com/kb/954835.
Regarding the performance impact of these you need to step back a bit to computer architecture and understand about pre-fetching. Usage of this disables pre-fetching. But with the advent of modern computers with large L1 & L2 cache, it does not have much significance anymore. Usage of these trace flags will give SQL Server access to all the “visible” processors on the machine. In case you don’t want to have these trace flags, the only other option with SQL 2000 is to disable these additional cores in BIOS so that the sum-total is a power of 2 (^2).
To summarize, we have come across no known issue or defects with the usage of these trace flags by other customers. This is the sole reason; we chose to document these on MSDN for customers out there to use them as a solution for having SQL 2000 running on multi-core machines.
SE, Microsoft SQL Server
TL, Microsoft SQL Server
TL, Microsoft SQL Server