搜尋本站文章

2017-07-20

[SQL Server] Log Path of Failover Cluster Instance Diagnostics, sys.dm_os_server_diagnostics_log_configurations 容錯移轉叢集診斷記錄檔的存放路徑


使用 sys.dm_os_server_diagnostics_log_configurations,可取得 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑等相關資訊。

以 SQL Server 2016 為例,容錯移轉叢集診斷記錄檔的存放路徑,預設是:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

-- 使用 sys.dm_os_server_diagnostics_log_configurations,查詢 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑
USE master
GO
SELECT is_enabled, path, max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations
GO

-- 01_使用 sys.dm_os_server_diagnostics_log_configurations,查詢 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑




-- 02_檔案總管_ 查詢 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑













參考資料

[SQL Server] ServerProperty('ErrorLogFileName') , Error log file location 錯誤紀錄檔: 目前最新的完整檔案路徑與檔案名稱
http://sharedderrick.blogspot.tw/2017/07/sql-server-serverpropertyerrorlogfilena.html

sys.dm_os_server_diagnostics_log_configurations
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-server-diagnostics-log-configurations

sys.dm_os_server_diagnostics_log_configurations
https://technet.microsoft.com/zh-tw/library/gg471697(v=sql.110).aspx

View and Read Failover Cluster Instance Diagnostics Log
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/view-and-read-failover-cluster-instance-diagnostics-log

[SQL Server] ServerProperty('ErrorLogFileName') , Error log file location 錯誤紀錄檔: 目前最新的完整檔案路徑與檔案名稱


以 SQL Server 2016 為例,錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱,預設:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG

以 SQL Server 2016 為例,錯誤記錄檔的檔案路徑,預設是:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log


使用 ServerProperty('ErrorLogFileName'),可以取得 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱


-- 查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱
USE master
GO
SELECT ServerProperty('ErrorLogFileName') 'Error log file location'
GO


-- 01_ ServerProperty_ErrorLogFileName_SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱




-- 02_檔案總管_ 查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱





使用 xp_readerrorlog,查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱


-- 使用 xp_readerrorlog,查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱
USE master
GO
EXEC xp_readerrorlog 0, 1,N'Logging SQL Server messages in file'
GO

-- 03_使用 xp_readerrorlog,查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱





參考資料

設定SQL Server「錯誤記錄檔(Error log file)」的保存份數

檢視 SQL Server 錯誤記錄檔 (SQL Server Management Studio)

使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server Agent「錯誤記錄檔」

使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server 「錯誤記錄檔」

Identify location of the SQL Server Error Log file

SERVERPROPERTY (Transact-SQL)

2017-07-16

[PowerShell] Get-ClusterLog: Generate Windows cluster log for troubleshooting (產生 Cluster.log 進行錯誤排除)

對 容錯移轉叢集 執行錯誤排除時,將需要分析 Windows cluster log (Cluster.log)。
可以使用 PowerShell 的 Get-ClusterLog 來產生 全部 或是 指定 節點伺服器的系統記錄檔。

本文以 [SQL Server] AlwaysOn, Error 41131, failover failed,容錯移轉 失敗 為例,
逐步帶領各位使用 PowerShell 的 Get-ClusterLog 來產生 Cluster.log,執行 錯誤排除 [NT AUTHORITY\SYSTEM] 帳戶權限不足 的問題。



[PowerShell] Get-ClusterLog: Generate Windows cluster log for troubleshooting


工作01. 使用 [PowerShell] Get-ClusterLog: 產生 Windows cluster log


-- 語法
Get-ClusterLog –Node [SQL Server node name] –TimeSpan 15



-- 範例
Get-ClusterLog –Node AGN02 –TimeSpan 15


-TimeSpan
指定要產生 Cluster.log 的時間間距,以分鐘為單位。
例如:–TimeSpan 15,是指產生最近 15 分鐘內的 Cluster.log。
- Node  指定需要產生 Cluster.log 節點伺服器。
Cluster.log
系統紀錄 Cluster.log 預設是存放在 %WINDIR%\cluster\reports。
例如:C:\Windows\Cluster\Reports。


-- 01_01_PowerShell 產生 Windows cluster log



