locked
[Solved] Install SQL SERVER 2008 SP2 Failed RRS feed

  • Question

  • Hi,

    i have installed the SP2 of SQL Server 2008.

    When the install was finished, no problem seem appear in the system. But after some minutes, i see that the MSSQLSERVER service is stopped.

    I try to restart it several times without success.

    The error message in the event viewer was :

    "Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it."

    Do you have any solution to resolve my problem?

    Thx for your help.

     

     

    • Edited by rei1982 Wednesday, December 1, 2010 10:57 AM
    Tuesday, November 30, 2010 4:57 PM

Answers

  • Cause of the problem is

    2010-11-30 17:38:00.50 spid7s      Error: 15002, Severity: 16, State: 1.
    2010-11-30 17:38:00.50 spid7s      The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
    2010-11-30 17:38:00.59 spid7s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2010-11-30 17:38:00.59 spid7s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2010-11-30 17:38:00.59 spid7s      Error: 574, Severity: 16, State: 0.
    2010-11-30 17:38:00.59 spid7s      CONFIG statement cannot be used inside a user transaction.


    Open SSMS, Right Click, Properties > Connection.. check if "Implicit transaction" connection property is checked.

    If yes, please uncheck that and try.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by rei1982 Wednesday, December 1, 2010 1:18 PM
    Wednesday, December 1, 2010 9:31 AM
  • hello Balmukund,

     

    by uncheck the case "Implicit transaction" , SP2 is working fine ! The MSSQLServer service is OK now.

    Thanks a lot for your analyse,

     

    Cheers.

    Rei

    • Marked as answer by rei1982 Wednesday, December 1, 2010 1:18 PM
    Wednesday, December 1, 2010 10:57 AM

