MSSQLWIKI

Karthick P.K on SQL Server

Archive for the ‘Startup failures’ Category

SQL Server fails to start with error "Failed allocate pages: FAIL_PAGE_ALLOCATION 1" During startup

Posted by Karthick P.K on January 6, 2013

SQL Server fails to start and If you look at the SQL Server Error log you will find "Failed allocate pages: FAIL_PAGE_ALLOCATION" and SQL Server generating exception dump. Similar to the SQL Server error log below.

 

Note: This blog is applicable when you out get of memory error during startup (or) with event ID: 2019 in system event log. For general troubleshooting of SQL Server out of memory errors follow steps in Troubleshooting SQLServer Memory

{

2013-01-02 12:31:20.91 Server      Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86)

                Jun 17 2011 00:57:23

                Copyright (c) Microsoft Corporation

                Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

2013-01-02 12:31:20.91 Server      (c) Microsoft Corporation.

2013-01-02 12:31:20.91 Server      All rights reserved.

2013-01-02 12:31:20.91 Server      Server process ID is 1583.

2013-01-02 12:31:20.91 Server      Authentication mode is MIXED.

2013-01-02 12:31:20.91 Server      Logging SQL Server messages in file ‘C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG’.

2013-01-02 12:31:20.91 Server      This instance of SQL Server last reported using a process ID of 9240 at 1/3/2013 7:31:20 PM (local) 1/4/2013 12:31:20 AM (UTC). This is an informational message only; no user action is required.

2013-01-02 12:31:20.91 Server      Registry startup parameters:

                 -d C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\master.mdf

                -e C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG

                -l C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\mastlog.ldf

2013-01-02 12:31:20.92 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2013-01-02 12:31:20.92 Server      Detected 24 CPUs. This is an informational message; no user action is required.

2013-01-02 12:31:20.94 Server      Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

2013-01-02 12:31:27.33 Server       Failed allocate pages: FAIL_PAGE_ALLOCATION 1

2013-01-02 12:31:27.33 Server     

Memory Manager                                   KB

—————————————- ———-

VM Reserved                                 1534584

VM Committed                                  51576

AWE Allocated                                     0

Reserved Memory                                1024

Reserved Memory In Use                            0

2013-01-02 12:31:27.33 Server      Error: 17311, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2013-01-02 12:31:27.33 Server      Using ‘dbghelp.dll’ version ‘4.0.5’

2013-01-02 12:31:27.34 Server      **Dump thread – spid = 0, EC = 0x00000000

2013-01-02 12:31:27.34 Server      ***Stack Dump being sent to C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\LOG\SQLDump0008.txt

2013-01-02 12:31:27.34 Server      * *******************************************************************************

2013-01-02 12:31:27.34 Server      *

2013-01-02 12:31:27.34 Server      * BEGIN STACK DUMP:

2013-01-02 12:31:27.34 Server      *   01/03/13 19:31:27 spid 4344

2013-01-02 12:31:27.34 Server      *

2013-01-02 12:31:27.34 Server      * ex_handle_except encountered exception C0000005 – Server terminating

 

}

 

 

Why would SQL Server fail with out of memory error (FAIL_PAGE_ALLOCATION)during the startup? Only possible reason that I could think of is Paged or NonPaged pool is empty.

How to prove if my Paged / NonPaged pool is empty?  Look at the system event log for the Event ID: 2019

 

You will find error in system event log similar to one you see below.

{

Event Type:        Error

Event Source:    Srv

Event Category:                None

Event ID:              2019

Date:                     2013-01-02

Time:                     12:31:00 PM

User:                     N/A

Computer:          MSSQLWIKIServer

Description:

The server was unable to allocate from the system nonpaged pool because the pool was empty.

}

 

Above error indicates nonpaged pool is empty, When Nonpaged pool is empty every application would fail. How to identify who is consuming Nonpaged pool?

 

Use poolmon.exe from windows support tools. (Steps are documented in This KB).

If you r OS is windows 2003 or above you can simple run the exe from command prompt and identify who is consuming (Leaking J) space in Paged / NonPaged pool.

