Service fails to start after SQL SERVER 2008 upgrade to SP4

Source: Internet
Author: User
Tags management studio sql server management sql server management studio

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.