sql server 的Maintenance Plans(維護計劃)詳解

下麵說下我遇到的場景，就是我通過資料庫自身的維護計劃建立了資料庫收縮自動計劃，卻發現資料庫並沒有實際性收縮。

前奏自動化配置流程

資料庫--管理---維護計劃--雙擊（維護計劃嚮導）--下一步--名稱：test----選擇一項或多項維護--收縮資料庫--下一步---資料庫：選擇（列表勾選）確定，設置當前資料庫大小超過制定值時收縮資料庫（s） ,收縮後保留的可用空間（這兩個數值的設置策略參看下麵）---一直到完成

1、後來仔細閱讀了一下官網文件（https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017）：

2、如果不是太理解可以參考下麵一位大神的理解（https://www.cnblogs.com/lx823706/p/5194558.html）：

dbcc sqlperf(logspace)  --查看資料庫日誌空間使用情況

use xxxDB

exec sp_spaceused 　　--查看具體資料庫的空間使用情況

select * from sys.sysfiles　　　　--查看具體資料庫的邏輯文件名以及日誌文件名

DBCC SHRINKFILE　　　　　　　　　　--收縮資料庫中指定數據文件（或日誌文件）的命令

DBCC SHRINKDATABASE　　　　　　　　--收縮資料庫中所有數據文件和日誌文件命令

Transact-SQL 參考：

DBCC SQLPERF

提供有關所有資料庫中的事務日誌空間使用情況的統計信息。

語法

DBCC SQLPERF ( LOGSPACE )

註釋

事務日誌積累每個資料庫中數據的更改信息。由 DBCC SQLPERF(LOGSPACE) 返回的信息可用於監視使用的空間大小，並指明何時對事務日誌進行備份或截斷。

結果集

下表描述結果集內的列。

列名    定義
Database Name    資料庫名稱，為該資料庫顯示日誌統計信息。
Log Size (MB)    日誌的可用空間的實際大小。該大小比當初為日誌空間分配的要小，因為 Microsoft® SQL Server™ 保留一小部分磁碟空間用於內部頭信息。
Log Space Used (%)    事務日誌信息當前所占用的日誌文件的百分比。
Status    日誌文件狀態（總是包含 0）。

許可權

DBCC SQLPERF 對任何用戶預設許可權。

示例

下例顯示當前安裝的所有資料庫的 LOGSPACE 信息。

DBCC SQLPERF(LOGSPACE)
GO
下麵是結果集：

Database Name Log Size (MB) Log Space Used (%) Status      
------------- ------------- ------------------ ----------- 
pubs                1.99219            4.26471           0 
msdb                3.99219            17.0132           0 
tempdb              1.99219            1.64216           0 
model                   1.0            12.7953           0 
master              3.99219            14.3469           0 

sp_spaceused

顯示行數、保留的磁碟空間以及當前資料庫中的表所使用的磁碟空間，或顯示由整個資料庫保留和使用的磁碟空間。

語法

sp_spaceused [[@objname =] 'objname']
    [,[@updateusage =] 'updateusage']

參數

[@objname =] 'objname'

是為其請求空間使用信息（保留和已分配的空間）的表名。objname 的數據類型是 nvarchar(776)，預設設置為 NULL。

[@updateusage =] 'updateusage'

表示應在資料庫內（未指定 objname 時）還是在特定的對象上（指定 objname 時）運行 DBCC UPDATEUSAGE。值可以是 true 或 false。updateusage 的數據類型是 varchar(5)，預設設置為 FALSE。

返回代碼值

0（成功）或 1（失敗）

結果集

如果省略 objname，則返回兩個結果集。

列名    數據類型    描述
database_name    varchar(18)    當前資料庫的名稱。
database_size    varchar(18)    當前資料庫的大小。
unallocated space    varchar(18)    資料庫的未分配空間。

列名    數據類型    描述
reserved    varchar(18)    保留的空間總量。
Data    varchar(18)    數據使用的空間總量。
index_size    varchar(18)    索引使用的空間。
Unused    varchar(18)    未用的空間量。

如果指定參數，則返回下麵的結果集。