Below is sample output of poolmon.exe which I collected from my test system

 

clip_image002[4]

 

Memory consumption by each tag is printed in above output. After finding the tag which is leaking the memory (Highest bytes)identify the Driver which is using the tag by using  find command or strings utility from sysinternals (search for TAG in drivers folder %Systemroot%\System32\Drivers). Once you identify the driver, check if there are any known issue with the driver or you may have to contact the vendor of the driver to identify why the driver is consuming large amount of pooled /Non-pooled memory.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki , join our Facebook group MSSQLWIKI and post your SQL Server questions to get answered by experts.

 

 

Thank you,

Karthick P.K |

 My Facebook Page |My Site| Blog space| Twitter

 

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted in Configuration, Memory, SQL Server Engine, SQL Server memory, Startup failures | Tagged: , , , , | 26 Comments »

SQL Server Exception , EXCEPTION_ACCESS_VIOLATION and SQL Server Assertion

Posted by Karthick P.K on October 16, 2012

 

I have got few request’s from  SQL Server DBA’s in past to blog about analyzing SQL Server exceptions and assertions . After seeing lot of DBA’s getting stuck when they get EXCEPTION_ACCESS_VIOLATION (or) Assertion in SQL ServersI decided to write this blog.

This blog is published with intention to make DBA’s analyze and resolve EXCEPTION_ACCESS_VIOLATION and SQL Server Assertion before contacting Microsoft support.  Exception and assertion are two different things. SQL handles both assertions and exceptions by writing the current thread’s stack to the Error log and generating a dump.  In simple An exception is an event that occurs during the execution of a program, and requires the execution of code outside the normal flow of control and assertion is the check that the programmer inserted into the code to make sure that some condition is true, If it returns false an assert is raised. SQL handles both assertions and exceptions by writing the current thread’s stack to the Error log and generating a dump, so trouble shooting steps are similar. 

 

You will find messages similar to one below in SQL Serve error logs when you get Exception or EXCEPTION_ACCESS_VIOLATION .

{

Error

External dump process returned no errors.
Using ‘dbghelp.dll’ version ’4.0.5′
SqlDumpExceptionHandler: Process 510 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
* BEGIN STACK DUMP:
*  Exception Address = 000000007752485C Module(ntdll+000000000002285C)

*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

*   Access Violation occurred reading address 0000041EA9AE2EF0

* Input Buffer 510 bytes –

ex_terminator – Last chance exception handling

}

You will find messages similar to one below in SQL Server error logs when you get an Assertion.

{

Error

spid323     Error: 17065, Severity: 16, State: 1.

spid323     SQL Server Assertion: File: < .cpp>, line = 2576 Failed Assertion = ‘fFalse’  This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted

SQL Server Assertion: File: <   .cpp>, line=2040 Failed Assertion =

}

To analyze the dump download and Install Windows Debugger from This Link 

 

Step 1 (Load the memory dump file to debugger):

