搬移使用者資料庫

很久之前有寫過一篇搬移系統資料庫的網誌，不過其實也就是抄msdn的，今天實做搬移使用者資料庫時，遇到很多屎結，導致不能使用detach方式搬移邏輯檔案，訊息如：

Transaction (Process ID 239) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

其實就是很多的連線一直進入sql server，踢也不能踢，殺也不能殺
解決方案很簡單

SET DEADLOCK_PRIORITY 'HIGH'

這樣就行了，
這意思是把你現在的連線優先權設成high，這樣其他的連線屎結就應該被踢掉，
接著

 EXEC sp_detach_db 'dbname', 'true'

然後再設回NORMAL就可以了

 SET DEADLOCK_PRIORITY 'NORMAL'

另外這是一個踢掉連線的方式

 use master
declare @i varchar(max)=''
select @i=@i+'kill '+cast(spid as varchar(10))+';' from master.dbo.sysprocesses
where db_name(dbid)='dbname'
execute(@i)

這是設成單一使用者模式的方式
USE [master]
 
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

這是設回去的方式

ALTER DATABASE dbname SET  MULTI_USER

另外就是OFFLINE 和 ONLINE

ALTER DATABASE dbname SET OFFLINE
ALTER DATABASE dbname SET ONLINE

其他相關的東西 查一下 msdn會比較詳盡

PHP連結sql server

最近想寫些工具來幫助工作上的一些雜事，
因為現在工作使用 mssql，加上 php 開發環境建制簡單的原因
所以採用了win7 64bit + SQL SERVER 2008 R2 + PHP5，
結果這個php 怎樣就是連不到mssql，弄了非常久，
最後重灌了apache就好了

其中該注意的地方為
1.找到php.ini裡面 ;extension=php_mssql.dll把前面的分號砍掉就可以了
2.需要注意ntwdblib.dll檔案的版本，sql server 2008需為2000.80.2039.0這個版本號碼
3.因為是win7 64bit的關係，所以ntwdblib.dll檔案要放在SysWOW64底下

但是最後卻也只能連線到localhost，
遠端的資料庫就不能連線了。
訊息：↓
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server:

原因是php.ini裡面有一行
mssql.secure_connection = on
把他改成off就可以了



花了不少時間在搞這個，尤其是最後重灌apache就好了這點讓我有點不能接受
不管怎樣筆記一下就沒錯了。

交易記錄檔案暴增主因

看到一篇有關於交易紀錄檔案暴增的文章，算是提供了相當有用的資訊 ，而且這邊不是msdn翻譯機搞出來的詭異中文了。
連結如下：
 http://msdn.microsoft.com/zh-tw/library/jj993254.aspx

節錄重點如下：

如何確認交易記錄檔案暴增主因

透過下面語法你大概可以快速掌握造成交易記錄檔案暴增的原因。

SELECT name ,recovery_model_desc ,log_reuse_wait_desc
FROM sys.databases
WHERE name ='ricotest1'

log_reuse_wait_desc 這欄位會反映目前交易記錄檔案，因為什麼因素造成無法截斷非活動紀錄，導致無法重用內部空間，進而造成交易記錄檔案持續成長，更詳細說明可參考可能會延遲記錄截斷的因素，下面我將依序介紹幾個常見原因。

沒有備份交易記錄檔案的影響

如果你查看 log_reuse_wait_desc欄位顯示Log Backup，那就表示交易紀錄檔案過大，而這也是最常見的原因，就是沒有執行交易紀錄檔案備份。我常看到很多資料庫都有完善的完整資料庫備份計畫，但是卻沒有任何交易記錄檔案備份計畫，因為大部分的DBA都以為完整備份已包含交易記錄檔案備份，但事實上，資料庫完整備份根本不包含交易記錄檔案備份，換句話說，沒有執行交易紀錄檔案備份，所以無法自動截斷記錄，並且也無法重用交易記錄檔案空間，所以交易記錄檔案只好持續成長。要解決過大的交易紀錄檔案，基本上要依企業可容許資料遺失風險來規劃交易紀錄檔案備份頻率，如每30分鐘執行交易記錄檔案備份(允許30分鐘資料遺失風險)，但如果你無法改變備份頻率的話，那麼請一開始就建立較大的交易紀錄檔案(最小化VLF數量)，並且設定自動成長，而成長大小請固定一個數值，我個人建議不要超過1.2GB，這是因為如果成長過大相對所需處理時間也拉長，如果剛好遇到交易紀錄檔案成長不夠快速，資料庫將可能遇到9002錯誤。

索引維護作業的影響

