Freigeben über

INFO: SQL 2000 MSDE installation will fail if you have number of CPUs on a box which is not in power of 2.

  • Artikel

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..............]

 

EXPLANATION

 

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

https://support.microsoft.com/default.aspx?scid=kb;EN-US;954835

 

 

RESOLUTION

 

METHOD 1:

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.

METHOD 2:

 

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

 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]

"SQLArg3"="-T3601"

"SQLArg4"="-T3603"

 

ii. For named instance

 

Windows Registry Editor Version 5.00

 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Name_of_the_Instance\MSSQLServer\Parameters]

"SQLArg3"="-T3601"

"SQLArg4"="-T3603"

 

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 https://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 https://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

Additional Information

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 https://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.

 

 

Sumit Sarabhai

SE, Microsoft SQL Server
and

Sudarshan Narasimhan

TL, Microsoft SQL Server

Reviewed By

Mukesh Nanda
TL, Microsoft SQL Server

Comments

  • Anonymous
    May 27, 2009
    The comment has been removed

  • Anonymous
    August 19, 2009
    I had this problem installing sql 2000 sp4 on a dual 6 core Opteron machine and this article helped me solve the issue.  Thx. Here are a couple of corrections to the directions to edit the registry entries: The path is actually: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerParameters Insert the trace flags as "string values" not as "keys": New - string value - string value name: SQLArg3 -value data: -T3601

  • Anonymous
    October 02, 2009
    Hi John, Thanks for the comments and the corrections. Please note that the path that I have given in the article applies to Named Instance, and the path you have given is applicable to default instance of SQL Server. However, there is always a scope for improvement :) I have modified my article to include the registry path for both Default and Named Instance. The new article will be published in few days. [In case of Default Instance] HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerParameters [In case of Named Instance]HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstanceNameMSSQLServerParameters  

  • Anonymous
    January 25, 2010
    Thank you ! A hundread times thank you! i have been faced with this problem for 4 or 5 days, looking up information for solving it and relating to the error messages and coming up with nothing. Your post finally solved my problem with MSDE 2000 and SQL 2000 server, so i just had to let you know and hopfully more people will find this information and solve this stupied issue.

  • Anonymous
    February 14, 2010
    Hi Sumit, Thanks for your note, I too had problems trying to install SQL Server 2000 SP4 onto a dual 6 core Opteron machine HP DL385. This note has solved the problem.  I am surpised that there is no official Microsoft KB Article for this? Again, thanks for taking the time to write the note.

  • Anonymous
    February 19, 2010
    @Scott - For SQL 2000, yes there is no official document as such that is why we decided to create this blog. For SQL 2005 we have KB http://support.microsoft.com/kb/954835 which talks about the same issue but with different resolution.

  • Anonymous
    April 23, 2010
    Can you please comment if it is safe/recommended to remove the traceflags after servicepack/SQL has been successfully installed. I see the traceflags in registry after servicepack install has completed. Of if leaving the traceflags on, how will that effect performance on the server....Seems it would be best to have trace flags removed so SQL service can properly use and access all cores for optimization.

  • Anonymous
    April 25, 2010
    Hi Ron, I assume that you are referring to SQL Server 2000/MSDE. By nature of SMT architecture, it cannot be used when the number of CPUs not in power of 2. Therefore, we have to use the above two trace flags to skip the SMT check. Use of the trace flags means that the SQL server will not be able to use some advance CPU features such as CPU ID and prefetching. Below are some reference links regarding these features. CPUID <http://en.wikipedia.org/wiki/CPUID> Prefetch instruction <http://en.wikipedia.org/wiki/Instruction_prefetch> The two trace flags need to be set permanently for SQL server 2000 to startup successfully. Therefore, even if we use the trace flags to disable these features, the performance impact should be very small. SQL Server 2005 SP2 onwards, the issue is fixed, but for any build less than 9.00.3042 including SQL 2K, these trace flags NEED to be in place. HTH. Sudarshan & Balmukund

  • Anonymous
    April 29, 2010
    thank you for this solution. This was very helpfully for us :)

  • Anonymous
    June 15, 2010
    Thanks fro the info, this saved my day!

  • Anonymous
    June 28, 2010
    how many processors SQL Server  will use after you use the T3601, T3603. Looks like it continue to use only one CPU all the time? What need to be done if need to upgrade from 2000 to 2005 or 2008 in place?

  • Anonymous
    July 28, 2010
    Hi Mike,

  1. When you have these 2 trace flags enabled, SQL 2000 will only see processors which are power of 2 and won't be able to use the other processors which are NOT a power of 2. e.g. if you have 24 CPU, SQL will only use 16 (2^4)
  2. If you are upgrading to SQL 2005, then you need to follow the workaround given here support.microsoft.com/default.aspx
  3. If you are upgrading to SQL 2008, nothing needs to be done as SQL 2008 has support for tri/hex core processors and things will work smooth without the need for any trace flags Note: the 2 trace flags are needed ONLY on SQL 2000 and not required for SQL 2005 and above.

  • Anonymous
    July 31, 2010
    I am trying to install MSDE2000A on a XP  32 bit machine running on a Phenom 2 X6 which would fit this issue, but I can't see, to get any solution to work.  I tried the k=dbg and it doesn't prompt for any changes it just goes through the standard install and fails the server config and then rolls back. Any advice will be greatly appreciated. The issue is with Act 2006 which only uses SQL 2000

  • Anonymous
    August 02, 2010
    Hi Hutch77, Did you try using the registry workaround in method#2 of creating the traceflags? Can you also try with MSDE 2000 SP4 (SQL2000.MSDE-KB884525-SP4-x86-ENU.EXE) from www.microsoft.com/.../details.aspx MSDE is slipstreamed, meaning with the SP4 package you can install a new instance. Give this a try and let me know happens. Also, to be sure, check the verbose log to make sure its failing at configuration stage and the LOG folder to be sure you have a stack dump!

  • Anonymous
    May 06, 2011
    Nicely explained Sumit, Sudi and balmukund ..it worked like a charm ..

  • Anonymous
    August 28, 2011
    thank you very very much you saved my day !!!

  • Anonymous
    January 04, 2012
    I ran into this problem after a P2V, the sql service would not start. This got it going again. Thanks!!