Open Windbg .  Choose File menu –> select Open crash dump –>Select the Dump file (SQLDump000#.mdmp)

Note : You will find SQLDump000#.mdmp in your SQL Server error log when you get the Exception or assertion.

Step 2 (Set the symbol path to Microsoft symbols server):

on command window type

.sympath srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;

Step 3 (Load the symbols from Microsoft symbols server):

Type .reload /f and hit enter. This will force debugger to immediately load all the symbols.

 

Step 4 (check if symbols are loaded):

Verify if symbols are loaded for  SQL Server by using the debugger command lmvm

0:002> lmvm sqlservr

start             end                 module name

00000000`01000000 00000000`03679000   sqlservr T (pdb symbols)          c:\websymbols\sqlservr.pdb\21E4AC6E96294A529C9D99826B5A7C032\sqlservr.pdb

    Loaded symbol image file: sqlservr.exe

    Image path: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe

    Image name: sqlservr.exe

    Timestamp:        Wed Oct 07 21:15:52 2009 (4ACD6778)

    CheckSum:         025FEB5E

    ImageSize:        02679000

    File version:     2005.90.4266.0

    Product version:  9.0.4266.0

    File flags:       0 (Mask 3F)

    File OS:          40000 NT Base

    File type:        1.0 App

    File date:        00000000.00000000

    Translations:     0000.04b0 0000.04e4 0409.04b0 0409.04e4

 

Step 5 (Switch to exception context):

Type .ecxr

Step 6(Get the stack of thread which caused exception or assertion):

Type  kC  1000    //You will get the stack of thread which raised exception or assertion .

I have pasted one of the sample stack below, from the exception dump which I worked recently.  First thing to identify from stack is who is raising the exception. In the below stack look at the portion which is highlighted in red (In each frame before the ! symbol), that is the module which raised the exception (Exe or DLL name ).

If Exe/DLL name is Non Microsoft  module (Exe or DLL name ) then the exception is being caused by a third party component, you will need to work with the company that provided that component to get a solution. lmvm Exe/DLL name will give you the company name. For example: lmvm wininet

If Exe/DLL name is  SQLServr  (or) any other SQL Server modules then the exception is raised by SQL Server, In that case type kC 1000 and paste the stack in comments session of this blog (or) When you start thread in MSDN forums (or) In This face book group. If you don’t get any prompt reply from the community, you may need to open a support ticket with Microsoft.

Note: When you get Assertion make sure you post message line which contains   SQL Server Assertion: File: <Filename.cpp>, line = 2576 Failed Assertion =  ”  

 

0:000> kC 1000

Call Site

wininet!InternetFreeThreadInfo+0x26

wininet!InternetDestroyThreadInfo+0x40

wininet!DllMain_wininet+0xb5

wininet!__DllMainCRTStartup+0xdb

ntdll!LdrShutdownThread+0x155

ntdll!RtlExitUserThread+0x38

msvcr80!_endthreadex+0x27

msvcr80!_callthreadstartex+0x1e

msvcr80!_threadstartex+0x84

kernel32!BaseThreadInitThunk+0xd

ntdll!RtlUserThreadStart+0x1d

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Related posts:

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Debugging, SQL General, SQL Server Engine, Startup failures | Tagged: , , , , , , , , , , , , , , , | 254 Comments »

Transaction log for the database is growing and system SPID is holding open transaction

Posted by Karthick P.K on May 18, 2012

Transaction log for the database is growing and system SPID is holding open transaction

Select log_reuse_wait_desc From Sys.databases where name=’%’ will return

‘ACTIVE_TRANSACTION’

DBCC Opentran says there is a Opentran held by system thread (Similar to SPID (server process ID): 7s and 6s in below example)

Oldest active transaction:

SPID (server process ID): 7s

UID (user ID) : -1

Name : user_transaction

LSN : (543263:28204:1)

Start time : Dec 8 2011 11:02:19:483PM

SID : 0x01

Replicated Transaction Information:

Oldest distributed LSN : (544101:227459:27)

Oldest non-distributed LSN : (543263:28204:1)

Oldest active transaction:

SPID (server process ID): 6s

UID (user ID) : -1

Name : tran_sp_MScreate_peer_tables

LSN : (958510:111529:1)

Start time : May 6 2012 5:33:37:240AM

SID : 0x01

Replicated Transaction Information:

Oldest distributed LSN : (962272:93878:5)

Oldest non-distributed LSN : (0:0:0)

If we see Name: tran_sp_MScreate_peer_tables in DBCC opentran

Follow http://support.microsoft.com/kb/2509302 ie. Restart SQL-Server and wait for Script upgrade to complete and then start the SQL Server Agent.

If you see Name : user_transaction then script upgrade for database has failed leaving an open transaction. Verify the SQL Server error log to identify why the script upgrade has failed.

We can enable Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log while doing script upgrade . Identify the batch which is failing and troubleshoot the batch.

For more information Follow : https://mssqlwiki.com/2010/11/17/sqlserver2008-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100_msdb_upgrade-sql-encountered-error-574-state-0-severity-16/

Thanks

Karthick P.K

 

Posted in Space management, SQL Server Engine, Startup failures | Tagged: , , , , , | 6 Comments »

(SQLServer) Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

Posted by Karthick P.K on April 19, 2012

SQL Server might fail to start with below error

Server Error: 17190, Severity: 16, State: 1.

Server Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate

Error: 15466, Severity: 16, State: 1.

spid7s An error occurred during decryption.

Cause

CryptAcquireContext function is used by SQL Server to acquire a handle to key containers, create key containers and destroy key containers.

By default CryptAcuireContext function create key in “Roaming\Microsoft\Crypto\..” under path mentioned in below registry

HKEY_USERS\S-1-X-XXX\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\AppData

If the AppData Key is missing or if the user don’t have permission in path mentioned in above registry or if user profile is corrupted we might end up with above error.

To narrow down the issue outside SQL-Server run THIS executable which will Open or Create key container if it doesn’t exist. If the exe fails look at error code returned by exe and troubleshoot further.

To check if the problem is because of corrupted profile modify the path mentioned in HKEY_USERS\S-1-X-XXX\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\AppData folder to a

different path and check if the exe is able to create the key container.

 

Source  code for Exe is below

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <Wincrypt.h >
 
                                      
void main()
{
LPCSTR rgwchKeyContName = "Test123456";  
HCRYPTPROV m_hCryptoProviderFB;
BOOL ret;
BOOL ret2;

ret=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_SILENT);
    
if (!ret && GetLastError() == NTE_BAD_KEYSET)

{
    
    printf("\nUnable to open Keyset.CryptAcquireContext failed with error: 0x%X . \nWe will try creating key",GetLastError());

    ret2=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET | CRYPT_SILENT);
        if (!ret2)
        {
        printf("\nCryptAcquireContext failed creating key.Error: 0x%X",GetLastError());
        }
        else
        {
        printf("\nKey created");
        }
    exit;
}


