無法 systemctl 啟動/停止 mssql。mssql-server 卡在腳本升級模式。也無法登錄
我無法讓我的 mssql-server 重新聯機。顯然,它卡在了升級和錯誤的數據庫還原之間,現在卡住了,我嘗試過的任何方法都沒有幫助解決它。
基本上我在做一個
RESTORE DATABASE ···· FROM DISK = '/path/to/backup/·······.bak' WITH replace, move '········' TO '/path/to/data/········.mdf', move '······' TO '/path/to/data/······.ldf' GO
卡住了(
The database '····' is marked RESTORING and is in a state that does not allow recovery to be run
),並且在一些愚蠢的時機,幾分鐘後yum update
發生了,包括mssql-server
升級。在那之後,我被鎖定在伺服器之外,現在我無法做任何事情來進入。如果我輸入
systemctl start/stop mssql-server
命令永遠不會完成,當我完成時
ctrl-C
,systemctl status mssql-server
返回
● mssql-server.service - Microsoft SQL Server Database Engine Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled) Active: deactivating (stop-sigterm) since sat 2019-05-18 20:49:33 MST; 4min 0s ago Docs: https://docs.microsoft.com/en-us/sql/linux Main PID: 5864 (sqlservr) CGroup: /system.slice/mssql-server.service ├─5864 /opt/mssql/bin/sqlservr └─5866 /opt/mssql/bin/sqlservr
顯然,它永遠不會完成停用,並在片刻後
systemctl status
發送● mssql-server.service - Microsoft SQL Server Database Engine Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled) Active: active (running) since sáb 2019-05-18 20:27:35 MST; 17min ago Docs: https://docs.microsoft.com/en-us/sql/linux Main PID: 5864 (sqlservr) CGroup: /system.slice/mssql-server.service ├─5864 /opt/mssql/bin/sqlservr └─5866 /opt/mssql/bin/sqlservr
當我嘗試以管理員身份登錄時,我得到
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Timeout error [258]. . Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process due to delay in login response.
並
/var/opt/mssql/log/errorlog
閱讀Login failed for user '####'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: #######]
當我嘗試以普通使用者身份登錄時,我得到
我已經嘗試過SqlServer 中提出的解決方案處於腳本升級模式,這只是等待(在我的情況下是兩天),無法連接到 ubuntu 上的 mssql 伺服器,因為腳本處於升級模式,這建議輸入
sudo /opt/mssql/bin/mssql-conf traceflag 902 on sudo systemctl start mssql-server
Linux 上的 SQL Server 2017 在更新後處於腳本升級模式,建議鍵入
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true sudo systemctl start mssql-server
並且Linux 上的 SQL Server 在初始啟動時掛起,沒有錯誤,也沒有新的/更新的 ErrorLog 文件,這表明更改所有權權限,
sudo chown -R mssql:mssql /var/opt/mssql sudo systemctl start mssql-server
但到目前為止沒有任何效果。完整
errorlog
閱讀(如果我留下了一些敏感資訊,請幫我刪除)Server Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64) Mar 12 2019 19:29:19 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Linux (CentOS Linux 7 (Core)) Server UTC adjustment: -7:00 Server (c) Microsoft Corporation. Server All rights reserved. Server Server process ID is 32. Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'. Server Registry startup parameters: -d /var/opt/mssql/data/master.mdf -l /var/opt/mssql/data/mastlog.ldf -e /var/opt/mssql/log/errorlog Server SQL Server detected 2 sockets with 6 cores per socket and 12 logical processors per socket, 24 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required. Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. Server Detected 6554 MB of RAM. This is an informational message; no user action is required. Server Using conventional memory in the memory manager. Server Buffer pool extension is already disabled. No action is necessary. Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled. Server Implied authentication manager initialization failed. Implied authentication will be disabled. Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ###############################################################. Server The maximum number of dedicated administrator connections for this instance is '1' Server Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 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. Server Lock partitioning is enabled. This is an informational message only. No user action is required. Server In-Memory OLTP initialized on lowend machine. 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. Server Query Store settings initialized with enabled = 1, Server Software Usage Metrics is disabled. spid6s Starting up database 'master'. spid6s 12 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required. spid6s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required. spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required. spid6s SQL Server Audit is starting the audits. This is an informational message. No user action is required. spid6s SQL Server Audit has started the audits. This is an informational message. No user action is required. spid6s SQL Trace ID 1 was started by login "sa". spid6s Server name is '###'. This is an informational message only. No user action is required. spid23s Always On: The availability replica manager is starting. This is an informational message only. No user action is required. spid21s Starting up database '####'. spid22s Starting up database 'msdb'. spid9s Starting up database 'mssqlsystemresource'. spid23s 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. spid9s The resource database build version is 14.00.3076. This is an informational message only. No user action is required. spid9s Starting up database 'model'. spid21s The database '####' is marked RESTORING and is in a state that does not allow recovery to be run. spid22s 20 transactions rolled forward in database 'msdb' (4:0). This is an informational message only. No user action is required. spid9s Polybase feature disabled. spid22s 0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required. spid9s Clearing tempdb database. spid22s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required. spid18s A self-generated certificate was successfully loaded for encryption. spid18s Server is listening on [ 'any' <ipv6> ####]. spid18s Server is listening on [ 'any' <ipv4> ####]. spid18s Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required. 2019-05-18 20:27:39.32 Server Failed to verify the Authenticode signature of 'C:\binn\secforwarder.dll'. Signature verification of SQL Server DLLs will be skipped. Genuine copies of SQL Server are signed. Failure to verify the Authenticode signature might indicate that this is not an authentic release of SQL Server. Install a genuine copy of SQL Server or contact customer support. spid9s Starting up database 'tempdb'. spid21s The Service Broker endpoint is in disabled or stopped state. spid21s The Database Mirroring endpoint is in disabled or stopped state. spid21s Service Broker manager has started. spid6s Database 'master' is upgrading script 'msdb110_upgrade.sql' from level 234884072 to level 234884100. spid6s ---------------------------------- spid6s Starting execution of PRE_MSDB.SQL spid6s ---------------------------------- spid6s Setting database option COMPATIBILITY_LEVEL to 100 for database 'msdb'. spid6s ----------------------------------------- spid6s Starting execution of PRE_SQLAGENT100.SQL spid6s ----------------------------------------- spid6s Setting database option COMPATIBILITY_LEVEL to 140 for database 'msdb'. spid6s Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install. spid6s Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install. spid6s Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required. spid6s Using 'xpstar.dll' version '2017.140.3076' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required. spid6s DBCC TRACEOFF 1717, server process ID (SPID) 6. This is an informational message only; no user action is required. spid6s DBCC execution completed. If DBCC printed error messages, contact your system administrator. spid6s spid6s Creating table temp_sysjobschedules spid6s spid6s Alter table sysdownloadlist... spid6s spid6s Alter table sysjobhistory... spid6s spid6s Alter table systargetservers... spid6s spid6s Alter table sysjobsteps... spid6s Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install. spid6s Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install. spid6s spid6s ----------------------------------------- spid6s Execution of PRE_SQLAGENT100.SQL complete spid6s ----------------------------------------- spid6s DMF pre-upgrade steps... spid6s DC pre-upgrade steps... spid6s Check if Data collector config table exists... spid6s Data Collector state before upgrade: 0 spid6s pre_dc100::Check if syscollector_collection_sets_internal table exists... spid6s pre_dc100::Capturing Collection set status in temp table... spid6s Deleting cached auto-generated T-SQL Data Collection packages from msdb... spid6s End of DC pre-upgrade steps. spid6s DAC pre-upgrade steps... spid6s Starting DAC pre-upgrade steps ... spid6s End of DAC pre-upgrade steps. spid6s ---------------------------------- spid6s Starting execution of MSDB.SQL spid6s ---------------------------------- spid6s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install. spid6s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install. spid6s Checking the size of MSDB... spid6s spid6s Setting database option TRUSTWORTHY to ON for database 'msdb'. spid6s Setting database option RECOVERY to SIMPLE for database 'msdb'. spid6s ---------------------------------- spid6s Finished execution of MSDB.SQL spid6s ---------------------------------- spid6s ----------------------------------------- spid6s Starting execution of MSDB_VERSIONING.SQL spid6s ----------------------------------------- spid6s ----------------------------------------- spid6s Finished execution of MSDB_VERSIONING.SQL spid6s ----------------------------------------- spid6s spid6s Creating procedure sp_agent_add_job... spid6s spid6s Creating procedure sp_agent_delete_job... spid6s spid6s Creating procedure sp_agent_add_jobstep... spid6s spid6s Creating procedure sp_agent_start_job... spid6s spid6s Creating procedure sp_agent_get_jobstep... spid6s spid6s Creating procedure sp_agent_log_job_history... spid6s spid6s Creating procedure sp_agent_write_sysjobstep_log... spid6s spid6s Creating table syssubsystems... spid6s spid6s Creating view sysproxyloginsubsystem_view... spid6s spid6s Creating trigger trig_sysoriginatingservers_delete... spid6s spid6s Creating view sysoriginatingservers_view... spid6s spid6s Creating trigger trig_sysjobs_insert_update... spid6s spid6s Creating view sysjobs_view... spid6s spid6s Creating trigger trig_sysschedules_insert_update... spid6s spid6s Creating view sysschedules_localserver_view... spid6s spid6s Creating view systargetservers_view... spid6s spid6s Creating view sysalerts_performance_counters_view... spid6s spid6s Creating function SQLAGENT_SUSER_SNAME ... spid6s spid6s Creating function SQLAGENT_SUSER_SID ... spid6s spid6s Creating procedure SP_SQLAGENT_IS_SRVROLEMEMBER... spid6s spid6s Creating procedure sp_get_traceflag_status_internal... spid6s spid6s Creating procedure sp_verify_category_identifiers... spid6s spid6s Creating function agent_datetime... spid6s spid6s Creating procedure sp_verify_proxy_identifiers... spid6s spid6s Creating procedure sp_verify_credential_identifiers... spid6s spid6s Creating procedure sp_verify_subsystems... spid6s spid6s Creating procedure sp_verify_subsystem_identifiers... spid6s spid6s Creating procedure sp_verify_login_identifiers... spid6s spid6s Creating procedure sp_verify_proxy... spid6s spid6s Creating procedure sp_add_proxy... spid6s spid6s Creating procedure sp_delete_proxy... spid6s spid6s Creating procedure sp_update_proxy... spid6s spid6s Creating procedure sp_sqlagent_is_member... spid6s spid6s Creating procedure sp_verify_proxy_permissions... spid6s spid6s Creating procedure sp_help_proxy... spid6s spid6s Creating procedure sp_get_proxy_properties... spid6s spid6s Creating procedure sp_grant_proxy_to_subsystem... spid6s spid6s Creating procedure sp_grant_login_to_proxy... spid6s spid6s Creating procedure sp_revoke_login_from_proxy... spid6s spid6s Creating procedure sp_revoke_proxy_from_subsystem... spid6s spid6s Creating procedure sp_enum_proxy_for_subsystem... spid6s spid6s Creating procedure sp_enum_login_for_proxy... spid6s spid6s Creating procedure sp_reassign_proxy... spid6s spid6s Creating procedure sp_sqlagent_get_startup_info... spid6s spid6s Creating procedure sp_sqlagent_update_agent_xps... spid6s spid6s Creating procedure sp_sqlagent_has_server_access... spid6s spid6s Creating procedure sp_sqlagent_get_perf_counters... spid6s spid6s Creating procedure sp_sqlagent_notify... spid6s spid6s Creating procedure sp_is_sqlagent_starting... spid6s spid6s Creating procedure sp_verify_job_identifiers... spid6s spid6s Creating procedure sp_verify_schedule_identifiers... spid6s spid6s Creating procedure sp_verify_jobproc_caller... spid6s spid6s Creating procedure sp_downloaded_row_limiter... spid6s spid6s Creating procedure sp_post_msx_operation... spid6s spid6s Creating procedure sp_verify_performance_condition... spid6s spid6s Creating procedure sp_verify_job_date... spid6s spid6s Creating procedure sp_verify_job_time... spid6s spid6s Creating procedure sp_verify_alert... spid6s spid6s Creating procedure sp_update_alert... spid6s spid6s Creating procedure sp_delete_job_references... spid6s spid6s Creating procedure sp_delete_all_msx_jobs... spid6s spid6s Creating procedure sp_generate_target_server_job_assignment_sql... spid6s spid6s Creating procedure sp_generate_server_description... spid6s spid6s Creating procedure sp_msx_set_account... spid6s spid6s Creating procedure sp_msx_get_account... spid6s spid6s Creating procedure sp_delete_operator... spid6s spid6s Creating procedure sp_msx_defect... spid6s spid6s Creating procedure sp_msx_enlist... spid6s spid6s Creating procedure sp_delete_targetserver... spid6s spid6s Creating procedure sp_enlist_tsx spid6s spid6s Creating procedure sp_get_sqlagent_properties... spid6s spid6s Create procedure sp_set_sqlagent_properties... spid6s spid6s Creating procedure sp_add_targetservergroup... spid6s spid6s Creating procedure sp_update_targetservergroup... spid6s spid6s Creating procedure sp_delete_targetservergroup... spid6s spid6s Creating procedure sp_help_targetservergroup... spid6s spid6s Creating procedure sp_add_targetsvgrp_member... spid6s spid6s Creating procedure sp_delete_targetsvrgrp_member... spid6s spid6s Creating procedure sp_verify_category... spid6s spid6s Creating procedure sp_add_category... spid6s spid6s Creating procedure sp_update_category... spid6s spid6s Creating procedure sp_delete_category... spid6s spid6s Creating procedure sp_help_category... spid6s spid6s Creating procedure sp_help_targetserver... spid6s spid6s Creating procedure sp_resync_targetserver... spid6s spid6s Creating procedure sp_purge_jobhistory... spid6s spid6s Creating procedure sp_help_jobhistory... spid6s spid6s Creating procedure sp_add_jobserver... spid6s spid6s Creating procedure sp_delete_jobserver... spid6s spid6s Creating procedure sp_help_jobserver... spid6s spid6s Creating procedure sp_help_downloadlist... spid6s spid6s Creating procedure sp_enum_sqlagent_subsystems_internal... spid6s spid6s Creating procedure sp_enum_sqlagent_subsystems... spid6s spid6s Creating procedure sp_verify_subsystem... spid6s spid6s Creating procedure sp_verify_schedule... spid6s spid6s Creating procedure sp_add_schedule... spid6s spid6s Creating procedure sp_attach_schedule ... spid6s spid6s Creating procedure sp_detach_schedule ... spid6s spid6s Creating procedure sp_update_replication_job_parameter... spid6s spid6s Creating procedure sp_update_schedule ... spid6s spid6s Creating procedure sp_delete_schedule ... spid6s spid6s Creating procedure sp_get_jobstep_db_username... spid6s spid6s Creating procedure sp_verify_jobstep... spid6s spid6s Creating procedure sp_add_jobstep_internal... spid6s spid6s Creating procedure sp_add_jobstep... spid6s spid6s Creating procedure sp_update_jobstep... spid6s spid6s Creating procedure sp_delete_jobstep... spid6s spid6s Creating procedure sp_help_jobstep... spid6s spid6s Creating procedure sp_write_sysjobstep_log... spid6s spid6s Creating procedure sp_help_jobsteplog... spid6s spid6s Creating procedure sp_delete_jobsteplog... spid6s spid6s Creating procedure sp_get_schedule_description... spid6s spid6s Creating procedure sp_add_jobschedule... spid6s spid6s Creating procedure sp_update_jobschedule... spid6s spid6s Creating procedure sp_delete_jobschedule... spid6s spid6s Creating procedure sp_help_schedule... spid6s spid6s Creating procedure sp_help_jobschedule... spid6s spid6s Creating procedure sp_verify_job... spid6s spid6s Creating procedure sp_add_job... spid6s spid6s Creating procedure sp_update_job... spid6s spid6s Creating procedure sp_delete_job... spid6s spid6s Creating procedure sp_get_composite_job_info... spid6s spid6s Creating procedure sp_help_job... spid6s spid6s Creating procedure sp_help_jobcount... spid6s spid6s Creating procedure sp_help_jobs_in_schedule... spid6s spid6s Creating procedure sp_manage_jobs_by_login... spid6s spid6s Creating procedure sp_apply_job_to_targets... spid6s spid6s Creating procedure sp_remove_job_from_targets... spid6s spid6s Creating procedure sp_get_job_alerts... spid6s spid6s Creating procedure sp_start_job... spid6s spid6s Creating procedure sp_stop_job... spid6s spid6s Creating procedure sp_cycle_agent_errorlog... spid6s spid6s Creating procedure sp_get_chunked_jobstep_params... spid6s spid6s Creating procedure sp_check_for_owned_jobs... spid6s spid6s Creating procedure sp_check_for_owned_jobsteps... spid6s spid6s Creating procedure sp_sqlagent_refresh_job... spid6s spid6s Creating procedure sp_jobhistory_row_limiter... spid6s spid6s Creating procedure [dbo].[sp_sqlagent_set_job_completion_state]... spid6s spid6s Creating procedure [dbo].[sp_sqlagent_set_jobstep_completion_state]... spid6s spid6s Creating procedure [dbo].[sp_sqlagent_create_jobactivity]... spid6s spid6s Creating procedure [dbo].[sp_sqlagent_update_jobactivity_next_scheduled_date]... spid6s spid6s Creating procedure [dbo].[sp_sqlagent_update_jobactivity_requested_date]... spid6s spid6s Creating procedure [dbo].[sp_sqlagent_update_jobactivity_queued_date]... spid6s spid6s Creating procedure [dbo].[sp_sqlagent_update_jobactivity_start_execution_date]... spid6s spid6s Creating procedure sp_sqlagent_log_jobhistory... spid6s spid6s Creating procedure sp_sqlagent_check_msx_version... spid6s spid6s Creating procedure sp_sqlagent_probe_msx... spid6s spid6s Creating procedure sp_set_local_time... spid6s spid6s Creating procedure sp_multi_server_job_summary... spid6s spid6s Creating procedure sp_target_server_summary... spid6s spid6s Creating [legacy] procedure sp_uniquetaskname... spid6s spid6s Creating [legacy] procedure sp_addtask... spid6s spid6s Creating [legacy] procedure sp_droptask... spid6s spid6s Creating trigger trig_targetserver_insert... spid6s spid6s Creating procedure sp_add_alert_internal... spid6s spid6s Creating procedure sp_add_alert... spid6s spid6s Creating procedure sp_delete_alert... spid6s spid6s Creating procedure sp_help_alert... spid6s spid6s Creating procedure sp_verify_operator... spid6s spid6s Creating procedure sp_add_operator... spid6s spid6s Creating procedure sp_update_operator... spid6s spid6s Creating procedure sp_help_operator... spid6s spid6s Creating procedure sp_help_operator_jobs... spid6s spid6s Creating procedure sp_verify_operator_identifiers... spid6s spid6s Creating procedure sp_notify_operator... spid6s spid6s Creating procedure sp_verify_notification... spid6s spid6s Creating procedure sp_add_notification... spid6s spid6s Creating procedure sp_update_notification... spid6s spid6s Creating procedure sp_delete_notification... Logon Error: 18401, Severity: 14, State: 1. Logon Login failed for user '#####'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: ########]
在第 45 行
errorlog
,它說spid21s The database '####' is marked RESTORING and is in a state that does not allow recovery to be run.
而且我不能前進也不能後退。我什至嘗試重新安裝 mssql-server 但它也卡住了
Running Transactions
。有什麼建議嗎?
編輯
我認為這與安全有關。現在
systemctl stop mssql-server
可以工作,但需要很長時間,而且我無法遠端登錄。出現以下錯誤Logon SSPI handshake failed with error code 0x80090308, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The token supplied to the function is invalid [CLIENT: ######] Logon Error: 18452, Severity: 14, State: 1. Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: #######] spid51 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required. Using 'xplog70.dll' version '2017.140.3076' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
遺憾的是,我一直無法找到有關如何修復此錯誤的相關資訊。
我遇到了類似的問題,甚至無法連接。原來有兩個不同的版本流(https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-change-repo?view=sql-server-2017&pivots=ld2-ubuntu#cuversusgdr ):
- 累積更新 (CU):累積更新 (CU) 儲存庫包含基本 SQL Server 版本的包以及自該版本以來的任何錯誤修復或改進。累積更新特定於發布版本,例如 SQL Server 2017。它們定期發布。
- GDR:GDR 儲存庫包含基本 SQL Server 版本的包以及自該版本以來的關鍵修復和安全更新。這些更新也會添加到下一個 CU 版本中。
看起來在 prod 中使用 GDR 更穩定。“降級”至少解決了我所有的問題。
這個命令幫助了我,然後我終於可以在 Red Hat 8 中登錄 SQL Server。
sudo /opt/mssql/bin/mssql-conf traceflag 902 on sudo systemctl start mssql-server
來自此Microsoft 技術社區頁面:
如果在安裝 SQL Server 更新檔後由於升級後腳本失敗而無法啟動 SQL Server 服務,則 TF902 作為一種快速解決方法來啟動您的 SQL Server 服務。本文後面部分討論了解決升級後腳本故障的進一步故障排除步驟。
請注意,由於您的 SQL Server 處於未完成更新檔安裝狀態,並且被認為對您的生產環境不穩定,因此不建議您將 SQL Server 服務長時間保持在此狀態。