我相信大部分DBA都知道重建索引所帶來的好處，但很少人知道重建索引會為資料庫帶來什麼樣的壞處，最明顯的壞處就是交易紀錄檔案快速成長。大多數人執行重建索引都是在資料庫完整復原模式下進行(大部分正式環境資料庫都是完整復原模式)，在該模式下重建索引需要很大的交易紀錄檔案空間，而且重建索引大部分都在一個交易中處理，這表示重建索引作業是一個長時間交易作業，並且會產生很多密集交易活動，除非你很確定該索引破碎程度非常大並且已經對資料庫查詢效能影響夠大，那麼執行重建索引在所難免，但如果對資料庫查詢效能影響幅度不大或碎片幅度小的話，那麼請使用重新組織索引(Index Reorganization)來取代重建索引(Index Rebuild)操作。不管資料庫處於什麼模式下，重新組織索引所需要的交易紀錄檔案空間永遠比重建索引來的少，舉一個簡單例子來說，假設針對一個10GB索引執行重建，那麼可能需要該索引大小*1.5的交易紀錄檔案空間(15GB)才能完成該作業，但如果重新組織一個10GB索引，那只需要小於10GB交易紀錄檔案空間就可以完成，這是因為重新組織索引不會分配新頁面(page)，所以不會發生頁面分割，而且重新組織索引也使用較少的CPU和系統資源，下圖是微軟建議索引碎片臨界值，但我個人認為請依資料庫環境判斷較佳，最重要是只保留真正有用索引，刪除無用索引。

長時間交易的影響

如果你查看 log_reuse_wait_desc欄位顯示Active_Transaction，那就表示交易紀錄檔案因為長時間交易而持續成長中。交易記錄檔案內包含了資料庫詳細的資料操作過程和一些資料庫物件，所以不管任何資料操作都會產生交易記錄，而當一個交易被建立後，只有收到commit或rollback命令後，該交易作業才會真正結束。如果交易作業處理資料量龐大的話，那麼產生大量的交易記錄將無可避免，如果該資料表恰巧有FK或Trigger，那麼交易紀錄檔案成長量將更大，所以我們應該要盡量去最小化這些交易記錄活動，例如使用partition table來執行資料轉移作業(switch partition)，或是將資料庫復原模式切換成Simple後，在執行資料轉移作業，又或是縮小每次交易作業的資料大小，下面我簡單測試長時間交易(處理大資料量)所造成的影響。

形成長時間交易有幾個原因

• *應用程式沒有正確處理commit或rollback
• *應用程式逾時造成交易沒有commit
• *每一次交易作業處理資料量過於龐大

建立交易記錄檔初始大小=60MB 成長量=10MB，RecoveryMode=Full

頁面分割的影響

頁面分割作業是一項相當耗費資源的作業，會造成額外的I/O和空間浪費，但很多人可能不知道，頁面分割也會造成交易紀錄檔案快速成長，下面我簡單測試頁面分割將付出更多的交易紀錄檔案空間。

雖然我幾乎快貼了整篇文章，不過他提供的觀念真是很重要呢！

SQL語法列出排序序號

前日臨時有需求必須寫出一段語法，要列出查詢結果的排序序號，平常不常用加上使用sql 2000居多，故一時忘記函數與語法，寫出個大概但是卻是不正確的...唉唉
所以寫在這邊做個備忘

sql server 2005之後有一函數ROW_NUMBER()可使用
方法為：

select ROW_NUMBER() over (ORDER BY CLOUMN) as COLUMN_NAME from  TABLE

另外有一效能很差的寫法，但是有同樣效果：

select (select count(*) from table where table.column <= a.column) as column_name from table a

兩種方法都寫了，但是都寫錯。
字體顏色就是我忘記和寫錯的地方，唉唉，寫了這麼多年 sql 語法，在考試時寫錯真令人難過。

如何找出所有資料庫裡面所有資料表的筆數(SQL Server)

下面這段是網路上抄來的，的確是很好用，有時候是會派上用場的
來源 ： http://www.dotblogs.com.tw/lolota/archive/2009/10/13/11041.aspx

做了一點點修改，可以清楚表示 dbname

DECLARE @SQL nvarchar(2000)
DECLARE @DBName nvarchar(200)
DECLARE @name VARCHAR(150) -- database name
use master
SET NOCOUNT ON

搬移sql server系統資料庫

詳見msdn : http://msdn.microsoft.com/zh-tw/library/ms345408.aspx

搬移master

修改啟動參數裡面的-d和-l參數後面的路徑位置後，重啟sql server即可。

搬移tempdb

按照以下方式修改路徑後，直接貼上執行即可（除非有改過邏輯檔案名稱）

LogShipping的搞怪問題，first_file_000000000000.trn

