Shrink Database從8T壓縮到5T要多久?

        有機會碰到這樣的需求應該不多，特此記錄一下，執行環境尚未上線，所以可以有時間來處理，硬碟是SAS 10K 1.2T的，使用Raid 5，用Backup測試一下IO的速度如下

        實際在Shirk的IO如下，沒有其他活動喔，以上數據僅供參考

無法開啟SQL Server Error Log

        今天在檢查某台SQL Server，在本機上透過SSMS要開啟Error Log，等了好久居然打不開？出現如下錯誤

An exception occurred while executing a Transect-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
A severe error occurred on the currentcommand. The results, if any, should be discarded

[T-SQL]如何判斷IP是否落在某段IP範圍內？

        最近要測試Excel的Power Map，一般系統不會記錄使用者的地理資訊，頂多記IP吧，稍微查了Get and prep your data for Power Map，裡面提到Power Map的地理資訊支援下列幾種

Latitude/Longitude pair, City, Country/Region, Zip code/Postal code, State/Province, or Address

        用經緯度分析資料除非資料量非常大，要不然量小看起來沒什意義，那收斂到City感覺比較合理，所以找了一下如何用IP轉City，當然網上一堆服務可以用，但我不想讓DB層對外，想看有沒有現成的對應表可用，結果DB-IP - IP Geolocation and Network Intelligence有現成的DB可下載，免費版的可就可對應到city耶，五百萬筆資料應該也夠測試了 ，於是就下載來試試

        下載回來的資料是csv，我一開始轉入DB時發現一些問題，資料會有亂碼，用Unicode轉進去會剖析不了，發現編碼好像怪怪的，所以調成下圖那樣就可以匯入了

[推薦][SQL Server]sp_who加強版系列

        sp_who或sp_who2系列一直以來不斷的有高人加強它衍生出很多版本，這邊紀錄一下看到覺得不錯用的，給不知道的人參考

• Who is Active v11.11 這個我用最久了，很好用，但沒有對應SQL 2012之後的版本

• sp_AskBrent 這個含有whoisactive的功能，再加上job、 wait statistics及Perfmon counters的監控，也很不錯

• sp_whopro 這個最近看到的，對應到最新SQL 2014版本，但還沒試，感覺像whoisactive囉

[SQL Server][Oracle][MySQL][PostgreSQL] IN條件式寫法的差異

        一般找某個資料表的資料是否存在另一個資料表中，就語法上來說，可以用INNER JOIN，若條件式僅JOIN單一欄位，可以改成用IN或EXISTS；若是JOIN多個欄位，就無法用IN囉，這是在SQL Server的情況

        無意間看到在Oracle中，IN條件式的寫法裡竟然可以接受多個欄位呢，另外試了MySQL與PostgreSQL也都可以喔

msdtc settings not configured optimally

        話說我用SQL Server 2008 R2 BPA分析新安裝的SQL Server，有一項Warning是msdtc settings not configured optimally，照建議的 MSDTC 設定將用於分散式交易中 SQL Server的這個去設定，我發現怎樣都不會通過檢查，後來仔細看它Issue的地方有反饋一些registry key得設定成建議的值，回推正確的設定應該如下圖，供參考

DBCC SHRINKFILE遇到cannot be shrunk as it is either being shrunk by another process or is empty.

        先不管這件事好不好，同事因為磁碟空間不足去DBCC SHRINKFILE資料檔，結果遇到下面的錯誤

File ID # of database ID # cannot be shrunk as it is either being shrunk by another process or is empty.

        理論上SHIRKFILE不是獨佔的行為，不需要停機進行，而同事已經在應用停止的情況下作業了，也確認沒有另一個process在shirk，資料檔也是有剩餘幾十G的空間，但出現這錯真是奇怪

        上網搜尋了一下，這篇DBCC SHRINKFILE and SHRINKDATABASE failing after backup的解法竟然是建議對資料檔加大幾MB的空間，照著做再shirk還真的成功了，實在不解啊

[SQL Server]Replication如何加入新的發行項

