Category Archives: MSSQL Tips and Tricks

解決Transaction Log is Full的問題 – Full Recovery Mode

之前介紹過  – 如何解決Transaction Log is Full的問題 – Simple Recovery Mode

今天想和大家介紹怎樣解決Transaction Log is Full的問題 – Full Recovery Mode
原來DatabaseRecovery Mode 設定為 “Full”的時候

即使做了Database Full Backup 也不能解決Transaction Log is Full的問題

其實解決方法十分簡單 和之前的網誌的解決方法差不多

唯一改變的是 “Backup type:” 選擇 “Transaction Log

E.G.
SQL Server Management StudioDatabase 上Right Click
之後選擇”Task“->”Backup
SSMS -> Backup Database

在”Back Up Database 對話方塊” 上 的 “Backup type:” 選擇 “Transaction Log
Backup Transaction Log - SSMS
之後選擇Backup 到的地點..按OK 便可以了

Hope you find it useful

解決Transaction Log is Full的問題 – Simple Recovery Mode

經過一會兒學習之後..終於學會了怎樣好好地解決Transaction Log Is Full的問題

之前和大家介紹過
怎樣取得Transaction Log的大小資訊
E.G. Transaction Log的檔案大小和用了多小真實空間

TSQL – How to Get the Transaction Log Size -MSSQL

今天想和大家介紹怎樣解決
如果你的DatabaseSimple Mode的話

你們只需要把Database 做一個Full Backup便可以解決這個問題了

SQL Server Management StudioDatabase 上Right Click
之後選擇”Task“->”Backup
SSMS -> Backup Database

在”Back Up Database 對話方塊” 上 的 “Backup type:” 選擇 “Full
Backup Database Dialogue Box
之後選擇Backup 到的地點..按OK 便可以了

Hope you find it useful

SQL Backup failed with exit code: 5240 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed. – SQB service did not acknowledge receipt of data (WAIT_TIMEOUT)

公司同事成日話..公司的 SQL Back up / Log Shipping 等的 SQL Job 成日都failed
好耐都無 success過了

最後都是忍不住去看看有什麼問題
SQL ServerLog File Viewer 上看到以下的 Log Summary
SQL Backup failed with exit code: 5240 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.

Date		12/04/2016 14:00:00
Log		Job History (SQL Backup log shipping job for database ShareChiWaiDB created 28/03/2011 17:24:57)

Step ID		1
Server		SQL_01
Job Name		SQL Backup log shipping job for database ShareChiWaiDB created 28/03/2011 17:24:57
Step Name		execute master..sqlbackup
Duration		00:00:15
Sql Severity	16
Sql Message ID	50000
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
Executed as user: SQL_01\Administrator. SQL Backup failed with exit code: 5240  SQL error code: 0 [SQLSTATE 42000] (Error 50000).  The step failed.

SQL Server Log File Viewer
因為知道這個SQL Job 是由RedGate建立的
所以便去了RedGateSoftware上看看
開啟這個Database node 時 發現以下的錯誤信息
SQB service did not acknowledge receipt of data (WAIT_TIMEOUT)
SQL service did not acknowledge receipt of data (WAIT_TIMEOUT)
做了一會research 之後發現解決方法十分簡單

只要去了那個Database Server的 “Administrative Tools” -> “Service” 上重新啟動 RedGate的”SQL Backup Agent“便可以了
Administrative Tools -> Services

Service” 上重新啟動 RedGate的”SQL Backup Agent
Restart RedGate -> SQL Backup Agent
Hope you find it useful

SQL Server Keep Track on Log Space

最近終於有機會 去做一些Database Mangement 的任務
如果有留意之前的Blog都會發現公司很多時候都會有Transaction Log Full 這個問題
所以便想寫一些schedulemoniter transaction log什麼時候會用了多小
還有當有log shipping有問題是 也可以predict得到
[因為Transaction logused Percentage 應該都會留在得高位置]
首先我們要建立一個 Temp table去把 之前介紹 取得Log Space 資訊輪出的資料暫存在這個Table上

TSQL – How to Get the Transaction Log Size -MSSQL

DECLARE @LogSpace TABLE(
DatabaseName VARCHAR(255), 
LogSizeMB DECIMAL(18,2), 
SpaceUsedPercentage DECIMAL(18,2), 
LogStatus VARCHAR(1)

)

之後把Export出來的Log 資訊 暫存在這個Table上

INSERT INTO @LogSpace
EXEC('DBCC SQLPERF(logspace)');

由於這是一個Demo所以便再Create 一個新的temp table 來儲存這個Log資料
但是在這個Table上我加了 DOE (Date Of Entry) 來方便將來分析這些Log什麼時候會開始滿

DECLARE @LogSpaceLog TABLE(
DatabaseName VARCHAR(255), 
LogSizeMB DECIMAL(18,2), 
SpaceUsedPercentage DECIMAL(18,2), 
LogStatus VARCHAR(1),
DOE DATETIME DEFAULT GETDATE()

)

加資料加進這個Table上

INSERT INTO @LogSpaceLog
(DatabaseName, LogSizeMB, SpaceUsedPercentage, LogStatus)
SELECT DatabaseName, LogSizeMB, SpaceUsedPercentage, LogStatus
FROM @LogSpace

Preview

SELECT *
FROM @LogSpaceLog

hope you find it useful

TSQL Case Sensitive Query – TSQL 如何寫一條Query可以做一個Case Sensitive match