在作logshipping的時後發生了怪異的問題，同一台sql server 兩個不同db 對slave作 Log Shipping，其中一個db可以正常複製還原，但是某時間之後會突然開始只還原先前已經還原過得檔案，並回報錯誤，另外一個永遠都只會出現上次載入檔案first_file_000000000000.trn，這個trn是蝦餃，msdn說就是沒有載入任何檔案的意思，就降子也沒有解決方案，google了好多網頁，原來不只有我有這問題，發現問的人多，但是回答的解決方案卻是一個也沒有，終於我找到原因，原來是系統檔案裡面紀錄了許多的歷史備份還原資料，導致這個難用的Log Shipping一直失敗，解決方案就是清掉就好了，

Use MSDB

執行系統裡面原來就有的sp
sp_delete_backup_and_restore_history
↑  我是用這個一次統統殺掉，其實還有其他的sp可以使用


目前運作正常，持續觀察中

平常db的問題大家都問我，當我有問題的時候我都不知道要問誰，
fb發言求救，得到的回應還比不上一個小妞留言說：『我肚子餓了』
這就是人篸啊！

20130312補充：
之前的問題最終解決是因為有人額外備份了交易紀錄，導致交易紀錄檔備份不連續
所以在還原的時候導致中斷，有做log shipping的朋友這點要注意喔！

SQL Server 不存在或拒絕存取 or 變更SQL伺服器名稱

<SQL SERVER 2K>
今天SQL Server的複寫忽然中斷，錯誤訊息顯示『SQL Server 不存在或拒絕存取...............』，查了一下原因居然是因為之前SE有更改過Server Name，不過sql server 名稱卻沒有更著變動所造成的，不過比較奇怪的是server 名稱變更也不是一天兩天了，過去都沒中斷，為什麼忽然就中斷了咧....可見得，有時候機器鬧彆扭，就跟女人的心情一樣是捉摸不定的，處理方式如下：


use master
go
exec sp_dropserver <old_name>
GO
exec sp_addserver <new_name>, local
GO

另外，可以使用 select @@servername來看一下目前sql server的名稱是不是和機器相同。

也順便驗證一下改好之後資料對不對。

ps ：server name 資料存在sysservers 裡面。

mysqlcheck

轉自http://ssorc.tw/rewrite.php/read-645.html

mysqlcheckmysqlcheck的功能類似myisamchk，但其工作不同。主要差別是當mysqld伺服器在運行時必須使用mysqlcheck，而myisamchk應用於伺服器沒有運行時。使用mysqlcheck的好處是不需要停止伺服器來檢查或修復資料表。
ref: http://twpug.net/docs/mysql-5.1/client-side-scripts.html#mysqlcheck

MySQL 停止後, 於 shell 下修復 TABLE(MyISM)

檢查

myisamchk *.MYI

快速修復，式圖不接觸資料檔案來修復索引檔

myisamchk -r -q *.MYI

從資料檔案中刪除不正確的記錄和已被刪除的記錄並重建索引檔

myismchk -r *.MYI

安全恢復模式使用一個老的恢復方法，處理常規恢復模式不行的少數情況

myisamchk --safe-recover *.MYI

Check Table 是否有錯誤

mysql> CHECK TABLE tbl_name [fast] [quick]

於系統運作下, 修復 TABLE

mysql> REPAIR TABLE table_name

ref: http://open.38.com/index.php/topic,1357.0.html

http://linuxhelp.blogspot.com/2006/02/basic-setup-of-mysql-in-gnulinux.html

http://linuxhelp.blogspot.com/2005/06/mysql-cheat-sheet.html

http://www.hkwebs.net/catalog/teach/

最佳化MySQL
mysqlcheck -a -c -o -r 資料庫名稱 資料表名稱
mysqlcheck -a -c -o -r --databases 資料庫名稱1 資料庫名稱2 資料庫名稱3
mysqlcheck -a -c -o -r --all-databases

-a = Analyse given tables.
-c = Check table for errors
-o = Optimise table
-r = Can fix almost anything except unique keys that aren't unique

或者
mysql> use 資料庫名稱
mysql> OPTIMIZE TABLE 資料表名稱

更多内容 http://ssorc.tw/rewrite.php/read-645.html#ixzz1fecO1TTO

lock!!!!!!!!

常常有人問我死結怎麼辦？通常我都會說去開profiler..

不過其實還有sp_lock可以查詢死結，查出來的結果通常看起來怪怪的那個就是死結了，至於sp_lock欄位的意義就上MSDN去看看就知道了。

sp_who也可以加減參考一下。

