SQLDiag工具程式的使用

SQLDiag工具程式的使用

 

前言:SQLDiag是SQL Server內建的診斷收集共用程式,

[SQL Server 2005]
預設位置:C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLDiag.exe
你可以在任何路徑下執行SQLDiag因為此路徑已被加到path系統變數,執行sqldiag -?或sqldiag /?即可顯示參數說明

SQL 2005的sqldiag搭配參數請使用正斜線"/"

SQLdiag 
可以收集下列類型的診斷資訊：
Windows 效能記錄
Windows 事件記錄檔
SQL Server Profiler 追蹤
SQL Server 封鎖資訊
SQL Server 組態資訊

http://technet.microsoft.com/zh-tw/library/ms162833.aspx

一般使用:
例如: 
sqldiag /O C:\temp\sqldiag
(/O 指定輸出檔案的目錄)
最下方會出現
2008/07/01 00:34:42.82 SQLDIAG 
Collection started. Press Ctrl+C to stop.表示正在收集中
在此時按下Ctrl+C即可停止收集 收集的資訊會儲存在指定的C:\temp\sqldiag\目錄下,其中數個log_xx.trc檔是從C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\路徑下所複製的

進階收集: 修改並指定xml檔,來收集Performance Counter的資訊

[SQL Server 2000]
預設位置:C:\Program Files\Microsoft SQL Server\MSSQL\Binn\SQLDiag.exe
切換目錄到此位置後執行sqldiag -?或sqldiag /?即可顯示參數說明
SQL 2000的sqldiag搭配參數執行要使用"-"
例如: sqldiag -E -O c:\temp\sqldiag.log
(-O 指定輸出資訊到指定的檔案裡)

若要讓SQL 2000可以取得SQL Trace(*.trc)資料,可以利用下面[Sample Code]或KB281671文件範例SQL在master資料庫新增一個名為trace_blackbox的Stored Procedure
KB281671 INF： 預存程序來建立一個 SQL Server 2000 的 blackbox 追蹤
然後在排定時間執行trace_blackbox 1以啟動trace_blackbox,執行期間這個Instance所有連線的SQL Command都會記錄到C:\Program Files\Microsoft SQL Server\MSSQL\Data\blackbox開頭.trc,在必要的時間執行trace_blackbox 0以關閉trace_blackbox,而這些trc檔案都將會被sqldiag所複製並更名到指定的位置

[Caution] 
若執行SQLDiag當時trace_blackbox還開著,檔案將無法複製所以SQLDiag.trc會是空的

[Samlp Code]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trace_blackbox]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[trace_blackbox]

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE    PROCEDURE trace_blackbox @on int = 2 AS
/* If no argument is passed to the @on parameter then get the current blackbox trace status.
  If @on is zero then stop and delete the blackbox trace.
  If @on is one then create and start the blackbox trace.
*/
declare @traceid int, @blackboxstatus int, @dir nvarchar(80)
set @traceid = 0
set @blackboxstatus = 0
set nocount on
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0)
where property = 1 and value = 8
IF @on = 0 and @traceid > 0
begin
 select @blackboxstatus = cast(value as int) FROM :: fn_trace_getinfo(0)
  where traceid = @traceid and property = 5
 IF @blackboxstatus > 0 exec sp_trace_setstatus @traceid,0 --stop blackbox trace
 exec sp_trace_setstatus @traceid,2 --delete blackbox trace definition
end
IF @on = 1
  begin
   IF @traceid < traceid =" 0" blackboxstatus =" 0" traceid =" traceid" property =" 1" value =" 8" blackboxstatus =" cast(value" traceid =" @traceid" property =" 5"> 0 and @blackboxstatus > 0
  begin
   select @dir = cast(value as nvarchar(80)) FROM :: fn_trace_getinfo(0)
    where traceid = @traceid and property = 2
   select 'The blackbox trace is running and the trace file is in the following directory.'
   select @dir + '.trc'
  end
ELSE select 'The blackbox trace is not running.'
set nocount off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

 參考資料:
MSDN Library - SQL Server 2005 - SQLdiag 公用程式(繁體中文)
TechNet- SQL Server 2005 - SQLdiag 公用程式(繁體中文)MSDN Library - SQL Server 2000- sqldiag Utility (英文)

SQLDiag工具程式Bug-Buffer overrun detected

SQLDiag工具程式Bug-Buffer overrun detected

 

當SQL Server 2000升級到SP4之後,SQLDiag.exe工具已被更新到SP4的版本,此版本有個Bug,執行後會出現以下的錯誤訊息:

