Log Shipping Monitoring and Troubleshooting 交易紀錄傳送監控與疑難排解

Log Shipping Monitoring and Troubleshooting
交易紀錄傳送監控與疑難排解


1.Log Shipping status
View the Log Shipping Report (SQL Server Management Studio)
https://msdn.microsoft.com/en-us/library/ms181149.aspx

To display the Transaction Log Shipping Status report on a server instance

1. Connect to a monitor server, primary server, or secondary server.
2. Right-click the server instance in Object Explorer, point to Reports, and point to Standard Reports.
3. Click Transaction Log Shipping Status.

sp_help_log_shipping_monitor (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms187820.aspx

sp_help_log_shipping_monitor

Remarks
sp_help_log_shipping_monitor must be run from the master database on the monitor server.

Permissions
Requires membership in the sysadmin fixed server role.

找出Monitoring Server方法

SELECT monitor_server FROM msdb.dbo.log_shipping_primary_databases;
or
SELECT monitor_server FROM msdb.dbo.log_shipping_secondary;

2.Job History

View the Job History
https://msdn.microsoft.com/en-us/library/ms181046.aspx

To view the job history log

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, and then expand Jobs.
3. Right-click a job, and then click View History.
4. In the Log File Viewer, view the job history.
5. To update the job history, click Refresh. To view fewer rows, click the Filter button and enter filter parameters.

or

-- lists all job information for the NightlyBackups job.  
USE msdb ;  
GO  

EXEC dbo.sp_help_jobhistory   
    @job_name = N'LSRestore_ServerName_DBname' ;  
GO

3.Backup and Restore History
Backup History and Header Information (SQL Server)
https://msdn.microsoft.com/en-us/library/ms188653.aspx

Query SQL Server backup history and restore history records
https://blogs.msdn.microsoft.com/bradchen/2014/03/12/query-sql-server-backup-history-and-restore-history-records/

4.ERRORLOG
View the SQL Server Error Log (SQL Server Management Studio)
https://msdn.microsoft.com/en-us/library/ms187109.aspx

1. In Object Explorer, connect to an instance of the SQL Server and then expand that instance.
2. Find and expand the Management section (Assuming you have permissions to see it).
3. Right-click on SQL Server Logs, select View, and choose View SQL Server Log.

5.Event log
Start Event Viewer
https://technet.microsoft.com/en-us/library/cc766401(v=ws.11).aspx

To start Event Viewer by using the Windows interface

1. Click the Start button.
2. Click Control Panel .
3. Click System and Maintenance .
4. Click Administrative Tools .
5. Double-click Event Viewer .

To start Event Viewer by using a command line

1. Open a command prompt. To open a command prompt, click Start , click All Programs , click Accessories and then click Command Prompt .
2. Type eventvwr .

Reference:
Monitor Log Shipping (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms190224.aspx

Stored procedure	Description	Run this procedure on
sp_help_log_shipping_monitor_primary	Returns monitor records for the specified primary database from the log_shipping_monitor_primary table.	Monitor server or primary server
sp_help_log_shipping_monitor_secondary	Returns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.	Monitor server or secondary server
sp_help_log_shipping_alert_job	Returns the job ID of the alert job.	Monitor server, or primary or secondary server if no monitor is defined
sp_help_log_shipping_primary_database	Retrieves primary database settings and displays the values from the log_shipping_primary_databases and log_shipping_monitor_primary tables.	Primary server
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.	Primary server
sp_help_log_shipping_secondary_database	Retrieves secondary-database settings from the log_shipping_secondary, log_shipping_secondary_databasesand log_shipping_monitor_secondary tables.	Secondary server
sp_help_log_shipping_secondary_primary (Transact-SQL)	This stored procedure retrieves the settings for a given primary database on the secondary server.	Secondary server

Bring Database back to norecovery mode from standby mode(Standby / Read-Only)

RESTORE DATABASE [AdventureWorks]
WITH NORECOVERY;

Adding a log shipping monitor
http://www.sqlservercentral.com/articles/Log+Shipping/77295/

File xxx.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see get-help about_signing

File xxx.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see get-help about_signing
from my MSDN blog - October 5, 2016

PS C:\Temp>.\myPS.ps1

C:\Temp\myPS.ps1 檔案無法載入，因為這個系統上已停用指令碼執行。如需詳細資訊，請參閱 "get-help about_signing"。
位於 行:x 字元:xx
+ .\myPS.ps1 <<<<
+ CategoryInfo          : NotSpecified: (:) [], PSSecurityException
+ FullyQualifiedErrorId : RuntimeException

File C:\Temp\myPS.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.


Resolution:
PS C:\Users\Administrator>Set-ExecutionPolicy RemoteSigned


Using the Set-ExecutionPolicy Cmdlet
https://technet.microsoft.com/en-us/library/ee176961.aspx

Restricted - No scripts can be run. Windows PowerShell can be used only in interactive mode.
AllSigned - Only scripts signed by a trusted publisher can be run.
RemoteSigned - Downloaded scripts must be signed by a trusted publisher before they can be run.
Unrestricted - No restrictions; all Windows PowerShell scripts can be run.

Set-ExecutionPolicy
https://technet.microsoft.com/en-us/library/hh849812.aspx

Database Corruption Challenge – Steve Stedman

Database Corruption Challenge – Steve Stedman
from my MSDN blog - September 28, 2016

2016年9月13日 PASS

Database Corruption Challenge - Steve Stedman

Presented by Steve Stedman

https://www.youtube.com/watch?v=5oBA5nL0uRc&feature=youtu.be&a

1.備份資料庫，還原至測試機來進行修復工作(確保還原程序遭遇問題時，可以重頭再來修復一次)

2.如果決定用allow data loss，嘗試先找出可能遺失的資料並事先嘗試SELECT INTO保留下來

3.執行之前尋求是否有其他人的意見

定期執行

osql -E -Q"DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS" -oC:\outputfile.txt

sqlcmd -E -Q"DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS" -o C:\outputfile.txt

PS.WITH NO_INFOMSGS只顯示錯誤訊息，一般的information不顯示

基本流程

1.將有損毀的資料庫備份檔還原到測試機，切換成single_user mode。

ATLER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK_IMMEDIATELY;

2.取得錯誤訊

USE [master];

GO

DBCC CHECKDB (AdventureWorks) WITH NO_INFOMSGS;

GO

DBCC CHECKTABLE ('HumanResources.Employee') WITH NO_INFOMSGS;

GO

2.取回損毀的資料

(1)如果原資料表還能查詢的狀況

A.透過DBCC CHECKTABLE找出哪一筆或哪幾筆資料損毀

page(1, 280) ->page 280

DBCC TRACEON(3604)

DBCC PAGE(dbname, 1, 280,2) with no_infomsgs;

從結果找出m_type

m_type=1 ->data page

slotcount =27 -> number of rows in this page

所以如果執行allow data loss則會出現27然後修復完成(損毀的地方)

B.透過select with non-clustered Index取回一些資料。

select col1, col2 from table with (index=clusted_index1);

利用彙總函數，比對clustered index與non-clustered index資料是否有不同

PS.數值與日期欄位可用SUM(column)，文字欄位可用 SUM(LEN(column))

若查出來值不同(例如col4不同)，表示其中一個欄位有損毀

使用OUTER ALL JOIN 同一個TABLE 找出哪一筆的col4是NULL

因無法直接update or delete那一筆損毀的資料，若執行會出現錯誤

所以從non-cluster index SELECT資料到table_save暫存資料表

D.嘗試修復

DBCC CHECKTABLE(tableName, REPAIR_REBUILD)

通常無法修復

DBCC CHECKTABLE(tableName, REPAIR_ALLOW_DATA_LOSS)

出現找到錯誤，並已經修復

但執行select * from table發現只剩一半的資料，其他消失

E.將資料從table_save補回原資料表

接下就INSERT table select * from table_save

where table.id not in (select id from table)

(2)如果原資料表不能查詢的狀況

A.先透過select with non-clustered Index取回一些資料。(如果後面的步驟無法取得完整資料，最少還有這些)

B.嘗試透過DBCC IND, DBCC PAGE來取回損毀資料表(clustered index)的完整資料

C.再用TRUNCATE TABLE來移除損毀

D.INSERT SELECT from救回資料的資料表

3.修復資料庫並回存資料

truncate table Table_1

insert into Table_1 select * from Table_copy

Add text to beginning or end of each line using SSMS (使用SSMS在每一行前面或後面加上特定字元)

Add text to beginning or end of each line using SSMS
使用SSMS在每一行前面或後面加上特定字元
from my MSDN blog - September 21, 2016

1.Add text to the beginning of each line

Ctrl+H

type "^" in the "Find"

type text you want to add in "Replace with"

Choose the "Regular expressions" checkbox

2.Add text to the end of each line

Ctrl+H

type "$" in the "Find"

type text you want to add in "Replace with"

Choose the "Regular expressions" checkbox

DBA Fundamentals-SQL Server Performance Tuning

DBA Fundamentals-SQL Server Performance Tuning
from my MSDN blog - September 20, 2016

2016/9/21 PASS online event

DBA Fundamentals: SQL Server Performance Tuning

Presented by Pinal Dave

1.資料庫相容性層級影響基數估計(Cardinality Estimation)

提升資料庫相容性層級就可以改善效能

The New and Improved Cardinality Estimator in SQL Server 2014

https://blogs.technet.microsoft.com/dataplatforminsider/2014/03/17/the-new-and-improved-cardinality-estimator-in-sql-server-2014/

Cardinality Estimation (SQL Server)

https://msdn.microsoft.com/en-us/library/dn600374.aspx

CE(Cardinality Estimation)會預測您的查詢可能傳回的資料列數目。 查詢最佳化工具使用基數預測，來產生最佳查詢計劃。 CE 愈精確，查詢計劃通常愈理想。

In 1998, a major update of the CE was part of Microsoft SQL Server 7.0, for which the compatibility level was 70. Subsequent updates came with SQL Server 2014 and SQL Server 2016, meaning compatibility levels 120 and 130. The CE updates for levels 120 and 130 incorporate assumptions and algorithms that work well on modern data warehousing workloads and on OLTP (online transaction processing).

新版(層級 120 和 130 )的基數估計更新了合併假設與演算法，增強並適用於新式資料倉儲工作負載和 OLTP (線上交易處理)。

改善了以下的問題

Your application system could possibly have an important query whose plan is changed to a slower plan due to the new CE. Such a query might be like one of the following:

• An OLTP query that runs so frequently that multiple instance of it often run concurrently.
• A SELECT with substantial aggregation that runs during your OLTP business hours.

Troubleshooting Poor Query Performance: Cardinality Estimation

https://technet.microsoft.com/en-us/library/ms181034(v=sql.105).aspx

2.自動更新與建立統計資訊

Statistics

https://msdn.microsoft.com/en-us/library/ms190397.aspx

3.Missing index (script from Pinal Dave blog)

http://blog.sqlauthority.com

http://go.sqlauthority.com

SQL SERVER – Missing Index Script – Download

4.Wait Stats and Queues (script from Pinal Dave blog)

http://blog.sqlauthority.com

http://go.sqlauthority.com

http://blog.sqlauthority.com/2016/09/17/sql-server-2016-wait-stats-queues-script-updated-identify-resource-bottlenecks/

Distributed query vs OPENQUERY

Distributed query vs OPENQUERY
from my MSDN blog - September 19, 2016

Distributed query (Four-part name) and OPENQUERY

	OpenQuery	Distributed Query
速度	一般來說 OPENQUERY較快一點	也很快
Query optimizer	遠端產生執行計畫	本地產生執行計畫 產生local query與remote query
連線數量	只會產生1條連線到遠端取回資料	會產生2條連線 第1條連線先取回統計資訊 第2條連線再取回資料
優點		可以在一個Query裡面JOIN多個不同SQL Server的Table
可能出現issue		如果太多連線使用Distributed Query，則會造成大量等待SOSHOST_MUTEX wait
缺點		雖然有WHERE條件，但SQL Server可能會送出SELECT * FROM the remote table，然後等資料回到本地端才進行filter
權限	只需要設定SELECT資料表的權限	為了產生最佳執行計畫，remote login account必須有以下權限，才能取得完整的統計資訊，若沒有以下權限，則查詢效能則會比較差 To create the best query plans the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server. https://msdn.microsoft.com/en-us/library/ms175537.aspx 但SQL 2012開始則不需要此權限

Best Performer: Distributed query (Four-part) or OPENQUERY when executing linked server queries in SQL Server
https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/
Security for Linked Servers
http://msdn.microsoft.com/en-us/library/ms175537.aspx
Guidelines for Using Distributed Queries
https://msdn.microsoft.com/en-us/library/ms175129.aspx
OPENQUERY (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188427.aspx
Optimizing Distributed Queries
https://technet.microsoft.com/en-us/library/ms180972(v=sql.105).aspx
Improving the Performance of Distributed Queries
http://sqlmag.com/database-performance-tuning/improving-performance-distributed-queries

Asus external Blu-Ray/DVD external drive not working in Windows 10

Solution from
SOLVED: Asus external Blu-Ray/DVD external drive not working in Windows 10
https://mightbeuseful.wordpress.com/2015/12/05/solved-asus-external-blu-raydvd-external-drive-not-working-in-windows-10/


1.Plug external drive into USB Port.
2.In the Device Manager, find Initio Default Controller and disable it under Intio Combo Device Class
3.Now right-click on it again and click ‘Properties’, in "Driver" tab,  Click on "Uninstall"
4.Unplug the external drive from the USB port.
5.Shut down your computer (be sure to shut it down fully, don’t just restart it).
6.plug in the external drive to the USB ports.
7.Power-on the computer