至於各spid代表是什麼語法造成死結呢？
使用DBCC inputbuffer(spid)這樣就看得到了！

mysql join update

有時候刪除資料必須關聯另一張表才能篩選出來

就必須update和join 同時

update table1 ,table2

set table1.column = table2.column

where table1.column2 = table2.column2

或者

SQL 取亂數

<這是MYSQL>

今天舊同事問了一個取亂數的問題 例如製作卡號取英文字母亂數

select char(65+floor(1+rand()*25))

其實這樣就可以解決了

 

 select char(65)  ---->  A

 

floor取最小整數值

 

RAND()取亂數

 

迴圈跑一下 看要多少長度的字串就給他跑幾次囉

MYSQL 字串連結函數CONCAT()

 

盡信書不如無書---with(nolock)的迷思

工作遇到某位資深「IT人員」，無論是什麼SELECT語法都要求下屬一定要加上WITH(NOLOCK)，今天我就要打破這樣的迷思

所謂WITH(NOLCK)的確可以減少LOCK的發生，但是並不是加上WITH(NOLOCK)就不會造成鎖死（死結），為啥MSSQL會有LOCK

這種東西？！先想想吧！

WITH(NOLOCK)是告訴SQL SERVER這段SELECT無須考慮目前資料表的TRANSACTION  LOCK狀態，

正因為如此，WITH (NOLOCK)不考慮目前table的transaction lock，當有某些資料正處於多個phase交易(例如跨多個table的transaction交易)，
WITH (NOLOCK)會讓目前處理交易程序的資料被忽略，看不懂嗎？簡單說，SELECT一個正在交易的TABLE，而交易尚未完成，

因為SELECT加上WITH(NOLCOK)然後發生上述的狀態............

盡信書不如無書！！別以為加上WITH(NOLOCK)就是萬靈丹！！！

 

附帶一提

WITH (NOLOCK)相當於 READ UNCOMMITTED

WITH (NOLOCK)可以加快查詢的速度

 

------------------------------------------

2009-05-18 補充

舉例來說

A有一百元 他想要領出 十元來花用

A的公司要匯入他當月薪水兩百元

 

如果兩段交易同時進行，那不管那段交易先完成，資料都會是錯的

所以該資料表一定先被LOCK，後面的交易就不能進行，要等到前面的交易完成，

這樣才能確保資料是正確的

 

所以如果不考慮資料表的TRANSACTION LOCK，那A的存款可能會從290元

變成90元，後面匯入的薪資兩百元會無緣無故消失了。

無法開啟XXX.MYI檔案

MYSQL忽然無法開啟XXX.MYI檔案

在命令列輸入myisamchk -r -q d:\mysqldata\DB_name\table_name.MYI

就可以修復INDEX

例:c:\mysql\bin\>myisamchk -r -q d:\mysqldata\DB_name\table_name.MYI

集合的用法

JOIN

join的用法就是將兩個table結合起來

on 則是條件的用法

語法如下:

 

select *

from A_Table Join B_Table

On  A_Colum = B_Column

 

LEFT JOIN

LEFT JOIN 的用法就是將兩個table結合起來

on 則是條件的用法

但是以左邊的為重

意思是左邊的會通通叫進來

右邊的只有符合條件的會被select出來

語法如下:

 

select *

from A_Table Left Join B_Table

On  A_Colum = B_Column

 

RIGHT JOIN

有LEFT JOIN 當然就有 RIGHT JOIN囉

意思就是和LEFT JOIN相反

語法相同

 

##

雖然JOIN的語法像上面這樣

但是我經常把JOIN寫成

select  a.XXX,b.XXX from A_table a,B_table b where a.ZZZ = b.ZZZ

XXX代表所要選取的欄位

ZZZ代表條件

這邊要注意就是選取的欄位儘量不要用 *(星號)

因為可能會有相同的key在裡面

造成結果與事實有點出入..(這句話怪怪低..不過意思大至是如此)

##

 

Union

Union就是將各select結果集合在一起

如:

 

select * from A_table

UNION

select * from B_table

不過 UNION 必須所選取的欄位通通要一樣

否則就必須指定NULL囉

 

 

SQL-無法解析 equal to 動作的定序衝突

select * from acc_info_tbl  where account_id in (select account_id from hot1003b)

出現如下訊息:

SQL-無法解析 equal to 動作的定序衝突 <=== 完全看不懂的一句話

解決方法如下:

select * from acc_info_tbl  where account_id in (select account_id collate chinese_taiwan_stroke_ci_as  from hot1003b)

 

COLLATE Chinese_Taiwan_Stroke_CI_AI
就是將兩個資料庫做同樣的字典設定
CI就是不分大小寫
AI就是不分腔調