今日公司有一個程式出現了一個問題
內容大概是這樣的 “Incorrect syntax near the keyword ‘and’
TSQL Incorrect syntax near the keyword
程式是用來執行一些使用者寫個SQL…
我們這個程式當作 Data Integrity Checker
誰不知有使用者在更新這個Table上的 SQL 寫了一些有Syntax Error
SQL Query 沒有檢查便儲存了
嘗試使用Query去找出那些SQL Query‘and’這個字
但是由於之前設定 Database 時設定了 TSQL 是 Case Insensitive的關係
所以便出現了很多 有 And的 record出現 E.G. ‘AND’, ‘And’, ‘and’ 等等

那麼怎樣可以寫一條Query可以做一個Case Sensitive match
做了一會research之後發現 原來解決方法十分簡單

解決方法:
我們只需要 在 WHERE Clause上 要比較的 Field上使用COLLATE Latin1_General_CS_AS [一個Case Sensitive的 collation/ 排序規則] 便可
E.G.

SELECT *
FROM DataIntegrityTable
WHERE IntegritySQL LIKE '%and%' COLLATE Latin1_General_CS_AS

Hope you find it useful

SQL Server TSQL how to View Index Fragmentation – 如何查詢資料庫的索引碎片 資料

今天在研究 Database Performance / 資料庫效能時發現..
原來Database 會像我們的硬碟一樣..會出現 Fragmentation/碎片 的
如果Fragmentation 越大..
Database的效能便會越差..
當然每天的Database Maintenance plan 應該是可以解決這些問題的

今天想和大家分享一個SQL Query 用來 查詢 Database Index 的 Fragmentation資料庫的索引碎片

首先我們要選擇想查詢的 database
之後執行以下的SQL Query

SELECT OBJECT_NAME(st.object_id) AS TableName, 
i.name AS IndexName, 
st.index_type_desc,
st.avg_fragmentation_in_percent, 
st.fragment_count, 
st.avg_fragment_size_in_pages, 
st.page_count, 
st.avg_page_space_used_in_percent, 
st.record_count,
 st.index_depth, st.index_level,st.partition_number, 
st.ghost_record_count, 
st.min_record_size_in_bytes, st.max_record_size_in_bytes, st.avg_record_size_in_bytes
FROM  sys.dm_db_index_physical_stats(DB_ID(DB_Name()), NULL, NULL, NULL , 'SAMPLED') as st
JOIN sys.indexes i 
ON st.object_id = i.object_id AND st.index_id = i.index_id
WHERE index_type_desc <> 'HEAP'

Database Fragmentation

之後他便出輸出很多有用的資訊
我對下面的幾個資訊比較有興趣
avg_fragmentation_in_percent – IN_ROW_DATA 配置單位中,索引的邏輯片段或是堆積的範圍片段。其值以百分比表示,而且會考量多個檔案

fragment_count – IN_ROW_DATA 配置單位分葉層級中的片段數目

avg_page_space_used_in_percent
– 所有頁面所用之可用資料儲存空間的平均百分比。 如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的平均數。 如果是堆積,則為 IN_ROW_DATA 配置單位中所有資料頁的平均數。

record_count-總記錄數。

有關Database Index 的 Physical statiic 的資料.可以參考以下網頁
https://msdn.microsoft.com/en-us/library/ms188917.aspx

Index Info

Hope you find it useful

Convert RedGate SQL Backup from sqb file to .bak without installing RedGate – 如何轉換 Redgate的 sqb file 到 SQL Server 能restore的 .bak 檔案

最近公司更新的給客戶用使用的網頁..
入面有一個Bugs.. 出現了很久…但是今天才發現..
所以便要restore / 復原 公司的 Database backupcompare data / 比較數據
由於公司的前DBA [現在以Contractor 的方式繼續在公司裡工作..] 不在英國的關係..要等待他回覆或 Restore Database..可能需要等幾天..
最大的挑戰是.. 公司的Database 是用RedGateDatabase Backup tools 來進行 Backup / 備份
而同事們不不太清楚那個電腦安裝了RedgateSQL Backup Pro..
還有.我們需要把 redgate SQL backup tool backup format .sqb 轉換成 .bak 來給SQL Server來 restore.

做了一會research 之後終於找到了解決方法..
我們可以到以下載SQL Backup convert tools
http://downloads.red-gate.com/labs/sqb2mtf.zip

解壓縮後我們可以執行以下指令去轉換 SQL Backup的格式
E.G.
首先..用Command prompt 去到儲存剛剛解壓縮的資料夾..
之後執行以下指令 去使用sqb2mtf.exe
sqb2mtf的指令是這樣的
sqb2mtf [input file e.g. .sqb 輪入那個 redgate的備份檔案 .sqb e.g. “f:\Database Backup\sharechiwai.sqb”] [output file e.g. .bak 輪出的檔案名稱 .bak e.g. “f:\Database Backup\sharechiwai.bak”] [password / ]
RedGate Database Backup tool command to convert .sqb to .bak via sqb2mtf tools
E.G

sqb2mtf "f:\Database Backup\sharechiwai.sqb" "f:\Database Backup\sharechiwai.bak"

開始轉換了…這個程式會report完成轉換了多小..
大家可以預計要等多久
終於完成轉換了
sqb2mtf process completed

完成後 便會看到很多的 _00.bak _01.bak.. _13.bak 檔案
RedGate Database Backup conversion completed

之後便可以使用 Microsoft SQL Server 來用這些檔案來 Restore Database

Hope you find it useful