Service fails to start after SQL SERVER 2008 upgrade to SP4

To improve SQL performance and security, number 2nd upgrades the SQL SP4 patch to the customer, and everything goes well in the upgrade process. However, after restarting the server, the SQL service cannot be started and the event log raises this error:

The script level upgrade of database ' master ' failed because the upgrade step ' Sqlagent100_msdb_upgrade.sql ' encountered error 598 (status 1, severity 25).

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/82/B6/wKioL1dfZETQcRTcAAHRd2uYqZE056.png-wh_500x0-wm_3 -wmp_4-s_989664925.png "title=" 111.png "alt=" Wkiol1dfzetqcrtcaahrd2uyqze056.png-wh_50 "/>

Try to restore the template master database, repair installation, upgrade repair can not be resolved, after the online search scheme, found in the SQL startup parameters add;-t902 parameter can start SQL, and can successfully log on to SMSS, the steps are as follows:

Enable trace flag 902 on the instance of SQL Server R2. To does this, follow these steps:

Open SQL Server Configuration Manager.

In SQL Server Configuration Manager, click SQL Server Services.

Double-click the SQL serverservice.

In the SQL Server Properties dialog box, click on the Advanced tab.

On Click the Advanced tab, locate the Startup Parameters item.

ADD;-t902 to the end of the existing string value, and then click OK.

Right-click the SQL serverservice, and then click Start.

IF The SQL Server Agent service is running, right-click the SQL Server Agent service, and then click Stop.

Open SQL Server Management Studio, and then connect to the instance of SQL Server R2.

Run the following statements:

EXEC sp_configure ' show advanced ', 1;

RECONFIGURE with override;

EXEC sp_configure ' allow updates ', 0;

RECONFIGURE with override;

EXEC sp_configure ' Agent XPs ', 1;

RECONFIGURE with override;

GO

In SQL Server Configuration Manager, right-click the SQL Serverservice, and then click Stop.

Remove trace flag 902 on the instance of SQL Server R2. To does this, delete, and-t902 from the string value, which updated in step 1f.

Right-click the SQL serverservice, and then click Start.

Right-click the SQL Server Agent Service, and then click Start.

In SQL Server Management Studio, reconnect to the instance of SQL Server R2.

In Object Explorer, expand Management, right-click Data Collection, and then click Enable Data Collection.

Note If data collection is already enabled and the Enable data collection item is unavailable.

After the above steps, and then start the SQL service, found that the service is still abnormal, try to add,-t902 parameters and manually run the error sqlagent100_msdb_upgrade.sql script, found that the operation results have this error:

Directory lookup for File "D:\SQLDATA\temp_MS_AgentSigningCertificate_database.mdf" failed with operating system error 2 (the system cannot find the file specified.) )。

found that the D:\SQLDATA directory does not exist, and then manually set up the SqlData directory, and then run the script, successfully completed. Then delete the boot parameter ,-t902, and then restart the service.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/82/B7/wKioL1dfaK7SahxUAABm9Ss2mnU554.png-wh_500x0-wm_3 -wmp_4-s_3674659017.png "title=" Qq20160602194420.png "alt=" Wkiol1dfak7sahxuaabm9ss2mnu554.png-wh_50 "/>

Some of the questions are slightly different, and after he has manipulated it, it leads to other error messages.

An error message can be seen from the event:

Could not allocate space for object ' dbo. #bulkpackage ' in database ' tempdb '

This is because tempdb is too small to cause the execution statement to fail.

-t902 after restarting SQL server, change the tempdb file to large. Delete again;-t902. It's OK to restart SQL Server.

This article is from the "Stone Blog" blog, make sure to keep this source http://dayday.blog.51cto.com/3248/1789021

Service fails to start after SQL SERVER 2008 upgrade to SP4