工作02. 分析 Windows cluster log(Cluster.log)

分析 Cluster.log

搜尋以下文字:
Failed to run diagnostics command.
The user does not have permission to perform this action.

-- 02_檢視Cluster_Log



-- 03_查詢到相關聯的錯誤_The user does not have permission to perform this action.



分析
在 Cluster.log 報告中,可以看到系統紀錄了 權限不足 的問題。
這是因為  [NT AUTHORITY\SYSTEM] 帳戶的權限不足,無法執行 「健全狀況」的偵測,也無法執行 「容錯移轉(Failover) 」。

解決方案

賦予 [NT AUTHORITY\SYSTEM] 帳戶以下權限來解決問題:
  1. Alter Any Availability Group
  2. Connect SQL
  3. View server state
參考



 補充資訊

-- 10_Error_41131



-- 30_SQL_Log



-- 31_發出Failover



-- 32_Error_Reported_02_TO_01




-- 33_RESOLVING_PENDING_FAILOVER



-- 34_state_change



-- 35_Rollback_Failover



-- 36_state_change__SECONDARY_NORMAL



-- 50_SQL_Log



-- 51_發出 Failover





參考文件

[SQL Server] AlwaysOn, Error 41131, failover failed,容錯移轉 失敗
http://sharedderrick.blogspot.tw/2017/07/sql-server-alwayson-error-41131.html

Cannot create a high-availability group in Microsoft SQL Server 2012
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012

Windows Server 2008 R2: Troubleshooting Failover Clusters
https://technet.microsoft.com/en-us/library/hh272674.aspx

Microsoft.FailoverClusters.PowerShell
https://technet.microsoft.com/en-us/library/ee461009.aspx

Get-ClusterLog
https://technet.microsoft.com/itpro/powershell/windows/failoverclusters/get-clusterlog

2017-07-15

[SQL Server] AlwaysOn, Error 41131, failover failed,容錯移轉 失敗

SQL Server AlwaysOn Availability Group 執行容錯移轉時,因故發生失敗,若收到的錯誤代碼是:41131

有可能是 [NT AUTHORITY\SYSTEM] 帳戶的權限不足所導致,可賦予以下權限來解決問題:
  1. Alter Any Availability Group
  2. Connect SQL
  3. View server state
[NT AUTHORITY\SYSTEM]  帳戶是 SQL Server AlwaysOn 「健全狀況」的偵測用帳戶。
若因故權限不足,則將造成:
  • 無法啟動 AlwaysOn 「健全狀況」 
  • 造成 AlwaysOn 可用性群組 無法執行 「容錯移轉(Failover) 」

系統顯示的錯誤訊息是:

訊息 41131,層級 16,狀態 0,行 13
無法讓可用性群組 'AGDBG01' 上線。作業逾時。
請確認本機 Windows Server 容錯移轉叢集 (WSFC) 節點已上線。
然後,確認可用性群組資源存在 WSFC 叢集中。
如果此問題持續發生,您可能需要卸除可用性群組,然後再次建立它。

Msg 41131, Level 16, State 0, Line 2
Failed to bring availability group 'AGDBG01' online.  The operation timed out.
Verify that the local Windows Server Failover Clustering (WSFC) node is online. 
Then verify that the availability group resource exists in the WSFC cluster. 
If the problem persists, you might need to drop the availability group and create it again.

-- 100_Error_41131




-- 101_錯誤訊息_41131



示範版本:
SQL Serve 2012、2014



錯誤
41131 的解決方案:賦予權限

步驟01. 檢查帳戶:[NT AUTHORITY\SYSTEM] 帳戶是否存在。

若不存在,請使用以下語法重建此帳戶

-- 01. 檢查帳戶:NT AUTHORITY\SYSTEM,是否存在。若不存在,請使用以下語法重建此帳戶
-- To create the [NT AUTHORITY\SYSTEM] account

/****** Object:  Login [NT AUTHORITY\SYSTEM] ******/
USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITY\SYSTEM')
BEGIN
 CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
END
GO

-- 01_ 檢查帳戶_NT AUTHORITY_SYSTEM,是否存在