else if (!ret && GetLastError() == NTE_BAD_KEYSET)
{
printf("CryptAcquireContext failed with error: 0x%X",GetLastError());
}

else
{

    printf("CryptAcquireContext opened key. Return value is 0x%X.",ret);
}

    if (CryptReleaseContext(m_hCryptoProviderFB,0))
    {
    printf("\nHandle is released.\n");
    }
    else
    {
    printf("\nHandle could not be released.\n");
    }

}

 

 

Thanks

Karthick P.K

Posted in Configuration, Security, Startup failures | Tagged: , , , , , , , | 19 Comments »

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive

Posted by Karthick P.K on November 25, 2010

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive

Error:

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.

Error: 927, Severity: 14, State: 2.

Database ‘model’ cannot be opened. It is in the middle of a restore.

Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

 

Cause

We get above error if the Model database is corrupted. TempDB is recreated every time when SQLServer restarts using model database, We receive "Could not create tempdb" because model database is corrupted

Resolution

1. Start SQLServer with Traceflag 3608,3609,-c,-f

-T3609 Will keep the existing TEMPDB, Which means when SQLServer is restarted SQLServer uses the existing tempdb instead of re-creating it as long as checkpoint in the tempdb had been done immediately before the last server shutdown

{

Sqlservr.exe -sInstanceName -T3608 -c -f -T3609

}

2. Open SQLCMD and make DAC connection

{

SQLCMD -E -SADMIN:Servername\InstanceName

}

3. Use Tempdb

Go

{

Above command will open the TempDB. If you get error while executing "Use Tempdb" your tempdb is not cleanly shutdown.

To work around this copy a Tempdb.mdf and templog.ldf from Cleanly shutdown SQLServer of same version and replace it in TEMPDB location.

Also note Transaction log location is stored in Tempdb.mdf.So you may have to copy Tlog file in destination server in same directory structure as it existed source server .

Once you copy the file restart SQLServer using -T3608 -c -f -T3609 Then run

Use Tempdb

Go

}

4. sp_detach_db ‘model

5. Replace model.mdf and model.ldf from different server of same build

5. sp_attach_db ‘model’,’X:\PAth\model.mdf’,’x:\modellog.ldf’