解決方法1
微軟的KB902955-此Bug已有hotfix,若要取得此hotfix需連絡Microsoft產品支援服務
FIX： 當您執行 Sqldiag.exe 公用程式安裝 SQL Server 2000 SP 4 之後， 您收到 「 取得登錄資訊 」 訊息
解決方法2
將SQLDiag.exe更名,然後從SQL Server 2000安裝光碟將原始的SQLDiag.exe複製出來使用

立即中斷所有SQL Server連線

1.針對單一資料庫的連線

範例:將Northwind資料庫設定為只能有一個連線,並中斷其他連線
--中斷Northwind資料庫的所有連線
USE master
GO
ALTER DATABASE [Northwind]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--復原為一般多人連線
USE master
GO
ALTER DATABASE [Northwind]
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;
GO

2.針對所有SQL Server連線
(1)查出現有連線,一一刪除
EXEC sp_who

KILL spid號碼


(2)使用T-SQL 的SURSOR取出所有非系統spid,一次全部刪除

USE master
GO
DECLARE @spid_number int
DECLARE @sql_text varchar(100)

DECLARE CUR CURSOR FOR
SELECT [spid] FROM master..sysprocesses
WHERE [spid] > 50 and [spid] <> @@spid

OPEN CUR
FETCH CUR INTO @spid_number

WHILE (@@FETCH_STATUS=0)
BEGIN

SET @sql_text = 'KILL ' + CAST(@spid_number as varchar)

EXEC(@sql_text)

FETCH CUR INTO @spid_number
END

CLOSE CUR
DEALLOCATE CUR
GO

-- Update on 2009-01-18
3.最快速的方式
(1)SQL Server 2005 - 設定資料庫為單一使用者連線
使用SSMS,針對要進行清除連線的資料庫,
進入[屬性]設定的頁面


在左邊點選[選項],再到右下方限制存取項目修改為SINGLE_USER,
再按確定


跳出此視窗,按一下[是],即可清除對此資料庫的所有連線


此時資料庫狀態已變成(單一使用者)


或直接用T-SQL

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH NO_WAIT
GO
ALTER DATABASE [AdventureWorks] SET SINGLE_USER
GO

(2)SQL Server 2000 - 利用卸離Detach資料庫工作
針對要進行清除連線的資料庫,執行卸離資料庫工作


點一下[清除]按鈕,即可清除所有對此資料庫的連線,
再按一下取消,來取消卸離detach作業

Linux Shell Script(初階)

# ===== 編輯軟體 ===================================================
1.使用Linux的vi文書編輯軟體
建議使用vi直接新增script檔,之後可以用notepad或其他文書編輯軟體編輯

2.可直接使用Windows的notepad文書編輯軟體

3.若改來改去,後來無法執行,可能是Return Method的問題
可用文字編輯軟體EmEditor將檔案另存
並修改Return Method:將CR+LF(Windows)改成LF Only(UNIX)
Encoding可以維持System Default(950,big5),如此才能在正常在Linux上執行

# ===== 排程執行 ===================================================
可在crontab排程執行shell script


# ===== 一般debug或logging用 ==========================================
顯示在Console
echo "test!!!"

寫入test.log文字檔
echo "test!!!" > /tmp/test.log

取得當下日期與時間寫入文字檔
echo "NowDate_"`date '+%Y-%m-%d %H:%M'` > /tmp/test.log
寫入到test.log文字檔結果是 NowDate_2008-12-05 15:30


# ==== 使用變數 ====================================================
backupFilefolder='2008-06-25'
TARFILE='/media/cdrom/'$backupFilefolder'/'$backupFilefolder'_data.tar.gz'

echo $TARFILE
輸出到console的結果是/media/cdrom/2008-06-25/2008-06-25_data.tar.gz

tar -zxvf $TARFILE
也就是執行tar -zxvf /media/cdrom/2008-06-25/2008-06-25_data.tar.gz


# ==== 判斷式 ======================================================
if [ -f /etc/init.d/mysql ]; then
/etc/init.d/mysql stop
else
echo "could not find /etc/init.d/mysql script, exit..."
exit 1
fi

=================================================================
Linux Shell Script(中階)
用日期當作變數
發送mail
....

VMware Tool

在Lunux Guset 安裝VMware Tool

安裝完成後必須執行 /usr/bin/vmware-config-tools.pl才算設定完成

VMware 的Linux Guest系統網卡無法啟動

