SQL Server is not coming up

[Former Member]

Hi Gurus,

Here am facing one critical issue. today morning sqlserver 2008 r2 stopped working. when i and check services those are stopped so i tried to start them.

sql server service is starting and it is autometically stopping itself, same for sql server agent also.

when i go check sqlerror log it is saying as below

error 15174 severity 16 state 1( for more details please check attached screen shot).

i tried permissions for MS Sql server and given full control and i tried with local system/local service/network service options for sql server service but no luck.

we were not upgrading  SQL and not updating patch also, i am not able to find solution for this.

please help me to solve it.

Thanks in advance.

Venkat

[clas_hortien]

Hi,

can you please attached the zipped errorlogs (all of them).

Many thanks

Clas

[Former Member]

Hi,

i have attached error log, please help me to solve it.

Thanks,

Venkat

ERRORLOG.txt.zip

[clas_hortien]

Hi,

the server is definitely in an upgrade phase (Service Pack) and wants to upgrade the MSDB and other system databases. To work around your problem go and find the file sqlagent100_msdb_upgrade.sql in c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Upgrade. Open it in an text editor and search for ##MS_PolicyEventProcessingLogin#. You should find the first entry at.

IF EXISTS (SELECT * from sys.database_principals where name = N'##MS_PolicyEventProcessingLogin##')

    DROP USER [##MS_PolicyEventProcessingLogin##]

GO

in line 46348. This is the starting point of the block, that you have to delete.

From there find the line

-- End policy objects

at line 46445. This is the endline of the block.

Save the block between the start (line 46348) and the end (line 46445) into a extra textfile. Then delete the block, save the sqlagent100_msdb_upgrade.sql and restart your SQL Server. Hopefully the upgrade now runs through. If this is the case, connect to the server and open a new query and switch to the msdb database by running

use msdb

Then load the saved text and execute it.

If any of the steps are failing, please send the new errorlog file and the complete errormessages.

Regards

Clas

[Former Member]

Hi Clas,

Thanks a lot for your response. I did not do any upgrade on this server.

Before see your post, i have applied SP2 for SQL Server 2008 R2 successfully and then i tried to start services but those are not coming up and giving same error(please find attached latest log)

as per your recommondation i have gone through file sqlagent100_msdb_upgrade.sql from line 46348 to line 46445 deleted( taken backup of file) here am having some doubt so am giving files to you please see them confirm once. so that i will go further.

Thanks,

Venkat

ERRORLOG.txt.zip

sqlagent100_msdb_upgrade.sql.backup.txt.zip

cut codefrom 46348-46445.txt.zip

[clas_hortien]

Hi,

you have a different version of the SQL file, so you have to search for the begin and end of the block and ignoring my line numbers. Search for

IF EXISTS (SELECT * from sys.database_principals where name = N'##MS_PolicyEventProcessingLogin##')

    DROP USER [##MS_PolicyEventProcessingLogin##]

GO

from the beginning and from there for

-- End policy objects

These are your start and end points for the deletion. Delete/Save from 'IF EXISTS..' to '-- End Policy objects' (delete the whole lines).

Regards

Clas

[Former Member]

Hi Clas,

i have deleted mentioned lines and started sql services but no luck still same error it is giving.

here am attaching sqlagent100_msdb_upgrade.sql(which is after deleted required lines), please correct me if am doing wrong.

Thanks,

Venkat

ERRORLOG.txt.zip

sqlagent100_msdb_upgrade.sql.txt.zip

[clas_hortien]

Hi,

please search you c:\ drive for other files with the same name, I have two exact same files on my machine in different locations. Maybe you are using the wrong one.

Regards

Clas

[Former Member]

Hi Clas,

Yes am able to find same file in 4 locations as below

C:\Program Files\Microsoft SQL Server\MSSQL10_50.BOSQLSERVER\MSSQL\Upgrade

C:\Program Files\Microsoft SQL Server\MSSQL10_50.BOSQLSERVER\MSSQL\install

C:\Users\sapuser\AppData\Roaming\microsoft\windows\recentitems

C:\Users\sapuser\recent

which one do i need to change?

Thanks,

Venkat

[clas_hortien]

Hi,

only the first two files are relevant, just change both to be sure (make a backup of the file before changing the last original file).

Regards

Clas

[Former Member]

Hi Clas,

I saw your direct message, thanks a lot.

To raise a message with SAP i have to speaak with custmore, so i will communicate with them now.

in the same time please guide me to solve it.

Thanks,

Venkat

[Former Member]

Hi Clas,

i have changed both as same and started services but no luck and giving error as same.

Thanks,

Venkat

[clas_hortien]

Hi,

ok, then I need direct access to the system.

Can you prepare a OSS message and an open WTS access for this customer please.

Additionally ask the customer to download the process monitor tool from http://technet.microsoft.com/en-us/sysinternals/bb896645 and store it on the same host, where the problem occurs.

many thanks

Clas

[Former Member]

Hello Clas,

Problem is it is BO server and am not able to find sid/installation no to raise a message on sap portal.

could you please help me on this.

Thanks,

Venkat

[clas_hortien]

Hi,

please call one of the Customer Interactive Centers (https://websmp209.sap-ag.de/~sapidp/011000358700000560361996E/) and ask them to open a case for you.

Regards

Clas

[Former Member]

Hi Clas,

Many thanks for your valuable help.

I am not able to open message with SAP(due to some other problem).

I find the root cause of it. we have SQL Server 2008 R2 (installed with DVD 51041678    SQL Server 2008 R2 including SP1 CU1 (slipstream) )

by default Windows update is set on and windows update is again updating SP1 for sqlserver 2008 r2.

after update database is not starting and giving error as above.

due to urgent what i did is restored last successful OS backup and started database then it got started perfectly, when windows update came and installed database stopped working and giving error as above. i think it is bug with sql server?

strange thing for me is am not able to find sql server 2008 r2 sp2 on sap marketplace, is it available or not?

Thanks,

Venkat

[clas_hortien]

Hi,

SAP is providing only the DVDs on the marketplace, that are used for SAP shipments and for the SQL 2008 R2 SP2 there was no SAP shipment.

Can you run the following command and send me the output as a textfile, while you can connect to the SQL Server 200 R2 SP1 ?

use master

select sd.name as DBName, sl.name as OwnerName from

    sys.databases sd inner join sys.syslogins sl on sd.owner_sid = sl.sid

This will give back the database names and the owner of the DB. If the script returns '##MS_PolicyEventProcessingLogin##' for any DB, change the owner of that DB to sa. This then should solve the problem too.   

Many thanks

Clas