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

由於DB內屬重要的資料，但其實也會有系統將暫存資料寫在DB內造成資料成長很快

DB資料庫常有越來越肥大的狀況，這時候就會需要以下方式來讓DB減減肥

Log減肥

查詢DB大小

EXEC sp_helpdb "DatabaseName"

將查出來的資料代入以下

DBCC SHRINKFILE([name],2)

ps: 參數2 = 2MB

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

取的資料庫中各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

DROP TABLE #t