很簡單...紀錄一下...以免忘記

對Local Publications下你要加入新的發行項的發行集右鍵，選屬性

在Articles加入新增的資料表為發行項後

[SQL Server]Snapshot Replication

        很簡單的設定，但久久用一次沒記錄很容易忘記，這邊記錄一下囉！當然前置作業還要設定SQL Server Agent的啟動帳號與權限，還有快照集目錄的設定，我這邊沒提到，至於Transactional replication與Merge replication設定上也差不多，但使用限制跟情境不太一樣，自行參考Types of Replication Overview吧，還有點對點的交易式複寫可參考Cary大師的SQL Server 分散式架構 - 點對點交易式複寫 + NLB囉

[SQL Server]怎麼查ShrinkFile進度及預估完成時間呢?

   非不得已還是不要用shrinkfile吧，沒什好處，但廠商要求就只能照做了，結果出乎意外地跑了好久，MDF不過500G，使用380G，壓成400G，結果花了快八小時！

--壓縮之前先確認有沒有足夠的可用空間可以移除

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

--shirk data file(MB)

DBCC SHRINKFILE (N'TestDB_log' , 1024)

--shirk log file(MB)

DBCC SHRINKFILE (N'TestDB' , 10)

關於Shrink的過程可參考下列連結

How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000

--因為實在太久了，找到以下可以看到進度及預估時間的命令
--但真的是估計，會一直變，會一直往遞延
--實際上完成時間往後遞延了一個多小時喔...@@

