Frequently Asked Questions for PSSDiag Collection (PSSDiag收集常見問題)

Frequently Asked Questions for PSSDiag Collection
(PSSDiag收集常見問題)

Windows Server Performance Tuning Guidelines

Windows Server Performance Tuning Guidelines

OS	Documents
Windows 2016	Performance Tuning Guidelines for Windows Server 2016 https://docs.microsoft.com/en-us/windows-server/administration/performance-tuning/
Windows 2012 R2	Performance Tuning Guidelines for Windows Server 2012 R2 https://www.microsoft.com/en-us/download/details.aspx?id=51960Performance Tuning Guidelines for Windows Server 2012 R2https://www.microsoft.com/en-us/download/details.aspx?id=51960Performance Tuning https://msdn.microsoft.com/en-us/library/windows/hardware/dn529133
Windows 2012	Performance Tuning Guidelines for Windows Server 2012 http://download.microsoft.com/download/0/0/B/00BE76AF-D340-4759-8ECD-C80BC53B6231/performance-tuning-guidelines-windows-server-2012.docx
Windows 2008 R2	Performance Tuning Guidelines for Windows Server 2008 R2 http://download.microsoft.com/download/6/B/2/6B2EBD3A-302E-4553-AC00-9885BBF31E21/Perf-tun-srv-R2.docx
Windows 2008	Performance Tuning Guidelines for Windows Server 2008 http://download.microsoft.com/download/9/c/5/9c5b2167-8017-4bae-9fde-d599bac8184a/Perf-tun-srv.docx

Performance Tuning Guidelines for previous versions of Windows Server
https://msdn.microsoft.com/en-us/library/windows/hardware/dn529134

The distribution agent failed to create temporary files in xxx directory. System returned errorcode 5 散發代理程式建立暫存檔失敗

The distribution agent failed to create temporary files in xxx directory. System returned errorcode 5

散發代理程式建立暫存檔失敗

Error: 錯誤訊息

當你修改了散發代理程式的處理帳號(執行帳號)

When you change the Agent process account in the Distribution Agent Security 

The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\110\COM' directory. System returned errorcode 5

Cause: 原因

新的執行帳號，對C:\Program Files\Microsoft SQL Server\110\COM 目錄沒有權限

Resolution: 解決方法

設定新的執行帳號對 C:\Program Files\Microsoft SQL Server\110\COM 目錄有寫入的權限

Reference: 參考

Error message when you run the Distribution Agent in SQL Server 2008 or in SQL Server 2008 R2: "The distribution agent failed to create temporary files"
https://support.microsoft.com/en-us/help/956032/error-message-when-you-run-the-distribution-agent-in-sql-server-2008-o

Replication Log Reader Agent Error “The principal dbo does not exist” 複寫記錄讀取器代理程式出現主體dbo不存在

Replication Log Reader Agent Error “The principal dbo does not exist”
複寫記錄讀取器代理程式出現主體dbo不存在

設定SQL Server Replication複寫時，Log Reader Agent複寫記錄讀取器代理程式出現以下錯誤

Error: 錯誤訊息

Error messages:

• The process could not execute 'sp_replcmds' on 'SQLPublisher'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
  Get help: http://help/MSSQL_REPL20011
• Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
  Get help: http://help/15517
• The process could not execute 'sp_replcmds' on 'SQLPublisher'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
  Get help: http://help/MSSQL_REPL22037

Cause: 原因

Publication database's owner does not exist

發行資料庫沒有設定擁有者

PS.通常發生在，當發行資料庫是從備份檔還原，而資料庫的建立者無法在還原的資料庫找到對應時，還原後就會是空白

Resolution: 解決方法

發行資料庫設定擁有者

1.right click on the database that you want to change the owner and go to “Properties” menu.
2.In the properties window, go to menu “Select a page -> Files”. Search or type the new owner.

Replication Log Reader Agent back to normal 恢復正常

Guideline for SQL Server configuration, installation and database creation

Guideline for SQL Server configuration, installation and database creation

SQL Server Reporting Services failed to start (Reporting Services啟動失敗)

SQL Server Reporting Services failed to start
(Reporting Services啟動失敗)

Problem 問題:

The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:

The service did not respond to the start or control request in a timely fashion.

Resolution 解決方法:

有三種解決方法，以下是我常用的一種

To increase the default service time-out, follow these steps:

1. 1. Click Start, click Run, type regedit in the Open box, and then click OK.
   2. Locate and then select the following registry subkey:  
   3. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
   4. Right-click Control, point to New, and then click DWORD.
   5. In the New Value box, type ServicesPipeTimeout, and then press Enter.
   6. Right-click ServicesPipeTimeout, and then click Modify.
   7. Click Decimal, type the number of milliseconds that you want to wait until the service times out, and then click OK.
   8. For example, to wait 60 seconds before the service times out, type 60000.
   9. On the File menu, click Exit, and then restart the computer.

其他解決方法請參考以下連結文件

You cannot start SQL Server Reporting Services after you apply the update that is discussed in KB 2677070

https://support.microsoft.com/en-us/help/2745448/you-cannot-start-sql-server-reporting-services-after-you-apply-the-upd

Reporting Services service doesn’t start after the installation of MS12-070 security patch

https://blogs.msdn.microsoft.com/mariae/2012/11/12/reporting-services-service-doesnt-start-after-the-installation-of-ms12-070-security-patch/

Cluster service verification failed 叢集服務驗證失敗

Cluster service verification failed
叢集服務驗證失敗

Cause:

如果在Windows 2012 R2 failover cluster上安裝SQL Server 2008 R2 cluster將會遇到此錯誤

Rule "Cluster service verification" failed.

Resolution: 解決方法

事先啟用 Failover Cluster Automation Server功能

Using SQL Server in Windows 8 and later versions of Windows operating system

https://support.microsoft.com/en-us/kb/2681562

To resolve this issue, do one of the following:

• Enable the Failover Cluster Automation Server feature on each node by using Server Manager. In Server Manager, expand Remote Server Administration Tools, expand Feature Administration Tools, expand Failover Clustering Tools, and then click to select Failover Cluster Automation Server. (The screen shot for this step follows.)