列名    數據類型    描述
Name    nvarchar(20)    為其請求空間使用信息的表名。
Rows    char(11)    objname 表中現有的行數。
reserved    varchar(18)    為 objname 表保留的空間總量。
Data    varchar(18)    objname 表中的數據所使用的空間量。
index_size    varchar(18)    objname 表中的索引所使用的空間量。
Unused    varchar(18)    objname 表中未用的空間量。

註釋

sp_spaceused 計算數據和索引使用的磁碟空間量以及當前資料庫中的表所使用的磁碟空間量。如果沒有給定 objname，sp_spaceused 則報告整個當前資料庫所使用的空間。

當指定 updateusage 時，Microsoft® SQL Server™ 掃描資料庫中的數據頁，並就每個表使用的存儲空間對 sysindexes 表作出任何必要的糾正。例如會出現這樣一些情況：當除去索引後，表的 sysindexes 信息可能不是當前的。該進程在大表或資料庫上可能要花一些時間運行。只有當懷疑所返回的值不正確，而且該進程對資料庫中的其它用戶或進程沒有負面影響時，才應使用該進程。如果首選該進程，則可以單獨運行 DBCC UPDATEUSAGE。

許可權

執行許可權預設授予 public 角色。

示例

A. 有關表的空間信息

下例報告為 titles 表分配（保留）的空間量、數據使用的空間量、索引使用的空間量以及由資料庫對象保留的未用空間量。

USE pubs
EXEC sp_spaceused 'titles'
B. 有關整個資料庫的已更新空間信息

下例概括當前資料庫使用的空間並使用可選參數 @updateusage。

USE pubs
sp_spaceused @updateusage = 'TRUE'

收縮資料庫文件或日誌文件命令：

DBCC SHRINKFILE

收縮相關資料庫的指定數據文件或日誌文件大小。

語法

DBCC SHRINKFILE
    ( { file_name | file_id }
        { [ , target_size ]
            | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] 
        }
    )

參數

file_name

是已收縮文件的邏輯名稱。文件名必須符合標識符的規則。有關更多信息，請參見使用標識符。

file_id

是要收縮的文件的標識 (ID) 號。若要獲得文件 ID，請使用 FILE_ID 函數或在當前資料庫中搜索 sysfiles。

target_size

是用兆位元組表示的所要的文件大小（用整數表示）。如果沒有指定，DBCC SHRINKFILE 將文件大小減少到預設文件大小。

如果指定 target_size，DBCC SHRINKFILE 將試圖將文件收縮到指定大小。將要釋放的文件部分中的已使用頁將重新定位到保留的文件部分中的可用空間。例如，如果數據文件為 10MB，則帶有 target_size 為 8 的 DBCC SHRINKFILE 將導致文件最後 2 MB 中所有已用頁重新分配到文件前 8 MB 中的任何可用槽中。DBCC SHRINKFILE 不會將文件收縮到小於存儲文件中的數據所需要的大小。例如，如果使用 10MB 數據文件中的7 MB，帶有 target_size 為 6 的 DBCC SHRINKFILE 語句只能將該文件收縮到 7 MB，而不能收縮到 6 MB。

EMPTYFILE

將所有數據從指定文件中遷移到同一文件組中的其它文件。Microsoft® SQL Server™ 不再允許將數據放在用於 EMPTYFILE 選項的文件上。該選項允許使用 ALTER DATABASE 語句除去文件。

NOTRUNCATE

導致將釋放的文件空間保留在文件中。

當與 target_size 一起指定 NOTRUNCATE 時，釋放的空間不會釋放給操作系統。DBCC SHRINKFILE 的唯一影響是將已使用的頁從 target_size 行上面重新定位到文件的前面。當未指定 NOTRUNCATE 時，所有釋放的文件空間返回給操作系統。

TRUNCATEONLY

導致文件中的任何未使用的空間釋放給操作系統，並將文件收縮到上一次分配的大小，從而減少文件大小，而不移動任何數據。不嘗試將行重新定位到未分配頁。如果使用 TRUNCATEONLY，將忽略 target_size。

註釋

DBCC SHRINKFILE 適用於當前資料庫中的文件。將上下文切換到資料庫，以發出引用該特定資料庫中文件的 DBCC SHRINKFILE 語句。有關更改當前資料庫的更多信息，請參見 USE。

所生成的資料庫不能比 model 資料庫更小。

