Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL 2008 Services fails to start

已答复 SQL 2008 Services fails to start

  • Friday, October 05, 2012 2:51 PM
     
     
    Sign In to Vote
    0

    On My Windows 2008 R2 Standard 64 bit server I am running SQL 2008 Standard 64 bit server.

    I have three instances running. All databases were migrated from a SQL 2005 server. All working well so far.

    Problem I have when ever I restart the sql server service or restart the Windows 2008 server the SQL server and sql agent services fail to start.

    I am using a domain account to logon as the service.

    All I need to do to get it to work is just type in the password again.

    Any thoughts on why 2008 is not retaining the sql services logon information?

    Thanks

    Tom

    Thomas R Grassi Jr

     

All Replies

  • Friday, October 05, 2012 2:57 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    When they fail to start, I generally check two things (to begin with)
    1. Do we see SQL ERRORLOG generated? (http://sqlserver-help.com/2011/06/26/help-where-is-sql-server-errorlog/)
    2. If no ERRORLOG then what does appplication and system event log say? (Start > Run > EventVwr.msc)

    There could be various causes and troubleshooting would depend on answer to above questions.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked As Answer by TRGOneCare Friday, October 05, 2012 6:00 PM
    •  
     
  • Friday, October 05, 2012 3:06 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    I have seen services stopping their execution when there is memory contention on the server. You are mentioning that the server is performing well, but consider to balance memory across all instances. Please limit memory usage on all three instances and make sure you leave 4 GB of RAM the operating system.

    Any error messages on the SQL Server Agent Error Log?

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    • Marked As Answer by TRGOneCare Friday, October 05, 2012 6:00 PM
    •  
     
  • Friday, October 05, 2012 3:17 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    Thanks

    Where to I limit the memory? I am poking around

    Also getting this error over and over after the restart

    Log Name:      Application
    Source:        MSSQLSERVER
    Date:          10/5/2012 9:31:18 AM
    Event ID:      18456
    Task Category: Logon
    Level:         Information
    Keywords:      Classic,Audit Failure
    User:          NETWORK SERVICE
    Computer:      server10
    Description:
    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSSQLSERVER" />
        <EventID Qualifiers="49152">18456</EventID>
        <Level>0</Level>
        <Task>4</Task>
        <Keywords>0x90000000000000</Keywords>
        <TimeCreated SystemTime="2012-10-05T13:31:18.000000000Z" />
        <EventRecordID>8033</EventRecordID>
        <Channel>Application</Channel>
        <Computer>server10</Computer>
        <Security UserID="S-1-5-20" />
      </System>
      <EventData>
        <Data>NT AUTHORITY\NETWORK SERVICE</Data>
        <Data> Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.</Data>
        <Data> [CLIENT: &lt;local machine&gt;]</Data>
        <Binary>184800000E0000000800000054004700430053003000310030000000070000006D00610073007400650072000000</Binary>
      </EventData>
    </Event>

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Friday, October 05, 2012 6:00 PM
    •  
     
  • Friday, October 05, 2012 4:34 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    Please see the following link to know how to limit memory usage by SQL Server:

    http://msdn.microsoft.com/en-us/library/ms178067(v=sql.105).aspx

    To troubleshoot error 18456, please read the following resources:

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked As Answer by TRGOneCare Friday, October 05, 2012 6:01 PM
    •  
     
  • Friday, October 05, 2012 4:50 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    I will look at the memory later.

    My big issue is the 18456 error.

    The service that is trying to start use Network Servce as a logon  and it is failing everytime I try to start the service.

    Now  I am trying this sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE' but I do not know how to do this for the individual database?

    When I look at the permissions from the properties of the database I see under Users and Roles one entre domain\server10$ type user

    then under permissions for server10$ explicit  I see the grantor dbo and only a check in Connect GRant

    All my other databasees have grantor as domain\administrator

    So how to I grant login for the above on my database susdb

    Thomas R Grassi Jr

    Just checked my sql log for this instance

    Date,Source,Severity,Message
    10/05/2012 12:59:46,Logon,Unknown,Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    10/05/2012 12:59:46,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
    10/05/2012 12:56:29,Logon,Unknown,Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    10/05/2012 12:56:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
    10/05/2012 12:52:14,Logon,Unknown,Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    10/05/2012 12:52:14,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
    10/05/2012 12:43:29,Logon,Unknown,Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    10/05/2012 12:43:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.

    does this help?

    • Edited by TRGOneCare Friday, October 05, 2012 5:13 PM
    • Marked As Answer by TRGOneCare Friday, October 05, 2012 6:01 PM
    •  
     
  • Friday, October 05, 2012 5:23 PM
     
     Answered
    Sign In to Vote
    0

    Hi Thomas

    Please modify Service Log on as account using SQL Server configuration Manager. Make sure it meets below requirement before adding domain user as service account.

    To perform its functions, SQL Server Agent must be configured to use the credentials of an account that is a member of the sysadmin fixed server role in SQL Server. The account must have the following Windows permissions:

    • Log on as a service (SeServiceLogonRight)

    • Replace a process-level token (SeAssignPrimaryTokenPrivilege)

    • Bypass traverse checking (SeChangeNotifyPrivilege)

    • Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

    Cheers, Vishal

    • Marked As Answer by TRGOneCare Friday, October 05, 2012 6:01 PM
    •  
     
  • Friday, October 05, 2012 6:01 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    Had to add NT Authority\Network Service to sysadmin Server Roles

    Now working

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Friday, October 05, 2012 6:02 PM
    •