SELECT

     session_id,

     percent_complete,

     DATEADD(MILLISECOND,estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,

     (total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,

     DB_NAME(Database_id) Database_Name ,command,sql_handle

FROM sys.dm_exec_requests

where command in ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact');

下面這篇有提到shirk的建議作法，也可參考

Why SHRINKFILE is a very bad thing, and what to do about it.

使用前可參考DBCC SHRINKFILE (Transact-SQL)

[HammerDB][TPC-C]Database load testing and benchmarking tool for SQL Server

        HammerDB是一個open source的多執行序效能測試工具，支援多種DB，如Oracle、SQL Server、PostgreSQL、MySQL等的DB，對應SQL Server的版本有SQL 2008及SQL 2012，最新的SQL 2014並沒有支援

        它本身支援TPC-C模型，TPC（Transaction Processing Performance Council）是一系列交易處理和資料庫基準測試的規範。其中TPC-C是針對OLTP的基準測試模型，模擬零售商店下訂單的交易環境，是很熱門的基準測試模型

        用HammerDB進行TPC-C測試是很簡單的，只要多試幾次就能上手喔，可參考以下說明

[MySQL][ODBC]reading authorization packet, system error 2

        我想在SQL Server上打算建Link Server到MySQL去，要連兩台MySQL，所以建兩個Link Server，測試時先在兩台上建同一組帳密，然後開始在SQL上設ODBC，第一台順利建立完成，但第二台設ODBC時就出現如下錯誤

        看起來像驗證的問題，我原以為密碼錯誤，但是我SQL上用mysql cmd連是OK，MySQL Workbench也OK，也確認ODBC輸入的帳密沒問題

        我想該不會是帳號怪怪的有問題吧，我又建立另一組帳號，只是帳號名稱不同，密碼權限相同，結果另一組帳號可行

        總結一下，在同一台MySQL上，建兩組帳號，除帳號名稱不同外，密碼權限皆相同，在遠端用mysql cmd與Workbench兩個帳號都通過驗證，但odbc卻有一組帳號會出現上面那個錯誤，我以為是帳號名稱太長，但另一台同樣的帳密卻OK，真是活見鬼

        在網上搜尋一下，Bug #28359：Intermitted lost connection at 'reading authorization packet' errors，我覺得應該是Bug

在SQL Server上建立連結伺服器到Oracle

        方法超簡單，首先要SQL Server上安裝ODAC，裝完後記得重開機

        然後展開Linked Servers的Providers，應該要有OraOLEDB.Oracle喔，如上圖

[BPA]安裝Microsoft SQL Server 2008 R2 Best Practice Analyzer

        周五參加集英信誠舉辦的與大師對談的課，其中的一堂課裡有提到BPA(Microsoft SQL Server Best Practice Analyzer)，可以很方便的診斷SQL Server的設定與微軟建議的Best Practice有何不同，回家後就來安裝試試

        要使用BPA需先安裝MBCA 2.0，然後針對SQL 2008 R2與SQL 2012則要分別下載

• Microsoft® SQL Server® 2008 R2 Best Practices Analyzer
• Microsoft® SQL Server® 2012 Best Practices Analyzer

        然後我安裝SQL2008R2BPA_Setup64時遇到下面的錯誤

Remote Scan的問題

        在Performance Dashboard看到有不正常的Waiting Requests，點進去看Query Plan，發現問題在於Remote Scan，如下圖

[CTE]Common Table Expession到底有沒有使用TEMPDB呢?

        今天遇到一位同事，聊了一下跟我說CTE少用，因為CTE會佔用TEMPDB的空間，用的不恰當會把TEMPDB灌爆，講的煞有其事，我心想好像不是吧，印象中CTE並不會佔用TEMPDB的空間，但我也沒有去證實過也就沒有反駁了

        之後我查了一下WITH common_table_expression (Transact-SQL)，上面是有提到Specifies a temporary named result set, known as a common table expression (CTE). 不知是不是看到temporary就認為一定是存在TEMPDB裡？

         那到底會不會佔用TEMPDB呢？我找到Steve這位大師的Temp Table vs Table Variable vs CTE and the use of TEMPDB這篇文章，值得一看，講得清楚明白啊

[T-SQL]CTE也可以用來簡化你的UPDATE喔

        CTE一般說來都拿來參考用，也就是拿來Join，但我不知道竟然可以直接拿來Update呢，而不用Join喔，第一次看到有人這樣用我還很驚訝，心想怎麼不需JOIN呢？

        不多說，直接看例子吧

[DMVs]Analysis Services也有專用的DMVs

        前一篇文章我介紹了幾個XMLA的例子，不管是Process,Backup或Restore都是XMLA的命令，但其實我最先接觸的是XMLA的方法，因為我想取得Cube的Metadada，所以我使用Diccover方法，可是發現實在太難用了，後來發現竟然有替代Discover方法的的東西，就是Data Management Views (DMVs)

        看到DMVs，DBA應該都很熟悉，不過這個是Analysis Services(AS)的DMVs，不是SQL Server(SQL)的DMVs，使用起來是有差異的喔

• 工具：SQL的DMVs是用Database Engine Query，而AS的DMVs得用MDX Query或DMX Query

• 查詢語法：SQL的用的是Transact-SQL的Select，而AS的是SELECT (DMX)

[T-SQL]CROSS APPLY的用法你會幾種?

        在沒看過The many uses of CROSS APPLY這篇之前，以前我只知道配合Inline Table-Valued Function來使用，今年五月改做BI的工作之後，常需寫ETL Script，遇到要UNPIVOT Table的狀況，想到之前設計的Inline Table-Valued Function裡面有用到Tally Table，由Tally Table想到SQL 2008之後的table value constructors (TVCs)，彼此配合使用就可以很簡單的UNPIVOT了說

        也就是說我只知道兩種用法，沒想到還可以切割XML跟重用計算欄位呢！那你知道幾種呢？

        有兩個常需父母操煩小孩後，時間都給了小孩，都沒時間念書，也很少有時間寫Blogger了，現在居然瘦到十幾年前高中時的體重了，有沒有給他誇張，所已請見諒我很少更新Blogger了...

       

[Documenting]結合Table Layout與Value

        現有個需求是製作Table Layout的文件，有個比較特別的是還需要列出一筆對應的欄位值，產生Table Layout很簡單，列出一筆資料也很簡單，結合的話可能得用到Execl，把那一筆資料轉置，我想把這幾個步驟自動化，之後如果要製作所有的資料表時就會方便很囉了