使用 DBCC SHRINKFILE 以將文件減小到比其最初創建時更小。之後，文件的最小大小重置為新指定的大小。

若要刪除在文件中可能出現的任何數據，請在執行ALTER DATABASE 之前執行 DBCC SHRINKFILE('file_name', EMPTYFILE)。

要收縮的資料庫不必在單用戶模式下；收縮文件時，同時其他用戶可以在資料庫中工作。不必在單用戶模式下運行 SQL Server 以對系統資料庫進行收縮。

對於日誌文件，SQL Server 使用 target_size 以計算整個日誌的目標大小；因此，target_size 是收縮操作完成後日誌中的可用空間大小。之後，整個日誌的目標大小可以解釋為每個日誌文件的目標大小。DBCC SHRINKFILE 嘗試立即將每個物理日誌文件收縮至其目標大小。如果虛擬日誌中的所有邏輯日誌部分都沒有超出日誌文件的目標大小，該文件將成功截斷，DBCC SHRINKFILE 完成且不顯示任何消息。然而，如果虛擬日誌中的邏輯日誌部分超出目標大小，則 SQL Server 釋放儘可能多的空間併發出一條消息。該信息告訴您需要執行什麼操作來移動文件末尾超出虛擬日誌的邏輯日誌部分。執行完該操作後，可以重新發出 DBCC SHRINKFILE 命令以釋放剩餘的空間。有關收縮事務日誌的更多信息，請參見收縮事務日誌。

因為日誌文件只能收縮到虛擬日誌文件邊界，所以不可能將日誌文件收縮到比虛擬日誌文件更小（即使現在沒有使用該文件）。例如，可以將資料庫的 1 GB 日誌文件收縮到只有 128 MB。有關截斷的更多信息，請參見截斷事務日誌。有關確定虛擬日誌文件大小的更多信息，請參見虛擬日誌文件。

結果集

下表描述結果集內的列。

列名    描述
DbId    SQL Server 試圖收縮的文件的資料庫標識號。
FileId    SQL Server 試圖收縮的文件的文件標識號。
CurrentSize    文件當前占用的 8KB 頁數。
MinimumSize    文件可以占用的最小 8KB 頁數。這與文件的最小大小或最初創建時的大小相對應。
UsedPages    文件當前使用的 8KB 頁數。
EstimatedPages    SQL Server 估計文件能夠收縮到的 8KB 頁數。

許可權

DBCC SHRINKFILE 許可權預設授予 sysadmin 固定伺服器角色或 db_owner 固定資料庫角色的成員且不可轉讓。

示例

下例將 UserDB 用戶資料庫中名為 DataFil1 的文件收縮到 7 MB。

USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO

收縮資料庫中數據文件和日誌文件的命令：

DBCC SHRINKDATABASE

收縮指定資料庫中的數據文件大小。

語法

DBCC SHRINKDATABASE
    ( database_name [ , target_percent ]
        [ , { NOTRUNCATE | TRUNCATEONLY } ] 
    )

參數

database_name

是要收縮的資料庫名稱。資料庫名稱必須符合標識符的規則。有關更多信息，請參見使用標識符。

target_percent

是資料庫收縮後的資料庫文件中所要的剩餘可用空間百分比。

NOTRUNCATE

導致在資料庫文件中保留所釋放的文件空間。如果未指定，將所釋放的文件空間釋放給操作系統。

TRUNCATEONLY

導致將數據文件中的任何未使用的空間釋放給操作系統，並將文件收縮到上一次所分配的大小，從而減少文件大小，而不移動任何數據。不試圖重新定位未分配頁的行。使用 TRUNCATEONLY 時，忽略 target_percentis。

註釋

Microsoft® SQL Server™ 可收縮：

特定資料庫的所有數據和日誌文件。執行 DBCC SHRINKDATABASE。

一次一個特定資料庫中的數據或日誌文件。執行 DBCC SHRINKFILE。
DBCC SHRINKDATABASE 以每個文件為單位對數據文件進行收縮。然而，DBCC SHRINKDATABASE 在對日誌文件進行收縮時，看起來好像所有的日誌文件都存在於一個連續的日誌池中。

假設名為 mydb 的資料庫有兩個數據文件和兩個日誌文件。這些數據文件和日誌文件大小都為 10 MB。第一個數據文件包含 6 MB 數據。