VMware 的Linux Guest系統網卡無法啟動

覆製後的VM若是網卡出問題,最常用的方法是把網卡移除後在偵測一次,
若抓到網卡後要進入設定則會出現自動帶出的Network Device 的名稱,此名稱會非常長,
請將此名稱改為ech0

VMware 的Linux Guest系統時鐘越來越慢或越來越快

VMware 的KB
Clock in a Linux Guest Runs More Slowly or Quickly Than Real Time



時鐘相關的命令

取得系統時間 date
設定系統時間 date MMDDhhmmYYYY
取得BIOS硬體時間 hwclock -r
將BIOS時間寫入系統時間 hwclock -w

將BIOS時間寫入系統時間 hwclock --hctosys
將系統時間寫入BIOS時間 hwclock --systohc

vmware 設定檔(XXX.vmx檔)
有一設定選項與時間相關
tools.syncTime = "TRUE"

solution:
由於虛擬機的運行是共享主機資源，中央處理器是其中之一，有可能造成虛擬機系統時間偏差。ServiceTech 的經驗在多核處理器環境，虛擬機時間有可能有很大差誤。VMware 有資料庫文件提供修正方法，分別有 Windows 主機 及 Linux 主機。

Adobe Reader FTP site 和 Reader的使用與更新

FTP:
一般人要安裝Adobe Reader通常是到官方網站Download網頁去下載與安裝,但是所下載的安裝檔是網路安裝版,也就是安裝的過程中安裝程式需要連線到Adobe網站 下載必要檔案,以下提供另一種選擇可以下載到Adobe Reader完整版,在沒有連線到網路的主機也能安裝

Site: ftp.adobe.com
Port: 21
登入: 匿名存取
遠端路徑: /pub/adobe/reader/

例如: 要找到 Windows版, 8.12 version, 繁體中文
/pub/adobe/reader/win/8.x/8.1.2/cht/


更新:
安裝完成後若有更新檔要下載,其預設自動下載更新檔的位置:
C:\Documents and Settings\UserName\Local Settings\Application Data\Adobe\Updater5\Install

各家LDAP List 清單

各家LDAP

1.Microsoft Active Directory

2.Sun ONE Directory Server

3.Novell eDirectory

4.OpenLDAP

Oracle Object Privileges( Grant , Revoke )

給與USER01對TABLE01讀取,新增,修改的權限
GRANT SELECT,INSERT,UPDATE ON TABLE01 TO USER01;

給與USER01對TABLE01的Column2讀取與修改的權限
GRANT SELECT,UPDATE(Column2) ON TABLE01 TO USER01;

給與USER01對procedure01執行的權限
GRANT EXECUTE ON procedure02 TO USER01;

移除USER01對TABLE01讀取,新增,修改的權限
REVOKE SELECT,INSERT,UPDATE ON TABLE01 FROM USER01;

使用T-SQL設定Primary Key

USE Northwind
GO

CREAT TABLE [myTable]
(
id int not NULL,
givenName varchar(50)
)

-- Create Primary Key 並放在另一個FileGroup
ALTER TABLE [myTable]
ADD CONSTRAINT [PK_TBL]
PRIMARY KEY CLUSTERED([id])
on [IDX]


[Reference]
-- 建立或移除Primary Key節至 SQL Server 2005 線上叢書 (2007 年 9 月)
http://msdn.microsoft.com/zh-tw/library/ms190273.aspx

M. 建立含有索引選項的 PRIMARY KEY 條件約束
下列範例會建立 PRIMARY KEY 條件約束 PK_TransactionHistoryArchive_TransactionID，並設定選項 FILLFACTOR、ONLINE 及 PAD_INDEX。產生的叢集索引將與條件約束同名。

USE AdventureWorks;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
GO

N. 在 ONLINE 模式中卸除 PRIMARY KEY 條件約束
下列範例會刪除 PRIMARY KEY 條件約束，並將 ONLINE 選項設為 ON。
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

修改SQL Server主機的電腦名稱

-- 查詢本機伺服器名稱
SELECT @@SERVERNAME

-- 修改Windows電腦主機名稱

-- 重新開機

-- 啟動SQL Server Management Studio連線時使用新主機名稱

-- 查詢本機伺服器名稱
USE master
GO
SELECT @@SERVERNAME
GO
-- 修改SQL Server系統連線名稱
EXEC sp_dropserver '舊電腦名稱'
GO
EXEC sp_addserver '新電腦名稱', 'local'
GO

