Discussion :

[johndoe08]

Host : Win 2008 R2 Enterprise x64
SQL Server 2008 Standard Edition x64

Bonjour,


après avoir passé le SP2 de SQL Server 2008, je ne peux plus démarrer les services SQL Server et SQL Server Agent.

Voici l'erreur remonté :

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

Avez vous déjà rencontré ce problème lors de cette mise à jour ?

Merci pour votre aide.

[kagemaru]

bonjour

est-ce qu'il est possible d'avoir l'ERRORLOG complet ?

Merci

[johndoe08]

Bonjour,

voici le dernier message d'erreur présent dans l'event viewer :

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.

Sur le lien suivant, il parle d'un bug connu de Microsoft et de désactiver le FILESTREAM avant l'installation du SP2 :

http://connect.microsoft.com/SQLServ...llation-failed


Voici également le contenu du fichier ERRORLOG (avec le FILESTREAM de désactivé) :

2010-10-20 14:06:40.28 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-10-20 14:06:40.28 Server (c) 2005 Microsoft Corporation.
2010-10-20 14:06:40.31 Server All rights reserved.
2010-10-20 14:06:40.31 Server Server process ID is 3640.
2010-10-20 14:06:40.31 Server System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2010-10-20 14:06:40.31 Server Authentication mode is WINDOWS-ONLY.
2010-10-20 14:06:40.31 Server Logging SQL Server messages in file 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-10-20 14:06:40.31 Server This instance of SQL Server last reported using a process ID of 3916 at 10/20/2010 2:05:07 PM (local) 10/20/2010 12:05:07 PM (UTC). This is an informational message only; no user action is required.
2010-10-20 14:06:40.31 Server Registry startup parameters:
-d E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2010-10-20 14:06:40.33 Server SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.
2010-10-20 14:06:40.33 Server Detected 4 CPUs. This is an informational message; no user action is required.
2010-10-20 14:06:40.40 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-10-20 14:06:40.44 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-10-20 14:06:40.50 spid6s Starting up database 'master'.
2010-10-20 14:06:40.67 spid6s 1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2010-10-20 14:06:40.71 spid6s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2010-10-20 14:06:40.71 spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-10-20 14:06:40.95 spid6s CHECKDB for database 'master' finished without errors on 2010-10-19 23:00:33.193 (local time). This is an informational message only; no user action is required.
2010-10-20 14:06:41.01 spid6s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-10-20 14:06:41.11 spid6s SQL Trace ID 1 was started by login "sa".
2010-10-20 14:06:41.13 spid6s Starting up database 'mssqlsystemresource'.
2010-10-20 14:06:41.14 spid6s The resource database build version is 10.00.4000. This is an informational message only. No user action is required.
2010-10-20 14:06:41.53 spid10s Starting up database 'model'.
2010-10-20 14:06:41.54 spid6s Server name is 'QSQL2008TEST'. This is an informational message only. No user action is required.
2010-10-20 14:06:41.55 Server The certificate [Cert Hash(sha1) "A6A9237FA287DE463A1176C3EAC63C8F3F488D5A"] was successfully loaded for encryption.
2010-10-20 14:06:41.55 Server Server is listening on [ 'any' <ipv6> 1433].
2010-10-20 14:06:41.55 Server Server is listening on [ 'any' <ipv4> 1433].
2010-10-20 14:06:41.55 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2010-10-20 14:06:41.55 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2010-10-20 14:06:41.56 Server Server is listening on [ ::1 <ipv6> 1434].
2010-10-20 14:06:41.56 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2010-10-20 14:06:41.56 Server Dedicated admin connection support was established for listening locally on port 1434.
2010-10-20 14:06:41.63 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/qsql2008test.quptest.net ] for the SQL Server service.
2010-10-20 14:06:41.63 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/qsql2008test.quptest.net:1433 ] for the SQL Server service.
2010-10-20 14:06:41.63 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-10-20 14:06:41.70 spid11s A new instance of the full-text filter daemon host process has been successfully started.
2010-10-20 14:06:41.82 spid19s FILESTREAM feature is disabled.
2010-10-20 14:06:41.82 spid19s Error: 5105, Severity: 16, State: 14.
2010-10-20 14:06:41.82 spid19s A file activation error occurred. The physical file name 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\xxx_Stream' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2010-10-20 14:06:41.82 spid19s Starting up database 'xxx'.
2010-10-20 14:06:41.82 spid25s Starting up database 'xxx'.
2010-10-20 14:06:41.82 spid24s Starting up database 'xxx'.

