Service Pack upgrade corrupting database

Question

Hello, I am trying to upgrade a SQL 2008 R2 SP2 server to SP3 and when I run the service pack install it corrupts the Master database so that SQL server will not start.

I see a lot of information on the net about how to recover from it but I was wondering if there are things I should look for in order to prevent it from even happening in the first place.

Any advice is helpful.

Thanks

Answer 1

It is much more likely your database was already corrupt and was detected during the SP installation.

Database corruption is 99.9% of the time due to a hard drive problem.

Answer 2

Hello, I am trying to upgrade a SQL 2008 R2 SP2 server to SP3 and when I run the service pack install it corrupts the Master database so that SQL server will not start.

What is the exact error message you get? If I recall the error message it does really sound alarming, but what happens is that something goes wrong when running an install script. And if you are lucky, it may be easy to recover from that error.

If you have the possibility to upload the SQL Server errorlog somewhere (Dropbox, OneDrive etc) and post a link, we can take a look.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Hi Mankney2,

Could you please share us the detailed error message from errorlog which is located at Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ about your issue?

In addition, please refer to Master Database Corruption - Know How To Resolve and Rebuild System Databases which might help.

Best Regards,

Amelia

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Looking at the error log I see some login failures such as this.

sysalerts_performance_counters_view...

2019-12-31 13:13:21.04 spid7s      Creating table MSdbms
2019-12-31 13:13:21.04 spid7s      Creating table MSdbms_datatype
2019-12-31 13:13:21.11 Logon       Error: 18401, Severity: 14, State: 1.
2019-12-31 13:13:21.11 Logon       Login failed for user 'domain\user'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: <local machine>]

Domain\user has been substituted for the actual user. 

The SQL box clone that I am using has no network connectivity by the way as the network cards have been turned off to isolate this from the network. I say this because while it still failed even when I tried briefly to have it on the network I don't know if it might make a difference elsewhere.

Anyways then at the end of the log I get this

2019-12-31 13:13:25.30 spid7s      Executing: CREATE SYNONYM [dbo].[syn_sysutility_ucp_space_utilization] FOR [sysutility_mdw].[sysutility_ucp_core].[space_utilization]
2019-12-31 13:13:25.30 spid7s      Error: 942, Severity: 14, State: 4.
2019-12-31 13:13:25.30 spid7s      Database 'sysutility_mdw' cannot be opened because it is offline.
2019-12-31 13:13:25.31 spid7s      Error: 912, Severity: 21, State: 2.
2019-12-31 13:13:25.31 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 942, state 4, severity 14. 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.
2019-12-31 13:13:25.32 spid7s      Error: 3417, Severity: 21, State: 3.
2019-12-31 13:13:25.32 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.
2019-12-31 13:13:25.32 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Answer 5

2019-12-31 13:13:25.30 spid7s      Executing: CREATE SYNONYM [dbo].[syn_sysutility_ucp_space_utilization] FOR [sysutility_mdw].[sysutility_ucp_core].[space_utilization]
2019-12-31 13:13:25.30 spid7s      Error: 942, Severity: 14, State: 4.
2019-12-31 13:13:25.30 spid7s      Database 'sysutility_mdw' cannot be opened because it is offline.
2019-12-31 13:13:25.31 spid7s      Error: 912, Severity: 21, State: 2.
2019-12-31 13:13:25.31 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 942, state 4, severity 14. 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.

Thankfully, it is a case of things sounding a lot more serious than they are.

I checked my instance of SQL 2008 R2 SP3 and looked at the update script, and it creates and executes a stored procedure which includes this piece of code:

   IF (DB_ID (@mdw_database_name) IS NOT NULL)       AND ((@require_mdw = 1) OR (dbo.fn_sysutility_get_is_instance_ucp() = 1))
   BEGIN
       -- This instance is a UCP; synonyms should reference objects in sysutility_mdw
       SET @database = @mdw_database_name;
       SET @schema = 'sysutility_ucp_core';

       -- Dimensions
       EXEC dbo.sp_sysutility_ucp_recreate_synonym_internal N'syn_sysutility_ucp_computers', @database, @schema, 'latest_computers';
       ...

       -- Measures
       EXEC dbo.sp_sysutility_ucp_recreate_synonym_internal N'syn_sysutility_ucp_cpu_utilization', @database, @schema, 'cpu_utilization';
       EXEC dbo.sp_sysutility_ucp_recreate_synonym_internal N'syn_sysutility_ucp_space_utilization', @database, @schema, 'space_utilization';

So apparently, you have a database called sysutility_mdw, and this database is offline for some reason. Or went offline for some reason. If we look at the script and check the error message closely, we see that it is not the first synonym that fails - unless for some reason the calls before this never attempt to actually create any synonym.

Anyway, check the state of your sysutility_mdw database, and make sure that it is online. You could also drop it, and then the upgrade script should get through, but things related to MDW may stop working. (MDW is one of these things you should be careful with, because there is not really any clean way to uninstall it.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Okay this is making sense, there are many DB's on that server that are offline as we go through and cleanup unused databases.

I will take a look at it when I am back from vacation.

I have also included a link to the full error log but it sounds like we are on the right track.

Error Log Link

I am learning so much thanks to this community.

Thanks.

Answer 7

Hi Mankney2,

Was your issue resolved?

If the replies could help you, please mark the useful reply as answer to help other community members find the helpful reply quickly.

If you have any questions or concerns, please feel free to ask.

Best Regards,

Amelia

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Bringing the Offline DB's worked. Thanks everyone for your help.