All replies

  • Hello,

    Does that instance host a user control point or has data collation enabled?

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, November 30, 2010 6:35 PM
  • Hello Rei,

    Please post the complete SQL errorlog. Upgrade Scripts in 2008 are run the first time SQl server service is started after the upgrade . So I am expecting this message to show up when the upgrade scripts are being run.

    I suspect this to be a problem with incorrect folder locations in the registry (for eg defaultdata / defaultlog) for SQL server instance.

    Vist this Blog for detailed troubleshooting.
    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-2008-service-fails-to-start-after-applying-service-pack-1.aspx

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    -------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    -------------------------------------------------------------------------------------

    Wednesday, December 1, 2010 3:41 AM
  • here is the ERRORLOG , thx for your help :

     

    2010-11-30 17:37:57.15 Server      Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
        Sep 16 2010 19:43:16
        Copyright (c) 1988-2008 Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

    2010-11-30 17:37:57.15 Server      (c) 2005 Microsoft Corporation.
    2010-11-30 17:37:57.15 Server      All rights reserved.
    2010-11-30 17:37:57.15 Server      Server process ID is 1812.
    2010-11-30 17:37:57.15 Server      System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
    2010-11-30 17:37:57.15 Server      Authentication mode is MIXED.
    2010-11-30 17:37:57.15 Server      Logging SQL Server messages in file 'd:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2010-11-30 17:37:57.15 Server      This instance of SQL Server last reported using a process ID of 3188 at 11/30/2010 5:37:54 PM (local) 11/30/2010 4:37:54 PM (UTC). This is an informational message only; no user action is required.
    2010-11-30 17:37:57.15 Server      Registry startup parameters:
         -d d:\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
         -e d:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
         -l d:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    2010-11-30 17:37:57.17 Server      SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.
    2010-11-30 17:37:57.17 Server      Detected 4 CPUs. This is an informational message; no user action is required.
    2010-11-30 17:37:57.24 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2010-11-30 17:37:57.29 Server      Node configuration: node 0: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2010-11-30 17:37:57.35 spid7s      Starting up database 'master'.
    2010-11-30 17:37:57.50 spid7s      1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
    2010-11-30 17:37:57.54 spid7s      0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
    2010-11-30 17:37:57.54 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    <{39B75A24-0837-4CEC-AFDF-B960027AE07E}>RsFxNso initialized. InstanceId = 00000005
    2010-11-30 17:37:57.66 spid7s      FILESTREAM: effective level = 1, configured level = 1, file system access share name = 'MSSQLSERVER'.
    2010-11-30 17:37:57.76 spid7s      SQL Trace ID 1 was started by login "sa".
    2010-11-30 17:37:57.78 spid7s      Starting up database 'mssqlsystemresource'.
    2010-11-30 17:37:57.79 spid7s      The resource database build version is 10.00.4000. This is an informational message only. No user action is required.
    2010-11-30 17:37:58.05 spid7s      Server name is 'ASTREE'. This is an informational message only. No user action is required.
    2010-11-30 17:37:58.05 spid10s     Starting up database 'model'.
    2010-11-30 17:37:58.05 spid7s      Informational: No full-text supported languages found.
    2010-11-30 17:37:58.05 spid14s     Starting up database 'ReportServer'.
    2010-11-30 17:37:58.05 spid13s     Starting up database 'msdb'.
    2010-11-30 17:37:58.05 spid16s     Starting up database 'DM_t_pointp'.
    2010-11-30 17:37:58.06 spid15s     Starting up database 'ReportServerTempDB'.
    2010-11-30 17:37:58.07 Server      The certificate [Cert Hash(sha1) "6F49636FEF1FB847CA9D4E622AE11F578BB4A6FC"] was successfully loaded for encryption.
    2010-11-30 17:37:58.07 Server      Server is listening on [ 'any' <ipv6> 1433].
    2010-11-30 17:37:58.07 Server      Server is listening on [ 'any' <ipv4> 1433].
    2010-11-30 17:37:58.07 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2010-11-30 17:37:58.07 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2010-11-30 17:37:58.07 Server      Server is listening on [ ::1 <ipv6> 1434].
    2010-11-30 17:37:58.07 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2010-11-30 17:37:58.08 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2010-11-30 17:37:58.09 spid16s     Error: 17207, Severity: 16, State: 1.
    2010-11-30 17:37:58.09 spid16s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL10.MSSQLSERVER\MSSQL\Data\DM_t_pointp_new.ldf'. Diagnose and correct the operating system error, and retry the operation.
    2010-11-30 17:37:58.10 spid16s     File activation failure. The physical file name "E:\MSSQL10.MSSQLSERVER\MSSQL\Data\DM_t_pointp_new.ldf" may be incorrect.
    2010-11-30 17:37:58.12 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/xxx.net ] for the SQL Server service.
    2010-11-30 17:37:58.12 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/xxx.net:1433 ] for the SQL Server service.
    2010-11-30 17:37:58.13 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2010-11-30 17:37:58.33 spid10s     Clearing tempdb database.
    2010-11-30 17:37:58.36 spid14s     CHECKDB for database 'ReportServer' finished without errors on 2010-11-30 10:25:31.590 (local time). This is an informational message only; no user action is required.
    2010-11-30 17:37:58.86 spid10s     Starting up database 'tempdb'.
    2010-11-30 17:37:58.99 spid13s     The Service Broker protocol transport is disabled or not configured.
    2010-11-30 17:37:58.99 spid13s     The Database Mirroring protocol transport is disabled or not configured.
    2010-11-30 17:37:59.02 spid13s     Service Broker manager has started.
    2010-11-30 17:37:59.07 spid7s      Database 'master' is upgrading script 'msdb_upgrade_discovery.sql' from level 167774691 to level 167776160.
    2010-11-30 17:37:59.07 spid7s      
    2010-11-30 17:37:59.07 spid7s      ----------------------------------------------------------------
    2010-11-30 17:37:59.07 spid7s      msdb_upgrade_discovery starting
    2010-11-30 17:37:59.15 spid7s      MSDB format is: SQL Server 2008
    2010-11-30 17:37:59.26 spid7s      User 'sa' is changing database script level entry 4 to a value of 2.
    2010-11-30 17:37:59.26 spid7s      User 'sa' is changing database script level entry 5 to a value of 2.
    2010-11-30 17:37:59.26 spid7s      User 'sa' is changing database script level entry 6 to a value of 2.
    2010-11-30 17:37:59.26 spid7s      User 'sa' is changing database script level entry 6 to a value of 0.
    2010-11-30 17:37:59.26 spid7s      Running SQL Server 2005 SP2 to SQL Server 2008 upgrade script
    2010-11-30 17:37:59.26 spid7s      ----------------------------------------------------------------
    2010-11-30 17:37:59.26 spid7s      
    2010-11-30 17:37:59.99 spid7s      Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 3.
    2010-11-30 17:37:59.99 spid7s      ----------------------------------------
    2010-11-30 17:37:59.99 spid7s      Starting execution of PREINSTMSDB100.SQL
    2010-11-30 17:37:59.99 spid7s      ----------------------------------------
    2010-11-30 17:38:00.50 spid7s      Error: 15002, Severity: 16, State: 1.
    2010-11-30 17:38:00.50 spid7s      The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
    2010-11-30 17:38:00.59 spid7s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2010-11-30 17:38:00.59 spid7s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2010-11-30 17:38:00.59 spid7s      Error: 574, Severity: 16, State: 0.
    2010-11-30 17:38:00.59 spid7s      CONFIG statement cannot be used inside a user transaction.
    2010-11-30 17:38:00.59 spid7s      Error: 912, Severity: 21, State: 2.
    2010-11-30 17:38:00.59 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 574, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
    2010-11-30 17:38:00.60 spid7s      Error: 3417, Severity: 21, State: 3.
    2010-11-30 17:38:00.60 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
    2010-11-30 17:38:00.60 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
    2010-11-30 17:38:00.65 Server      The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/xxx.net ] for the SQL Server service.
    2010-11-30 17:38:00.66 Server      The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/xxx.net:1433 ] for the SQL Server service.

     

    Wednesday, December 1, 2010 9:02 AM
  • Something went wrong with the upgrade scripts with the installation of your SP. As stated in the 4th last entry of the error log, you will have to restore a backup of master db from the last backup, or rebuild the master db.

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Wednesday, December 1, 2010 9:13 AM
  • Cause of the problem is

    2010-11-30 17:38:00.50 spid7s      Error: 15002, Severity: 16, State: 1.
    2010-11-30 17:38:00.50 spid7s      The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
    2010-11-30 17:38:00.59 spid7s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2010-11-30 17:38:00.59 spid7s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2010-11-30 17:38:00.59 spid7s      Error: 574, Severity: 16, State: 0.
    2010-11-30 17:38:00.59 spid7s      CONFIG statement cannot be used inside a user transaction.


    Open SSMS, Right Click, Properties > Connection.. check if "Implicit transaction" connection property is checked.

    If yes, please uncheck that and try.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by rei1982 Wednesday, December 1, 2010 1:18 PM
    Wednesday, December 1, 2010 9:31 AM
  • I just noticed that SQL is not statying in start mode. So, you need to start SQL in single user mode with trace flag 3608. Once done, connect to SQL using SQLCMD and run sp_configure

    Here are the steps
    1. Open command prompt and run Net Start MSSQLServer /m /c /T3608
    2. From another command prompt SQLCMD -S ASTREE -E
    3. you should see 1>
    4. run below command
               EXEC sys.sp_configure N'user options', N'0'
               GO
               RECONFIGURE WITH OVERRIDE
               GO
    5. Come back to first command prompt and run
    Net Stop MSSQLServer
    6. Now start SQL normally.  

    If you need help, ping me at blakhani (at) koolmail (dot) in
    I am online for next couple of hours (3 hours to be precise)


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, December 1, 2010 9:38 AM
  • hello Balmukund,

     

    by uncheck the case "Implicit transaction" , SP2 is working fine ! The MSSQLServer service is OK now.

    Thanks a lot for your analyse,

     

    Cheers.

    Rei

    • Marked as answer by rei1982 Wednesday, December 1, 2010 1:18 PM
    Wednesday, December 1, 2010 10:57 AM
  • Could you please mark as answer to help others.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, December 1, 2010 11:30 AM
  • Hi

    We have this exact same problem. our error log looks identical to the one posted in this question by rei1982.

    However when we tried to start SQL using single user mode per your suggestion - it wont let our service account login in.

    Gives this error:

    Login failed for user 'CLT_LASH\sqlsvrsvc'. Reason: Server is in single user mode. Only one administrator can connect at this time.

    This is the service account for SQL.

    What do we need to do?

     

    Saturday, May 14, 2011 5:12 AM
  • Make sure no other service (SSRS, SSIS) etc are running. The message means that someone grabbed the connection before you could.
    You can also use -m parameter with the program name as explained in below blog

    http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/inf-hey-my-sql-server-service-is-not-starting-what-do-i-do.aspx


    6.

     

     

     

     

    -m”ClientApp Name”

     

     

     

     

    You can limit the connections to the specified client application. For example, -m"SQLCMD" limits connections to a single connection and that connection must identify itself as the SQLCMD client program. You can use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. Cool option J


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Saturday, May 14, 2011 5:52 AM