2010-10-20 14:06:42.75 spid10s CHECKDB for database 'model' finished without errors on 2010-10-03 23:00:04.250 (local time). This is an informational message only; no user action is required.
2010-10-20 14:06:42.75 spid10s Clearing tempdb database.
2010-10-20 14:06:42.91 spid11s CHECKDB for database 'msdb' finished without errors on 2010-10-03 23:00:04.770 (local time). This is an informational message only; no user action is required.
2010-10-20 14:06:43.23 spid10s Starting up database 'tempdb'.
2010-10-20 14:06:43.30 spid11s The Service Broker protocol transport is disabled or not configured.
2010-10-20 14:06:43.30 spid11s The Database Mirroring protocol transport is disabled or not configured.
2010-10-20 14:06:43.37 spid11s Service Broker manager has started.
2010-10-20 14:06:43.40 spid6s Database 'master' is upgrading script 'msdb_upgrade_discovery.sql' from level 167774691 to level 167776160.
2010-10-20 14:06:43.40 spid6s
2010-10-20 14:06:43.40 spid6s ----------------------------------------------------------------
2010-10-20 14:06:43.40 spid6s msdb_upgrade_discovery starting
2010-10-20 14:06:43.56 spid6s MSDB format is: SQL Server 2008
2010-10-20 14:06:43.67 spid6s User 'sa' is changing database script level entry 4 to a value of 2.
2010-10-20 14:06:43.67 spid6s User 'sa' is changing database script level entry 5 to a value of 2.
2010-10-20 14:06:43.67 spid6s User 'sa' is changing database script level entry 6 to a value of 2.
2010-10-20 14:06:43.67 spid6s User 'sa' is changing database script level entry 6 to a value of 0.
2010-10-20 14:06:43.67 spid6s Running SQL Server 2005 SP2 to SQL Server 2008 upgrade script
2010-10-20 14:06:43.67 spid6s ----------------------------------------------------------------
2010-10-20 14:06:43.67 spid6s
2010-10-20 14:06:44.40 spid6s Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 3.
2010-10-20 14:06:44.40 spid6s ----------------------------------------
2010-10-20 14:06:44.40 spid6s Starting execution of PREINSTMSDB100.SQL
2010-10-20 14:06:44.40 spid6s ----------------------------------------
2010-10-20 14:06:44.55 Logon Error: 18401, Severity: 14, State: 1.
2010-10-20 14:06:44.55 Logon Login failed for user 'DOMAIN\userxxx'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: <local machine>]

2010-10-20 14:06:44.81 spid6s Error: 15002, Severity: 16, State: 1.
2010-10-20 14:06:44.81 spid6s The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
2010-10-20 14:06:44.94 spid6s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-10-20 14:06:44.94 spid6s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-10-20 14:06:44.94 spid6s Error: 574, Severity: 16, State: 0.
2010-10-20 14:06:44.94 spid6s CONFIG statement cannot be used inside a user transaction.
2010-10-20 14:06:44.94 spid6s Error: 912, Severity: 21, State: 2.
2010-10-20 14:06:44.94 spid6s 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-10-20 14:06:44.95 spid6s Error: 3417, Severity: 21, State: 3.
2010-10-20 14:06:44.95 spid6s 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-10-20 14:06:44.95 spid6s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2010-10-20 14:06:45.02 Server The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/qsql2008test.test.net ] for the SQL Server service.
2010-10-20 14:06:45.02 Server The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/qsql2008test.test.net:1433 ] for the SQL Server service.

[johndoe08]

Pour information, j'ai tenté d'installer le SP2 avec et sans le FILESTREAM d'activé sans succès.

Merci pour votre aide

[dbaffaleuf]

Est-ce que tu peux démarrer en sqlservr -c -m -T3607 ? Peux-tu nous montrer la sortie de la commande ?

Sinon, tu dois avoir les fichiers templates pour les bases système sous ~Binn\Templates, pour récupérer les fichiers initiaux de master.

[johndoe08]

Voici le résultat de la commande sqlservr -c -m -T3607

2010-10-20 16:42:55.07 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-10-20 16:42:55.07 Server (c) 2005 Microsoft Corporation.

