[MSSQL]DB log減肥、資料表佔用空間筆記

由於 DB 內屬重要的資料，但其實也會有系統將暫存資料寫在 DB 內造成資料成長很快，DB 資料庫常有越來越肥大的狀況，這時候就會需要以下方式來讓 DB 減減肥

Log 減肥

• step.1 查詢 DB 大小

EXEC sp_helpdb "DatabaseName"

• step.2 將 database 還原為簡單模式 (不記錄Log)

alter database "DatabaseName" set recovery simple with no_wait

• step.3 將查出來的 log name 資料帶入以下。(參數 “2” = 2MB)

DBCC SHRINKFILE([name],2)

• step.4 將 database 還原回完整模式 (開始記錄 log)

alter database "DatabaseName" set recovery full with no_wait

若是不想儲存交易紀錄，可於資料庫屬性→ 選項→ 復原模式設為”簡單”  

取得資料庫中各 Table 的使用量資訊

• 使資料庫不會回傳計數 , 參閱 Microsoft手冊

SET NOCOUNT ON

• 先將資料庫資料表 update

DBCC UPDATEUSAGE(0)

• 查詢目前資料庫 size

EXEC sp_spaceused

• 新增一個 #t 的 table , 以便將查詢的資料塞到 table

CREATE TABLE #t
(
  [name] NVARCHAR(128),
  [rows] CHAR(11),
  reserved VARCHAR(18), 
  data VARCHAR(18), 
  index_size VARCHAR(18),
  unused VARCHAR(18)
)

• 把每個 table 使用的資訊存到 #t 這張表中

INSERT #t EXEC sys.sp_MSforeachtable 'EXEC sp_spaceused ''?''' 

• 依使用空間較大的依序排列並顯示 MB

SELECT * 
, LTRIM(STR(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB'
AS reservedSize_M
, LTRIM(STR(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB' 
AS dataSize_M
, LTRIM(STR(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB'
AS indexSize_M
FROM #t
ORDER BY CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) DESC

• 顯示總共筆數及總共使用資訊

SELECT SUM(CAST([rows] AS int)) AS [rows]
, LTRIM(STR(SUM(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB' 
AS sumOfreservedSize_M
, LTRIM(STR(SUM(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB'
AS sumOfdataSize_M
, LTRIM(STR(SUM(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB'
AS sumOfindexSize_M
FROM #t