-- 重新啟動SQL Server執行個體
C:\net stop mssqlserver
C:\net start mssqlserver

-- 連線到SQL Server檢驗名稱
SELECT @@SERVERNAME
GO

sp_configure的使用,設定SQL Server組態

-- 顯示SQL Server組態設定
USE master
GO
EXEC sp_configure
GO

-- 設定SQL Server顯示進階選項
USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
GO

[說明 RECONFIGURE]
RECONFIGURE 和 RECONFIGURE WITH OVERRIDE 都會使用每個組態選項。不過，基本 RECONFIGURE 陳述式會拒絕在合理範圍之外或可能造成選項衝突的任何選項值。例如，如果 recovery interval 值超出 60 分鐘，或 affinity mask 值與 affinity I/O mask 值重疊，RECONFIGURE 就會產生錯誤。相對地，RECONFIGURE WITH OVERRIDE 會接受任何資料類型正確的選項值，且會強迫利用指定的值來重設組態。

RECONFIGURE 陳述式會動態更新某些選項；其他選項則需要伺服器停止再重新啟動。例如，Database Engine 會動態更新 min server memory 和 max server memory 這兩個伺服器記憶體選項；因此，您不需要重新啟動伺服器，就可以改變它們。相對地，重設 fill factor 選項執行中的值，則需要重新啟動 Database Engine。

在執行組態選項的 RECONFIGURE 之後，您可以執行 sp_configure 'option_name' 來了解這個選項是否已動態更新。動態更新的選項，其 run_value 和 config_value 資料行的值應該會相符。您也可以查看 sys.configurations 目錄檢視的 is_dynamic 資料行來了解哪些選項是動態選項。

[啟動AWE並設定最大最小記憶體]

系統資料表System Table And 系統檢視表System View

管理員連線到SQL Server要取得系統上的各種系統資訊或組態設定或系統狀態最快的方式是透過系統資料表或系統檢視表,以下介紹幾個我常用的系統資料表或是系統檢視表能幫助管理員快速的得知系統狀況。

SQL Server 2000的System Table,這些system table大部分都可以在SQL Server 2005上使用只不過在SQL 2005已改成同名的View並放在sys這個schema下,另外SQL Server 2005增加的許多功能更強大的動態管理檢視表,只要看到sys.dm_開頭的檢視表就是預設的動態管理檢視表,由於動態檢視表太多,有興趣的人請到下面這個微軟官方網站有詳細的說明文件
Dynamic Management Views and Functions


master資料庫有以下資料表或檢視表
列出所有資料庫
SELECT * FROM sysdatabases

列出所有登入帳戶
sysxlogins資料表(SQL 2000 only)
SELECT * FROM sysxlogins
sys.syslogins檢視表(SQL 2005 only)
SELECT * FROM sys.syslogins

列出目前連線到SQL Server的connection
SELECT * FROM sysprocesses

列出所有Server列如本機SQL Server,遠端的Link Server
SELECT * FROM sysservers

列出SQL Server Instance設定
SELECT * FROM sysconfigures
與執行sp_configure相同

各個資料庫底下都有以下系統資料表
當下連線資料庫所有物件的資料表,包含Table,View,Stored Procedure...等
SELECT * FROM sysobjects

當下連線資料庫所有資料表的欄位
SELECT * FROM syscolumns

sysindexes:當下連線資料庫的所有Index
SELECT * FROM sysindexes

當下連線資料庫的資料檔與交易紀錄檔案資訊
SELECT * FROM sysfiles

當下連線資料庫的檔案群組
SELECT * FROM sysfilegroups

當下連線資料庫的使用者
SELECT * FROM sysusers

當下連線資料庫的使用者權限
SELECT * FROM syspermissions

交易紀錄檔 Transaction Log 管理

對於SQL Server不熟悉的管理員或開發人員,在管理SQL Server第一個會遇到的問題就是交易紀錄檔Transaction Log越來越大,甚至於大到磁碟空間不足,造成SQL Server無法啟動。

由於目前的硬碟越來越大也越便宜,所以交易紀錄檔Transaction Log越來越大短期間並不會造成什麼影響,但是久而久之經由自動成長次數越多也更容易造成磁碟的破碎,也可能會造成系統效能上的影響。

其實只要定期執行交易紀錄檔備份Transaction Log Backup,即可輕鬆解決這個問題,因為在執行交易紀錄檔備份完成後系統預設會清空交易紀錄檔,檔案維持原來的大小但內容已經清空了,如此一來後續進行的交易紀錄就可以從檔案的開頭從頭開始使用,而交易紀錄檔就不會繼續成長而會保持一樣的大小。
SQL Server 2005