步驟02. 若[NT AUTHORITY\SYSTEM] 帳戶已經存在,查詢其權限

-- 02. 若 [NT AUTHORITY\SYSTEM] 帳戶已經存在,查詢其權限
-- Listing effective permissions of the user
USE [master]
GO
EXECUTE AS LOGIN = N'NT AUTHORITY\SYSTEM';
SELECT 
 permission_name AS [Permission]
FROM fn_my_permissions(NULL, N'SERVER')
ORDER BY permission_name;
REVERT;

-- 02_1_若帳戶 NT AUTHORITY_SYSTEM 已經存在,查詢其權限



只具備兩項權限:
  1. CONNECT SQL
  2. VIEW ANY DATABASE

-- 02_2_缺少ALTER ANY AVAILABILITY GROUP



[NT AUTHORITY\SYSTEM] 帳戶,在 伺服器層級 應該具備的權限有:
  1. Alter Any Availability Group
  2. Connect SQL
  3. View server state

經過比對,缺少 2 項權限,分別是:
  1. Alter Any Availability Group
  2. View server state

步驟03. 授予 [NT AUTHORITY\SYSTEM] 帳戶必要的權限

-- 03_授予 [NT AUTHORITY\SYSTEM] 帳戶必要的權限
-- To grant the permissions to the [NT AUTHORITY\SYSTEM] account
use [master]
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO 

-- 03_授予 [NT AUTHORITY_SYSTEM] 帳戶必要的權限



步驟04. 再度查詢  NT AUTHORITY\SYSTEM 帳戶的權限

-- 04. 再度查詢  NT AUTHORITY\SYSTEM 帳戶的權限
-- Listing effective permissions of the user
USE [master]
GO
EXECUTE AS LOGIN = N'NT AUTHORITY\SYSTEM';
SELECT 
 permission_name AS [Permission]
FROM fn_my_permissions(NULL, N'SERVER')
ORDER BY permission_name;
REVERT;

-- 04_1再度查詢  NT AUTHORITY_SYSTEM 帳戶的權限



-- 04_2_SSMS_已經擁有ALTER ANY AVAILABILITY GROUP





經過比對後,權限已調整為


權限增加為
  1. ALTER ANY AVAILABILITY GROUP
  2. CONNECT SQL
  3. CREATE AVAILABILITY GROUP
  4. VIEW ANY DATABASE
  5. VIEW SERVER STATE
既有權限
  1. CONNECT SQL
  2. VIEW ANY DATABASE
先前賦予的權限
  1. ALTER ANY AVAILABILITY GROUP
  2. VIEW SERVER STATE
系統自動多給一個權限
  1. CREATE AVAILABILITY GROUP




[NT AUTHORITY\SYSTEM]  帳戶 的功能

是 SQL Server AlwaysOn 「健全狀況」的偵測用帳戶。
若因故權限不足,則無法啟動 AlwaysOn 「健全狀況」 ,也將造成 AlwaysOn 可用性群組 無法執行 「容錯移轉(Failover) 」

The [NT AUTHORITY\SYSTEM] account is used by SQL Server AlwaysOn health detection to connect to the SQL Server computer and to monitor health.

When you create an availability group, health detection is initiated when the primary replica in the availability group comes online.
If the [NT AUTHORITY\SYSTEM] account does not exist or does not have sufficient permissions, health detection cannot be initiated, and the availability group cannot come online during the creation process.

Make sure that these permissions exist on each SQL Server computer that could host the primary replica of the availability group.

Note The Resource Host Monitor Service process (RHS.exe) that hosts SQL Resource.dll can be run only under a System account.

The SQL Server Database Engine resource DLL connects to the instance of SQL Server that is hosting the primary replica by using ODBC in order to monitor health.

The logon credentials that are used for this connection are the local SQL Server NT AUTHORITY\SYSTEM login account.
By default, this local login account is granted the following permissions:
Alter Any Availability Group
Connect SQL
View server state

If the NT AUTHORITY\SYSTEM login account lacks any of these permissions on the automatic failover partner (the secondary replica), then SQL Server cannot start health detection when an automatic failover occurs.

Therefore, the secondary replica cannot transition to the primary role.



