none
MS SQL 2016(AON) и учетные записи служб RRS feed

  • Вопрос

  • Доброго времени суток!

    Есть три сервера WS2016 на каждом по MS SQL 2016 в AON.

    Создал по мануалу 

    https://www.derekseaman.com/2018/09/sql-2017-always-on-ag-pt-3-service-accounts.html

    Сервисную учетную запись, настроил запуск службы MSSQLServer на всех трех серверах от имени созданного сервисного аккаунта.

    На втором и третьем сервере все ОК, а вот на первом служба запускается и сразу падает.

    В логах вижу только 

    "

    Служба "SQL Server (MSSQLSERVER)" завершена из-за следующей внутренней ошибки: 
    Указанное имя ресурса не найдено в файле образа.

    "

    Кто сталкивался? Как диагностировать и "вылечить"?


Все ответы

  • Здравствуйте. А что говорит eventvwr?
  • Значит так, в вашем случае нужно смотреть не только журнал событий системы, цитату из которого вы привели, но и журнал самомого сервера SQL - текстовый файл ERRORLOG в подпапке Log в папке, куда установлен сервер.

    Текст сообщения об ошибке соответствует коду ошибки 1814 (он может ещё выводиться в лог как 0x716 или 0x80070716).  Текст сообщения запутывает: почему-то при выдаче сообщения SCM решает, что это  - ошибка Win32, в то время как это - ошибка SQL Server с тем же кодом и текстом: "Could not create tempdb..."

    Т.е. эта ошибка говорит о невозможности создания БД tempdb (она создается при запуске копированием файлов БД model): либо невозможно прочитать файлы БД model(нет либо файлов, либо разрешений), либо они повреждены, либо нет разрешений на создание файлов в папке, где должны быть файлы tempdb. Точнее причину можно посмотреть в упомянутом ERRORLOG.


    Слава России!


    • Изменено M.V.V. _ 11 мая 2019 г. 21:06
  • БД не повреждены, под старой учеткой запускаются без проблем.

    Искал Errolog, нашел только за 2018 в "C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\ .

    Т.к. это был MS SQL 2008К2 обновленный до 2016, видимо он после обновления начал писать в другое место...

    Т.к. SQL сейчас запущен под другим пользователем, зашел в Management Studio и смотрю лог там...

    Кстати по этой же учеткой для служб sql агент прекрасно запускается...

  • В журнале SQL вижу непонятные мне ошибки:

    Error: 35262, Severity: 17, State 1

    и

     Error: 18456, Severity: 14, State 38

    Думаю действительно дело в правах, но почему на втором и третьем сервере все ОК?

    Какие права добавить и как их добавить учетке служб?

  • Права - это разрешения на соответствующие папки. Могут быть даны напрямую или через членство в группах Для создания tempdb требуется Полный доступ на папку, где она находится.

    Если сейчас вы от ошибки ушли, заменив учетную запись, то смотрите старые копии ERRORLOG (несколько штук должны быть сохранены в той же папке в переименованном виде). Ориентируйтесь по дате ошибки в журнале событий системы.

    PS Ошибки, если по ним вам нужна помощь, пожалуйста, копируйте целиком вместе со всеми кодами и текстами - иначе вам придётся ждать телепатов.


    Слава России!

  • Скажите, а вы gMSA создавали этой утилитой (из статьи) или по-нормальному?

    Что говорит командлет Test-ADServiceAccount gmsa_name с проблемного хоста?

    В локальной политике проверьте наличие прав - "Log On As a Service"

  • Скажите, а вы gMSA создавали этой утилитой (из статьи) или по-нормальному?

    Что говорит командлет Test-ADServiceAccount gmsa_name с проблемного хоста?

    В локальной политике проверьте наличие прав - "Log On As a Service"

    Коллега, судя по всему, что с этим у автора всё в порядке: судя по ошибке из первого поста, учетная запись для службы позволяет запустить службу, ибо ошибка происходит уже после запуска на этапе инициализации SQL Server (разве что, ошибка относилась к запуску под другой учетной записью).

    Но лишний раз всё проверить не помешает, конечно.


    Слава России!

  • С учетной запись полный порядок, тест проходи на всех серверах.

    Долго искал разницу между первым(проблемным) и другими серверами...

    Увидел что на первом сервере на места размещения логов, баз и темпбаз нет прав у NT SERVICE \ MSSQLSERVER, добавил, все равно служба не запустилась.

    Прилагаю лог

    2019-05-18 17:26:42.54 Server      Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) - 13.0.5081.1 (X64) 
    	Jul 20 2018 22:12:40 
    	Copyright (c) Microsoft Corporation
    	Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: )
    
    2019-05-18 17:26:42.54 Server      UTC adjustment: 3:00
    2019-05-18 17:26:42.54 Server      (c) Microsoft Corporation.
    2019-05-18 17:26:42.54 Server      All rights reserved.
    2019-05-18 17:26:42.54 Server      Server process ID is 15696.
    2019-05-18 17:26:42.54 Server      System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge R630'.
    2019-05-18 17:26:42.54 Server      Authentication mode is MIXED.
    2019-05-18 17:26:42.54 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2019-05-18 17:26:42.54 Server      The service account is 'company\svc-msa-aon-DB$'. This is an informational message; no user action is required.
    2019-05-18 17:26:42.54 Server      Registry startup parameters: 
    	 -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
    	 -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
    	 -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    2019-05-18 17:26:42.54 Server      Command Line Startup Parameters:
    	 -s "MSSQLSERVER"
    2019-05-18 17:26:42.55 Server      SQL Server detected 2 sockets with 6 cores per socket and 6 logical processors per socket, 12 total logical processors; using 12 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
    2019-05-18 17:26:42.55 Server      SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.
    2019-05-18 17:26:42.55 Server      Detected 524194 MB of RAM. This is an informational message; no user action is required.
    2019-05-18 17:26:42.55 Server      Using locked pages in the memory manager.
    2019-05-18 17:26:42.55 Server      Large Page Allocated: 32MB 
    2019-05-18 17:26:42.55 Server      Large Page Allocated: 32MB 
    2019-05-18 17:26:42.80 Server      Error: 8319, Severity: 16, State: 1.
    2019-05-18 17:26:42.80 Server      Windows kernel object 'Global\SQL_130_MEMOBJ_28_MSSQLSERVER_0' already exists. It's not owned by the SQL Server service account. SQL Server performance counters are disabled.
    2019-05-18 17:26:42.80 Server      Error: 3409, Severity: 16, State: 1.
    2019-05-18 17:26:42.80 Server      Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
    2019-05-18 17:26:43.07 Server      Default collation: Cyrillic_General_CI_AS (us_english 1033)
    2019-05-18 17:26:43.07 Server      Processor affinity turned on: node 0, processor mask 0x000000000000001f. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
    2019-05-18 17:26:43.07 Server      Processor affinity turned on: node 1, processor mask 0x00000000000003c0. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
    2019-05-18 17:26:43.09 Server      Buffer pool extension is already disabled. No action is necessary. 
    2019-05-18 17:26:43.11 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
    2019-05-18 17:26:43.12 Server      InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
    2019-05-18 17:26:43.12 Server      Implied authentication manager initialization failed. Implied authentication will be disabled.
    2019-05-18 17:26:43.12 Server      The maximum number of dedicated administrator connections for this instance is '1'
    2019-05-18 17:26:43.12 Server      This instance of SQL Server last reported using a process ID of 6816 at 18.05.2019 17:25:06 (local) 18.05.2019 14:25:06 (UTC). This is an informational message only; no user action is required.
    2019-05-18 17:26:43.12 Server      Node configuration: node 0: CPU mask: 0x000000000000003f:0 Active CPU mask: 0x000000000000001f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.12 Server      Node configuration: node 1: CPU mask: 0x0000000000000fc0:0 Active CPU mask: 0x00000000000003c0:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.14 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2019-05-18 17:26:43.14 Server      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.19 Server      Query Store settings initialized with enabled = 1, 
    2019-05-18 17:26:43.19 spid6s      Starting up database 'master'.
    2019-05-18 17:26:43.19 Server      In-Memory OLTP initialized on standard machine.
    2019-05-18 17:26:43.23 Server      CLR version v4.0.30319 loaded.
    2019-05-18 17:26:43.26 spid6s      6 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
    2019-05-18 17:26:43.26 spid6s      0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
    2019-05-18 17:26:43.29 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
    2019-05-18 17:26:43.32 spid6s      Resource governor reconfiguration succeeded.
    2019-05-18 17:26:43.32 spid6s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
    2019-05-18 17:26:43.32 spid6s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
    2019-05-18 17:26:43.36 spid6s      SQL Trace ID 1 was started by login "sa".
    2019-05-18 17:26:43.36 spid6s      Server name is 'server'. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Database mirroring has been enabled on this instance of SQL Server.
    2019-05-18 17:26:43.37 spid13s     Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'company8' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid13s     Always On Availability Groups: Waiting for local Windows Server Failover Clustering service to start. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'company8Store' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'rozmarin8' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'rozmarin8store' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'WMS' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'company2' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'FStore' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid8s      Starting up database 'mssqlsystemresource'.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'company2' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid13s     Always On Availability Groups: Local Windows Server Failover Clustering service started. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'company2Store' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid13s     Always On Availability Groups: Waiting for local Windows Server Failover Clustering node to start. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'RP' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Error: 35262, Severity: 17, State: 1.
    2019-05-18 17:26:43.37 spid6s      Skipping the default startup of database 'company3' because the database belongs to an availability group (Group ID:  65537). The database will be started by the availability group. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid13s     Always On Availability Groups: Local Windows Server Failover Clustering node started. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid13s     Always On Availability Groups: Waiting for local Windows Server Failover Clustering node to come online. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid6s      Starting up database 'msdb'.
    2019-05-18 17:26:43.37 spid13s     Always On Availability Groups: Local Windows Server Failover Clustering node is online. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.37 spid13s     Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.38 spid8s      The resource database build version is 13.00.5081. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.45 spid8s      Starting up database 'model'.
    2019-05-18 17:26:43.51 spid12s     A self-generated certificate was successfully loaded for encryption.
    2019-05-18 17:26:43.52 spid12s     Server is listening on [ 'any' <ipv6> 1433].
    2019-05-18 17:26:43.52 spid12s     Server is listening on [ 'any' <ipv4> 1433].
    2019-05-18 17:26:43.52 spid12s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].ф
    2019-05-18 17:26:43.52 spid12s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2019-05-18 17:26:43.53 Server      Server is listening on [ ::1 <ipv6> 1434].
    2019-05-18 17:26:43.53 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2019-05-18 17:26:43.53 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2019-05-18 17:26:43.53 spid12s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2019-05-18 17:26:43.53 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
    2019-05-18 17:26:43.53 spid13s     Always On: The local replica of availability group 'SQL-CLuster-server' is starting. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.53 spid13s     The state of the local availability replica in availability group 'SQL-CLuster-server' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'.  The state changed because the local instance of SQL Server is starting up.  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
    2019-05-18 17:26:43.56 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/server.company.ua ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
    2019-05-18 17:26:43.56 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/server.company.ua:1433 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
    2019-05-18 17:26:43.58 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:43.58 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'WMS'. [CLIENT: 192.168.3.9]
    2019-05-18 17:26:43.62 spid8s      Polybase feature disabled.
    2019-05-18 17:26:43.62 spid8s      Clearing tempdb database.
    2019-05-18 17:26:43.62 spid8s      Error: 5123, Severity: 16, State: 1.
    2019-05-18 17:26:43.62 spid8s      CREATE FILE encountered operating system error 5(Отказано в доступе.) while attempting to open or create the physical file 'G:\tempdb.mdf'.
    2019-05-18 17:26:43.63 spid8s      Error: 17204, Severity: 16, State: 1.
    2019-05-18 17:26:43.63 spid8s      FCB::Open failed: Could not open file G:\tempdb.mdf for file number 1.  OS error: 5(Отказано в доступе.).
    2019-05-18 17:26:43.63 spid8s      Error: 5120, Severity: 16, State: 101.
    2019-05-18 17:26:43.63 spid8s      Unable to open the physical file "G:\tempdb.mdf". Operating system error 5: "5(Отказано в доступе.)".
    2019-05-18 17:26:43.63 spid8s      Error: 1802, Severity: 16, State: 4.
    2019-05-18 17:26:43.63 spid8s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
    2019-05-18 17:26:43.63 spid8s      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.
    2019-05-18 17:26:43.63 spid8s      SQL Server shutdown has been initiated
    2019-05-18 17:26:43.63 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
    2019-05-18 17:26:43.70 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:43.70 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: 192.168.3.9]
    2019-05-18 17:26:43.71 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:43.71 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: 192.168.3.9]
    2019-05-18 17:26:43.72 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:43.72 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: <local machine>]
    2019-05-18 17:26:43.73 spid51      Always On: The local replica of availability group 'SQL-CLuster-server' is preparing to transition to the primary role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.79 spid51      The state of the local availability replica in availability group 'SQL-CLuster-server' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'.  The state changed because the availability group is coming online.  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
    2019-05-18 17:26:43.80 Server      The lease worker of availability group 'SQL-CLuster-server' is now sleeping the excess lease time (164766 ms) supplied during online. This is an informational message only. No user action is required.
    2019-05-18 17:26:43.87 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:43.87 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: 169.254.3.165]
    2019-05-18 17:26:43.87 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:43.87 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: <local machine>]
    2019-05-18 17:26:43.88 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:43.88 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: <local machine>]
    2019-05-18 17:26:43.88 Server      The state of the local availability replica in availability group 'SQL-CLuster-server' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'.  The state changed because the local replica has completed processing Online command from Windows Server Failover Clustering (WSFC).  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
    2019-05-18 17:26:43.88 spid20s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid22s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid13s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid23s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid18s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid24s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid21s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid25s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid26s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid27s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.88 spid22s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.88 spid20s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.88 spid13s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.88 spid23s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.88 spid18s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.88 spid20s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.88 spid20s     Database 7 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.88 spid23s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.88 spid23s     Database 9 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.88 spid13s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.88 spid13s     Database 5 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.88 spid22s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.88 spid22s     Database 8 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.88 spid18s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.88 spid18s     Database 6 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.91 spid24s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.91 spid21s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.91 spid25s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.91 spid26s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.91 spid27s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.91 spid24s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.91 spid24s     Database 11 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.91 spid28s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 9CE11159-13E5-475F-8964-18F705476A08:1
    2019-05-18 17:26:43.91 spid27s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.91 spid27s     Database 14 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.91 spid28s     DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 776C6629-8440-4747-94C7-745B36E7473D:1
    2019-05-18 17:26:43.91 spid25s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.91 spid25s     Database 12 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.91 spid21s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.91 spid21s     Database 10 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.91 spid26s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.91 spid26s     Database 13 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:43.91 spid28s     Error: 904, Severity: 16, State: 2.
    2019-05-18 17:26:43.91 spid28s     Database 15 cannot be autostarted during server shutdown or startup.
    2019-05-18 17:26:44.01 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:44.01 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: <local machine>]
    2019-05-18 17:26:44.01 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:44.01 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: <local machine>]
    2019-05-18 17:26:44.06 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:44.06 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: <local machine>]
    2019-05-18 17:26:44.07 Logon       Error: 18456, Severity: 14, State: 38.
    2019-05-18 17:26:44.07 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'company8'. [CLIENT: <local machine>]
    2019-05-18 17:26:44.65 spid8s      Error: 25725, Severity: 16, State: 1.
    2019-05-18 17:26:44.65 spid8s      An error occurred while trying to flush all running Extended Event sessions.  Some events may be lost.
    

     

  • Судя по этому участку лога

    2019-05-18 17:26:43.62 spid8s      Error: 5123, Severity: 16, State: 1.
    2019-05-18 17:26:43.62 spid8s      CREATE FILE encountered operating system error 5(Отказано в доступе.) while attempting to open or create the physical file 'G:\tempdb.mdf'.
    2019-05-18 17:26:43.63 spid8s      Error: 17204, Severity: 16, State: 1.
    2019-05-18 17:26:43.63 spid8s      FCB::Open failed: Could not open file G:\tempdb.mdf for file number 1.  OS error: 5(Отказано в доступе.).
    2019-05-18 17:26:43.63 spid8s      Error: 5120, Severity: 16, State: 101.
    2019-05-18 17:26:43.63 spid8s      Unable to open the physical file "G:\tempdb.mdf". Operating system error 5: "5(Отказано в доступе.)".
    2019-05-18 17:26:43.63 spid8s      Error: 1802, Severity: 16, State: 4.
    2019-05-18 17:26:43.63 spid8s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
    2019-05-18 17:26:43.63 spid8s      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.
    

    проблема с tempdb (в корне диска G:\) у вас никуда не делась. Т.к. я не знаю, что это за диск, зачем tempdb создается именно на нем, под какой учетной  записью запускается служба MS SQL Server и, возможно, других фактов о вашей системе, то никаких других рекомендаций дать не могу.

    PS Плюс, в логах я вижу проблему с загрузко


    Слава России!

  • Почеvу-то на сами файлы баз данных права не назначились попробовал еще раз назначить, уже с остановленной службой...

    не удалось, отказано в доступе...как назначить права на файлы? не пойму..


  • Вы можете сделать восстановление дефолтных баз через рекавери.
  • Права не добавились не только на дефолтные базы, но и на все остальные.

    Потому вопрос как добавить NTFS пермишены актуален...

    По поводу восстановления дефолтных баз тоже интересно... 

  • Спасибо!

    Но все же это не решит проблему добавления прав на не системные БД...

    Предполагаю что права не добавляются т.к. файлы баз данных заняты, но в таком случае остановка службы mssql должно их освободить... не пойму, почему остановив службу и попытавшись снова добавить права, снова получаю сообщение о неудаче и занятости файла?

    Как добавить права на файлы всех баз данных?

  • Неужели никто не знает?

    Я уже подумываю что нужно загрузится в безопасном режиме  и попробовать там...

  • Почеvу-то на сами файлы баз данных права не назначились попробовал еще раз назначить, уже с остановленной службой...

    не удалось, отказано в доступе...как назначить права на файлы? не пойму..


    Вам нужно назначать разрешения не на файлы БД tempdb (они всё равно при каждом запуске сервера создаются заново), а на папку, в которой эти файлы создаюся. На всякий случай, если будете давать разрешения скриптом или через расширенный интерфейс (кнопка Дополнительно) уточню: разрешения должны быть наследуемые файлами, то естьс областью действия "Для этой папки, подпапок и файлов".

    А файлы от tempdb, которые у вас остались после остановки службы, можно удалить, чтобы не мешались.

     

    Слава России!

  • Получилось, спасибо за помощь!

    Но теперь имею другую проблему.

    Службы на всех трех серверах в AON запущены под одной  и той же сервисной учеткой, но синхронизация баз в не происходит...

        

  • Вопрос остался актуальным...

    Коллеги есть  ли у кого-то реальные опыт или мануал по переводу MS SQL AON на учетные записи служб ( MSA ) ?

    12 октября 2019 г. 5:42