Tag Archives: DBA

TSQL Get size of all tables in database – TSQL 查詢Table佔用了多小空間

在整理公司的Database 其間發現公司的其中一個Database 的 MDF 檔 799GB 大
之後發現入面有很多用來做Backup的Table
是Developer 有時貪方便
便使用 INSERT INTO z_TableName
來Backup Table 之後便留下了很多的佔有空間的Table 又未必有用 便在這個Database 上

或些有時候有些Table存有很多 舊的Data..又可能不常用
所以希望找一個Query來 查詢在Database上的Table佔有多小空間 和用了多小Row
有多小空間 Provision了 但是沒有用.. 那我們可以考慮使用一些 TSQL CommandReclaim/釋放 那些空間

解決方法十分簡單
我們可以使用這個Query便可以了

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8/ 1024.00 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 /1024.00AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024.00 AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

TSQL Show All space used in Database per table
Hope you find it useful

Microsoft SQL Server Check If Full-Text Search is Installed – 如何查詢 SQL Server上的 Full Index Search 是否已經安裝

最近忙於將公司的 Database由舊的Server Migrate到新的 Environment
舊的DBA當然很小心地沒有留下任何 Notes 他怎麼設定舊的Database
所以這次Migration有很多東西都要自己摸索

在設定時安裝程式問..要不是安裝 Full Text Search..
我真是不清楚舊的Server有沒有安裝.. 只是知道沒有使用過這個Feature
做了一會research之後發現
其實是可以用Query來查詢這些設定的
我們可以使用以下我Query來查詢 SQL Server上的 Full Index Search 是否已經安裝

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

SQL Server Full Text Search Info
以下的Query可以提供更多的資訊

SELECT * FROM sys.fulltext_catalogs

Hope you find it useful

TSQL Get Index Size Per Table – 如何使用TSQL 來查詢每一個Table上 每一個Index 佔用的空間大小

今天想和大家分享如何使用TSQL 來查詢每一個Table上 每一個Index 佔用的空間大小
這個可以令大家明白Database 的空間有多小放在Index上
明白更多..應該有幫助吧

解決方法
我們可以使用以下的Query 來查詢 每一個Table上 每一個Index 佔用的空間大小

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)',
8 * SUM(a.used_pages) / 1024.00 AS 'Indexsize(MB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

TSQL Get Individual Index SizeHope 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 via GUI – 如何轉換 Redgate的 sqb file 到 SQL Server 能restore的 .bak 檔案 用 GUI 使用者介面

在之前的網誌和大家介紹了如何使用 sqb2mtf.exe 的指令來轉換 RedGate SQL Backup Tool 備份的 SQL Database .sqb 檔案 轉換為一個 Microsoft SQL Server 可以Restore/復原的 備份檔案格式 .bak

今天發現了..原來有一個有介面的功具.
使用方法也十分簡單
我們以到以下網址下載.. [不用安裝的]
http://downloads.red-gate.com/labs/SQBConverterGUI.zip

解壓縮之後開啟 “SQBConverterGUI.exe
SQBConverterGUI Tool to convert Redgate Database Backup file to .BAK

之後選擇要轉換到的格式. E.G. .BAK
選擇檔案
如果有密碼的輸入密碼

SQBConverter GUI
之後按”Convert / 轉換” 便可以
這個程式亦都會報告狀況的
SQB Converter report progress

等一會..如無意外便會完成

SQB Converter Conversion completed
完成後 便會看到很多的 _00.bak _01.bak.. _13.bak 檔案
Lots of .bak files

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

How to update default language from SQL Server – 怎樣更改 SQL Server的預設語言

在安裝SQL Server 其間好像沒有地方可以給我們更改 SQL Server的預設語言
所以每當我們建立新的Database的時候都會用安裝時預設的語言 E.G. English [US English]

如果想了解怎樣找到當時使用者在 SQL Server 或這個Database 上的 語言可以到以下的網誌參考
TSQL Get Current SQL Server language – 使用TSQL 取得現在的SQL Server 使用中的語言

當我們需要更新現有Database的預設語言 我們會用到這一個 指令

EXEC sp_configure 'default language', [Language ID] ;
RECONFIGURE

但是怎樣可以找到 SQL Server上的 Language ID
大家可以執行以下的 TSQL 指令

SELECT *
FROM sys.syslanguages

SysLanguages on Microsoft SQL Server
當找到想更新到的 Language ID 時大家可以執行 sp_configure去更新 Database / 建立新用戶時的預設語言
在公司需要使用的 預設語言是 British English
所以我要執行的 TSQL 是這樣的

23 是British English

EXEC sp_configure 'default language', 23 ;
-- 之後再執行 Reconfigure 去安裝這個設定
RECONFIGURE

Hope you find it useful