對於每個文件，SQL Server 計算目標大小，即要收縮文件到的大小。當用 target_percent 指定 DBCC SHRINKDATABASE 時，SQL Server 計算的目標大小是收縮後文件中的 target_percent 可用空間大小。例如，如果指定按 target_percent 為 25 收縮 mydb。SQL Server 計算的該文件的目標大小為 8 MB（6 MB 數據加上 2 MB 可用空間）。因此，SQL Server 將任何數據從數據文件的最後 2 MB 中移動到數據文件前 8 MB 的可用空間中，然後對該文件進行收縮。

假設 mydb 的第一個數據文件包含 7 MB 的數據。若將 target_percent 指定為 30，則允許將該數據文件收縮到所要的可用百分比 30。然而，若將 target_percent 指定為 40，就不會收縮該數據文件，因為 SQL Server 不能將文件收縮到比數據當前占有的空間還小的程度。也可以從另一個角度考慮這個問題：40% 要求的可用空間 + 70% 完整的數據文件（10 MB 中的 7 MB）大於 100%。因為所要求的可用百分比加上數據文件占用的當前百分比大於 100%（多出 10%），所以任何大於 30 的 target_size 都不會收縮該數據文件。

對於日誌文件，SQL Server 使用 target_percent 以對整個日誌的目標大小進行計算；因此，target_percent 是收縮操作後日誌中的可用空間大小。之後，整個日誌的目標大小可以解釋為每個日誌文件的目標大小。DBCC SHRINKDATABASE 嘗試立即將每個物理日誌文件收縮到其目標大小。如果虛擬日誌中的所有邏輯日誌部分都沒有超出日誌文件的目標大小，則該文件將成功截斷，DBCC SHRINKDATABASE 完成且不顯示任何消息。然而，如果虛擬日誌中的邏輯日誌部分超出其目標大小，則 SQL Server 釋放儘可能多的空間併發出一條消息。該消息告訴您需要執行什麼操作來移動文件末尾超出虛擬日誌的邏輯日誌部分。執行完該操作後，可以重新發出 DBCC SHRINKDATABASE 命令以釋放剩餘的空間。有關收縮事務日誌的更多信息，請參見收縮事務日誌。

因為日誌文件只能收縮到虛擬日誌文件邊界，所以不可能將日誌文件收縮到比虛擬日誌文件更小（即使現在沒有使用該文件）。例如，可以將資料庫的 1 GB 日誌文件收縮到只有 128 MB。有關截斷的更多信息，請參見截斷事務日誌。有關確定虛擬日誌文件大小的更多信息，請參見虛擬日誌文件。

由 DBCC SHRINKDATABASE 計算的數據和日誌文件的目標大小始終不小於文件的最小大小。文件的最小大小是在文件最初創建時指定的大小，或是上一次用更改文件大小的操作（如用帶有 MODIFY FILE 選項的 ALTER DATABASE 或 DBCC SHRINKFILE）顯式設置的大小。例如，如果在執行 CREATE DATABASE 時，將 mydb 的所有數據和日誌文件大小都指定為 10 MB，則每個文件的最小大小都為 10 MB。DBCC SHRINKDATABASE 不能收縮任何小於 10 MB 的文件。如果使用帶有 MODIFY FILE 選項的 ALTER DATABASE 將其中的一個文件顯示增長到 20 MB，則該文件新的最小大小為 20 MB。若要將文件大小收縮到小於其最小大小，請使用 DBCC SHRINKFILE 並指定新大小。執行 DBCC SHRINKFILE 會將文件的最小大小更改為所指定的新大小。

在使用數據文件時，DBCC SHRINKDATABASE 有 NOTRUNCATE 和 TRUNCATEONLY 選項。如果為日誌文件指定這兩個選項，則將忽略這兩個選項。DBCC SHRINKDATABASE 不帶任何選項等價於 DBCC SHRINKDATABASE 帶有 NOTRUNCATE 選項，後面還要跟著帶有 TRUNCATEONLY 選項的 DBCC SHRINKDATABASE。