{

Note: If you are in SQLServer2008 we can use "create database with attach" option

 CREATE DATABASE [model] ON
 ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\model.mdf' ),
 ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\modellog.ldf' )
 FOR ATTACH
 go

}

Now we can start SQL Server normally and if you find any database in restoring state if can use RESTORE database DBNAME WITH RECOVERY to recover and open the database.

 

 

Thank You and Best Regards,

Karthick P.K

Posted in Recovery, Startup failures | Tagged: , , , , , , , , , , | 24 Comments »

SQL Server2008/SQL Server2012: Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16

Posted by Karthick P.K on November 17, 2010

SQL Server 2008 : Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16

SQL Server 2012 : Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’  encountered error

SQL Server 2008/2012 instance fails to start or hangs after service pack or Cumulative update installation.

 

Error

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.

 

 

Error: 574, Severity: 16, State: 0.

CONFIG statement cannot be used inside a user transaction.

Error: 912, Severity: 21, State: 2.

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.

Error: 3417, Severity: 21, State: 3.

Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 15173, state 1, severity 16

Start SQL Server from command prompt using trace flag –T902 to disable script execution

1.Turn off Implicit transaction

{

EXEC sys.sp_configure N’user options’, N’0′

GO

RECONFIGURE WITH OVERRIDE

GO

}

2. SQL Server not able to create temp_MS_AgentSigningCertificate_database.mdf

Error:

{

Directory lookup for the file "P:\Data\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 2(The system cannot find the file specified.).

Error: 1802, Severity: 16, State: 1.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

spid7s Error: 912, Severity: 21, State: 2.

spid7s Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘Q:\Data\temp_MS_AgentSigningCertificate_database_log.LDF’.

}

This error is raised when the default database location is invalid. Edit below registry to have a valid directory for default database location.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<Instance Name>\Setup\SQLDataRoot

3. Check if there are Orphan users in system databases and fix them.

{

EXEC sp_change_users_login ‘Report’;

}

4. If you see “error 15173, state 1, severity 16”

Ex: Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 15173, state 1, severity 16

Revoke the permissions granted on ‘##MS_PolicyEventProcessingLogin##’

you can use the below script to identify the users who have permissions granted on ‘##MS_PolicyEventProcessingLogin##’

select a.name,b.permission_name from sys.server_principals a,sys.server_permissions b,sys.server_principals c

where a.principal_id= b.grantee_principal_id and b.grantor_principal_id=c.principal_id and c.name = ‘##MS_PolicyEventProcessingLogin##’

 

Resolution

If none of the above resolves the issue then you can use Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log. Identify the batch which is failing and troubleshoot the batch.

 

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

 

Thank you,

Karthick P.K | My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in SQL Cluster Setup, SQL Server Setup, Startup failures | Tagged: , , , , , , , , , , , | 14 Comments »

SQLServer 2008 Fails to come online on cluster after upgrade

Posted by Karthick P.K on October 29, 2010

SQL-Server 2008 Fails to come online on cluster after upgrade.

Reason:

Server is in script upgrade mode. Only administrator can connect at this time.

Login failed for user ‘ccccc\xxxxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

Issue:

SQL-Server 2008 Fails to come online on cluster after upgrade.

Error from SQL-Server Error log

“Reason: Server is in script upgrade mode. Only administrator can connect at this time.”

“Login failed for user ‘ccccc\xxxxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.”

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25. 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 Error log entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Resolution:

Start the SQLServer from services console (or) Command prompt and wait till the master database is completely upgraded. Once the script upgrade is complete start the SQL-Server normally from Cluster admin (or) Failover cluster manager.

Cause: During the Script upgrade mode only administrator can connect to SQL-Server, So when the SQL-Server resource is brought online ISAlive check fails immediately before upgrade completes and SQL-Server resource goes down. When we start SQLServer from services or command prompt ISAlive check doesn’t happen so upgrade completes. Once the upgrade is completed we can start SQL-Server normally.

Thanks,

Regards

karthick pk

Posted in SQL Cluster Setup, SQL Server Cluster, Startup failures | Tagged: , | 1 Comment »