2010-10-20 16:42:55.07 Server All rights reserved.

2010-10-20 16:42:55.07 Server Server process ID is 2288.

2010-10-20 16:42:55.07 Server System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.

2010-10-20 16:42:55.07 Server Authentication mode is WINDOWS-ONLY.

2010-10-20 16:42:55.07 Server Logging SQL Server messages in file 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.

2010-10-20 16:42:55.07 Server This instance of SQL Server last reported using a process ID of 3640 at 10/20/2010 2:06:44 PM (local) 10/20/2010 12:06:44 PM (UTC). This is an informational message only; no user action is required.

2010-10-20 16:42:55.07 Server Registry startup parameters:

-d E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf

-e E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG

-l E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

2010-10-20 16:42:55.07 Server Command Line Startup Parameters:

-c

-m

-T 3607

2010-10-20 16:42:55.08 Server SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.

2010-10-20 16:42:55.08 Server Detected 4 CPUs. This is an informational message; no user action is required.

2010-10-20 16:42:55.17 Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled.

2010-10-20 16:42:55.19 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-10-20 16:42:55.35 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-10-20 16:42:55.37 Server Database Mirroring Transport is disabled in the endpoint configuration.

2010-10-20 16:42:55.38 spid6s Warning ******************

2010-10-20 16:42:55.39 spid6s SQL Server started in single-user mode. This an informational message only. No user action is required.

2010-10-20 16:42:55.39 spid6s Starting without recovery. This is an informational message only. No user action is required.

2010-10-20 16:42:55.40 spid6s Starting up database 'master'.

2010-10-20 16:42:55.88 Server The certificate [Cert Hash(sha1) "A6A9237FA287DE463A1176C3EAC63C8F3F488D5A"] was successfully loaded for encryption.

2010-10-20 16:42:55.88 Server Server is listening on [ 'any' <ipv6> 1433].

2010-10-20 16:42:55.88 Server Server is listening on [ 'any' <ipv4> 1433].

2010-10-20 16:42:55.88 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].

2010-10-20 16:42:55.88 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].

2010-10-20 16:42:55.89 Server Server is listening on [ ::1 <ipv6> 1434].

2010-10-20 16:42:55.89 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].

2010-10-20 16:42:55.89 Server Dedicated admin connection support was established for listening locally on port 1434.

2010-10-20 16:42:56.24 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x5, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

2010-10-20 16:42:56.24 Server SQL Server is now ready

[mikedavem]

Vous allez devoir restaurer la base master.

Ensuite comme indiqué la solution de contournement est de désactiver FILESTREAM temporairement avant d'exécuter la mise à jour vers le SP2.

Je n'ai pas encore essayé la mise à jour vers SP2. C'est une question d'heures ..

++

[dbaffaleuf]

Je n'ai pas encore essayé la mise à jour vers SP2. C'est une question d'heures ..
++

Je m'y suis un peu cassé les dents aussi, des problèmes d'espace disque (il n'y a pas de validation de l'espace libre dans l'installer du SP2). Il faut pas mal de place sur le C:\ pour pouvoir faire passer l'upgrade, bien plus que pour les anciens SP.

@johndoe08: as-tu un backup de la base master ?

[mikedavem]

Un service pack fait à l' ......
Ca serait possible ça ?

++

[Ptit_Dje]

Surtout que sur leur page:
http://www.microsoft.com/downloads/e...5-7dda1f1d4e17

La reference vers la documentation pointe sur l'installation du SP1:
http://go.microsoft.com/fwlink/?LinkId=147389

De plus je trouve que les informations contenues dans ce fichier sont tres lights...

Bref, autant pas se ruer sur les SPs ou alors sur une machine qu'on peut crasher...

[mikedavem]

Bon, pour avoir essayer le SP2 hier soir sur 2 types d'environnements je n'ai pas eu de souci en particulier.

1 environnement classique de base (32 bits) : WIN 2008 + SQL Server 2008 RTM
1 environnement plus complexe de base (32 bits) : 1 cluster actif / actif WIN 2008 + 2 instances SQL Server 2008 RTM

Filestream n'était pas activé chez moi par contre.

++

[johndoe08]

merci pour vos retours, je vais tenter une restauration de la base master.

[kagemaru]

Pour info, j'ai récemment upgradé une instance sql 2008 (ent ed) avec filestream en sp2 sans soucis particulier, en production.