SQL Server 2000


而多久要進行交易紀錄檔備份,則要視資料庫系統的類型與交易的頻率而定,若是線上交易系統由於有大量的Insert,Update,Delete的作業,造成交易紀錄檔快速成長,
建議每天使用DBCC SQLPERF(LOGSPACE)觀察Log的成長量,即可推算出多久會達到100%使用量,排程在到達100%之前就執行Backup Log的作業,即可清空Log從頭開始使用

另外在效能的建議上建議一次給足交易紀錄檔需要的檔案大小,所以在Create Database時,指定起始大小,以目前市面上的磁碟容量,一次設定到200MB也不算太大,而建議資料檔也要一次開足大小,起始值給到500MB也不算太大

SQL Server 2005


SQL Server 2000


如果交易紀錄檔(Transaction log)已經太大了,請參考這篇

交易記錄檔Transaction log大小的檢查清空與縮小

SQL Server 與SQL Agent 服務啟動帳號

最小權限原則

SQL Server 2005
1.針對SQL Server與SQL Agent分別新增Windows Local Account或在AD上新增AD User Account

2.使用SQL Server Configuration Manager修改SQL Server與SQL Agent的啟動服務帳號,
讓系統自動給與服務帳號應有的權限

3.針對備份用的特定目錄給予服務啟動帳號Full Control權限

SQL Server 2000
1.新增一個Windows Local user Account或在AD上新增一個AD User Account
例如:SQLServer

2.使用Enterprise Manager修改SQL Server與SQL Agent的啟動服務帳號,
讓系統自動給與服務帳號應有的權限,ex:將啟動帳號加入SQL Server的sysadmin角色

注意1:若SQL Server服務與SQL Agent服務是用不同帳號會造成SQL Agent的Job工作發生問題，則要需依照以下KB解決錯誤22022問題

Microsoft SQL-DMO (ODBC SQL狀態: 42000)
錯誤 22022: SQLServerAgent 目前不在執行中，因此無法通知它這個動作。
You may receive an "Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action" error message when you run a SQL Server Agent job in SQL Server 2000
http://support.microsoft.com/kb/911841/en-us

但只要再用SQL Server Enterprise Manager修改了SQL Server啟動帳戶後，即便你輸入了"computerName\MyServiceAccount"，但最後還是會改回".\MyServiceAccount"
則必須要再去修改一次機碼

The SQL Server service and the SQL Server Agent Service fail to start on a stand-alone server
http://support.microsoft.com/kb/307288/en-us

注意2:若設定SQL Server服務帳號為Local user account,SQL Agent服務帳號為LocalSystem，同樣會造成Job工作發生問題
Microsoft SQL-DMO (ODBC SQL狀態: 42000)
錯誤 22022: SQLServerAgent 目前不在執行中，因此無法通知它這個動作。

若是又依照已下KB911841設定會遇到KB307288問題，必須修改回都用LocalSystem或都用相同的Winodw Account


3.針對備份用的特定目錄給予服務啟動帳號Full Control權限

SQL Server 2000 有以下幾種狀況
狀況1只設定SQL Agent 啟動帳號為Local user account或Domain user account
SQL Server服務帳號:LocalSystem
SQL Agent服務帳號:Local user account或Domain user account
SQL Server可以運作,Job以Local user account登入SQL Server系統,由於Local user account是SQL sysadmin伺服器角色所以擁有SQL Server系統管理員權限

狀況2只設定SQL Server啟動帳號為Local user account或Domain user account
SQL Server服務帳號:Local user account或Domain user account
SQL Agent服務帳號:LocalSystem
可正常起動,SQL Server可以運作,但Job無法執行,執行時會發生錯誤

狀況3設定SQL Server與SQL Agent啟動帳號都為Local user account或Domain user account
此為最建議的最小權限設定:
SQL Server服務帳號:Local user account或Domain user account
SQL Agent服務帳號:Local user account或Domain user account

若SQL Server與SQL Agent設定為不同帳號
可正常起動,SQL Server可以運作,但Job無法執行,執行時會發生錯誤

若SQL Server與SQL Agent設定為相同帳號
可正常起動,SQL Server與SQL Agent都可以運作,且SQL Agent所執行的Job會以啟動帳號登入SQLServer,由於啟動帳號是SQL sysadmin伺服器角色所以擁有SQL Server系統管理員權限