參考訊息

Cannot create a high-availability group in Microsoft SQL Server 2012
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012

sys.fn_my_permissions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-my-permissions-transact-sql

2017-07-09

[SQL Server] sp_whoisactive: find out long running queries (找出費時、耗資源的查詢)


使用 sp_whoisactive,快速找出費時、耗資源的查詢。

作者: Adam Machanic - Microsoft MVP
http://sqlblog.com/blogs/adam_machanic/default.aspx

示範版本:
sp_whoisactive  Version 11.17 - October 18, 2016 (Box versions 2005-2016 only. NOT for Azure.)

適用環境:
SQL Server 2005 ~ 2016,不適用於 Azure。

若是 Azure SQL Database,請改用此版本:
sp_whoisactive for Azure SQL Database

個人常用的範例

-- EX01_sp_WhoIsActive:增加顯示 執行計畫、交易資訊、封鎖資訊等
USE master
GO
EXEC sp_WhoIsActive @get_plans=1, @get_transaction_info=1, @find_block_leaders=1
GO

-- EX02_sp_WhoIsActive:增加顯示 執行計畫、交易資訊、封鎖資訊、系統資訊等
USE master
GO
EXEC sp_WhoIsActive @get_plans=1, @get_transaction_info=1, @find_block_leaders=1, @get_task_info=2
GO



sp_whoisactive 參數說明


參數 說明
@get_plans = 1
增加顯示:執行計畫
If @get_plans = 1, gets the plan based on the request's statement offset
If @get_plans = 2, gets the entire plan based on the request's plan_handle
@get_locks = 1 
增加顯示:XML 格式的執行計畫
Gets associated locks for each request, aggregated in an XML format
@get_transaction_info=1
增加顯示:交易紀錄檔寫入資訊 與 交易持續時間
Enables pulling transaction log write info and transaction duration
@find_block_leaders = 1
增加顯示:封鎖鏈結、封鎖計數
Walk the blocking chain and count the number of total SPIDs blocked all the way down by a given session
Also enables task_info Level 1, if @get_task_info is set to 0
@get_task_info = 2
增加顯示:active tasks, current wait stats, physical I/O, context switches, blocker, 檢視 CXPACKET wait 等
Get information on active tasks, based on three interest levels
Level 0 does not pull any task-related information
Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
Level 2 pulls all available task-based metrics, including: number of active tasks, current wait stats, physical I/O, context switches, and blocker information




sp_whoisactive 使用範例

範例 1:sp_WhoIsActive


-- 01_sp_WhoIsActive without any parameters
USE master
GO
EXEC sp_WhoIsActive
GO

-- 02_sp_WhoIsActive SPID
EXEC sp_WhoIsActive 64
GO

-- 03_sp_WhoIsActive SPID
EXEC sp_WhoIsActive 66
GO


-- 010_sp_WhoIsActive without any parameters



-- 011_sp_WhoIsActive without any parameters_2



-- 012_sp_WhoIsActive without any parameters_3



-- 013_sp_WhoIsActive without any parameters_4



-- 014_sp_WhoIsActive_SPID



-- 015_sp_WhoIsActive_SPID_2



-- 016_sp_WhoIsActive_SPID_3



-- 017_檢視_sql_text




範例 2:@get_plans:增加顯示 執行計畫


-- sp_WhoIsActive,@get_plans:增加顯示 執行計畫
USE master
GO
EXEC sp_WhoIsActive @get_plans = 1
GO


-- 018_增加顯示:執行計畫_get_plans



-- 019_增加顯示:執行計畫_get_plans_2




範例 3:@get_locks:增加顯示 XML 格式的執行計畫


-- sp_WhoIsActive,@get_locks:增加顯示 XML 格式的執行計畫
USE master
GO
EXEC sp_WhoIsActive @get_locks = 1 
GO

-- 020_增加顯示:執行計畫_get_locks




範例 4:@find_block_leaders:增加顯示 封鎖鏈結、封鎖計數


-- sp_WhoIsActive,@find_block_leaders:增加顯示 封鎖鏈結、封鎖計數
USE master
GO
EXEC sp_WhoIsActive @find_block_leaders = 1 
GO