不論是否指定 target_percent，NOTRUNCATE 選項都執行 DBCC SHRINKDATABASE 的實際數據移動操作（包括將分配頁從文件的末尾移動到文件前面的未分配頁）。然而，文件末尾的可用空間不會返回給操作系統，文件的物理大小也不會更改。因此，如果指定 NOTRUNCATE 選項，數據文件好像沒有收縮。例如，假設再次使用 mydb 資料庫，該 mydb 有兩個數據文件和兩個日誌文件。第二個數據文件和第二個日誌文件的大小都為 10 MB。執行 DBCC SHRINKDATABASE mydb NOTRUNCATE 時，Microsoft SQL Server 將數據從數據文件的後面幾頁移動到前面幾頁中。而文件的大小仍然為 10 MB。

TRUNCATEONLY 選項將文件末尾的所有可用空間收回給操作系統。然而，TRUNCATEONLY 在文件內不執行任何頁移動操作。指定的文件只能收縮到上一次分配的大小。如果使用 TRUNCATEONLY 選項指定 target_percent，則忽略後者。

所生成的資料庫不能比 model 資料庫更小。

要收縮的資料庫不必在單用戶模式下；其他的用戶仍可以在資料庫收縮時對其進行工作。這也包括系統資料庫。

結果集

下表描述結果集內的列。

列名    描述
DbId    SQL Server 試圖收縮的文件的資料庫標識號。
FileId    SQL Server 試圖收縮的文件的文件標識號。
CurrentSize    文件當前占用的 8KB 頁數。
MinimumSize    文件可以占用的最小 8KB 頁數。這與文件的最小大小或最初創建時的大小相對應。
UsedPages    文件當前使用的 8KB 頁數。
EstimatedPages    SQL Server 估計文件能夠收縮到的 8KB 頁數。

說明  SQL Server 並不顯示未收縮的文件的行。

許可權

DBCC SHRINKDATABASE 許可權預設授予 sysadmin 固定伺服器角色或 db_owner 固定資料庫角色的成員且不可轉讓。

示例

下例將 UserDB 用戶資料庫中的文件減小，以使 UserDB 中的文件有 10% 的可用空間。

DBCC SHRINKDATABASE (UserDB, 10)
GO

sp_lock 進程號，打開資源管理器查看cpu使用狀況。

tab 非臨時表的x鎖的數量變化（過多，持續不減少 ，有問題，需要停止收縮），要是產生死鎖要及時停止收縮。

具體收縮步驟如下：

最後將文件末尾的所有可用空間釋放給操作系統（dbcc shrinkfile（'xxDB', xx,truncateonly））:

上述實例是收縮的數據文件，若是收縮日誌文件，則需以下操作：

首先更改資料庫的用戶恢復模式為簡單模式，之後進行收縮

--資料庫名稱為 test  
USE test  
   GO  
   ALTER DATABASE test SET RECOVERY SIMPLE WITH NO_WAIT  
   GO  
   ALTER DATABASE test SET RECOVERY SIMPLE     
   GO  
   USE test   
   GO  
--這裡的2根據 select * from sysfiles 來查詢 一般數據位1 日誌為2  

   DBCC SHRINKFILE (2 , xx, TRUNCATEONLY)  
--xx 為資料庫文件收縮到的大小
   GO  
   USE test  
   GO  
   ALTER DATABASE test SET RECOVERY FULL WITH NO_WAIT  
   GO  
   ALTER DATABASE test SET RECOVERY FULL    
   GO  

 此外（針對於不需要記錄日誌資料庫，也即是不需要恢復實時數據的庫，可以通過如下方式來減少日誌文件的增加，配合日誌文件收縮效果更佳）：

BACKUP LOG xxxDB TO DISK='nul' --nul並非null 的錯誤拼寫， nul可以被看成一個虛擬文件，所有寫入到 nul文件里的數據都被丟棄，對 SQL Server而言，nul 與其他真實存在的文件一樣， SQL SERVER會掃描所有活動日誌，將該日誌格式化後寫入 nul文件，數據寫入到nul文件後被操作系統丟棄，然後由操作系統返回確認信息給 SQL Server，然後將活動日誌標記為已日誌備份，該活動日誌所在空間可被 reuse。 --BACKUP LOG xxxDB  TO DISK='nul' 相當於正常備份日誌然後刪除日誌文件，並不破壞備份日誌鏈。