-- 021_增加顯示封鎖鏈結_封鎖計數_find_block_leaders




範例 5:@get_task_info:增加顯示 active tasks, current wait stats, physical I/O, context switches, blocker, CXPACKET wait

-- sp_WhoIsActive,@get_task_info:增加顯示 active tasks, current wait stats, physical I/O, context switches, blocker, CXPACKET wait
USE master
GO
EXEC sp_WhoIsActive @get_task_info = 2
GO


-- 022_增加顯示active tasks_current wait stats_physical IO_get_task_info






下載與安裝 sp_whoisactive

-- 200_sp_whoisactive_official_website



-- 201_who_is_active_v11_17.zip



-- 202_who_is_active_v11_17.sql



-- 203_Create_sp_WhoIsActive



-- 204_Create_sp_WhoIsActive






參考資料

sp_whoisactive
http://whoisactive.com/

sp_whoisactive: The Big Fixes!
http://sqlblog.com/blogs/adam_machanic/archive/2016/10/18/sp-whoisactive-the-big-fixes.aspx

How to Use sp_WhoIsActive to Find Slow SQL Server Queries
https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

sp_WhoIsActive utility for SQL Server troubleshooting
http://www.sqlserver-dba.com/2015/09/sp_whoisactive-utility-for-sql-server-troubleshooting.html

How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report
http://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html

How to Log Activity Using sp_whoisactive in a Loop
https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

INF: Understanding and resolving SQL Server blocking problems
https://support.microsoft.com/en-us/help/224453/inf-understanding-and-resolving-sql-server-blocking-problems

sp_whoisactive for Azure SQL Database
http://sqlblog.com/blogs/adam_machanic/archive/2016/04/14/sp-whoisactive-for-azure-sql-database-attempt-2.aspx

下載  20170709_sp_WhoIsActive
https://drive.google.com/drive/folders/0B9PQZW3M2F40RU43aFNZUWtJc0k?usp=sharing

2017-07-08

[SQL Server] sp_who 與 sp_who2


sp_who 提供 SQL Server Database Engine 執行個體中有關目前使用者、工作階段和處理序的資訊。
可以篩選資訊,只傳回屬於特定使用者或屬於特定工作階段的非閒置處理序。

sp_who2 是 undocumented 與 unsupported,但提供更多的資訊,例如:CPUTime、DiskIO、LastBatch、ProgramName 等。


-- 01_列出所有目前的處理序
USE master
GO
EXEC sp_who
GO

-- 02_列出特定使用者的處理序
USE master
GO
EXEC sp_who '[loginname]'
GO

-- 03_顯示所有使用中的處理序,排除在等待使用者下一個命令的工作階段。
USE master
GO
EXEC sp_who 'ACTIVE '
GO

-- 04_顯示工作階段識別碼所識別的特定處理序
USE master
GO
EXEC sp_who '[spid]'
GO

-- 90_列出特定使用者的處理序



-- 91_顯示所有使用中的處理序,排除在等待使用者下一個命令的工作階段



-- 100_sp_who



-- 101_sp_who_SPID



-- 102_sp_who2



-- 103_sp_who2_CPU_IO_LastBatch



-- 104_sp_who2_SPID



-- 105_sp_who2_SPID_CPU_IO_LastBatch



-- 106_ 詭異的時間







參考資料

sp_who (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-sql 

sp_who (Transact-SQL)
https://technet.microsoft.com/zh-tw/library/ms174313(v=sql.110).aspx

sp_who2 (or sysprocesses or sys.dm_exec_requests) might show your thread being blocked by SPID -4
https://blogs.msdn.microsoft.com/mangeshd/2009/10/02/sp_who2-or-sysprocesses-or-sys-dm_exec_requests-might-show-your-thread-being-blocked-by-spid-4/

What is difference between SP_WHO & SP_WHO2?
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aa06e533-ef6a-494a-9f9b-df3b29b73d85/what-is-difference-between-spwho-spwho2?forum=transactsql

找出被封鎖的連線; blocked lock connectoin
http://sharedderrick.blogspot.tw/2007/12/blocked-lock-connectoin.html