SQL Server實用技巧集（一）：掛起操作、收縮壓縮、轉移權限、備份驗證、修復、日志清理

常用維護指令速查

1. 解決安裝/補丁「掛起操作」

-- 刪除註冊表值
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
→ 刪除 PendingFileRenameOperations

2. 重建索引與收縮資料/日志

DBCC REINDEX
DBCC INDEXDEFRAG
DBCC SHRINKDB
DBCC SHRINKFILE

3. 壓縮資料庫

DBCC SHRINKDATABASE(dbname)

4. 轉移資料庫給新用戶（映射既有權限）

EXEC sp_change_users_login 'update_one', 'newname', 'oldname'

5. 驗證備份集完整性

RESTORE VERIFYONLY FROM DISK = 'E:\dvbbs.bak'

6. 修復損壞資料庫（單用戶模式）

ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs', REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

CHECKDB修復等級：

• REPAIR_ALLOW_DATA_LOSS：最徹底，可能遺失資料，含分配/取消分配頁、刪除損壞文字物件。
• REPAIR_FAST：輕量修復（如非叢集索引鍵），無資料遺失風險。
• REPAIR_REBUILD：含REPAIR_FAST + 重建索引等較耗時操作，無資料遺失風險。

僅做物理一致性檢查：DBCC CHECKDB('dvbbs') WITH NO_INFOMSGS, PHYSICAL_ONLY

日志清理兩法

法一：簡單模式收縮（定期執行）

1. EM → 資料庫屬性 → 選項 → 故障還原模式 → 簡單（SQL 7勾選trunc. log on chkpt.）。
2. 右鍵資料庫 → 所有任務 → 收縮資料庫 → 確定。
3. 完成後改回標準/完整模式（日志對異常恢復至關重要）。

或T-SQL：EXEC sp_dboption 'dbname', 'trunc. log on chkpt.', 'TRUE'

法二：手動截斷並壓縮日志檔（精確控制大小）

SET NOCOUNT ON
DECLARE @LogicalFileName SYSNAME, @MaxMinutes INT, @NewSize INT
USE 目標資料庫
SELECT @LogicalFileName = '資料庫_log', @MaxMinutes = 10, @NewSize = 1 -- 目標1MB

DECLARE @OriginalSize INT
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName
PRINT 'Original Size: ' + CAST(@OriginalSize*8/1024 AS VARCHAR) + ' MB'

CREATE TABLE DummyTrans (DummyColumn CHAR(8000) NOT NULL)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + DB_NAME() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE(@LogicalFileName, @NewSize)
EXEC(@TruncLog)

WHILE @MaxMinutes > DATEDIFF(MI, @StartTime, GETDATE())
  AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
  AND (@OriginalSize * 8 / 1024) > @NewSize
BEGIN
  SELECT @Counter = 0
  WHILE @Counter < @OriginalSize / 16 AND @Counter < 50000
  BEGIN
    INSERT DummyTrans VALUES ('Fill Log')
    DELETE DummyTrans
    SELECT @Counter = @Counter + 1
  END
  EXEC(@TruncLog)
END

PRINT 'Final Size: ' + CAST((SELECT size FROM sysfiles WHERE name = @LogicalFileName)*8/1024 AS VARCHAR) + ' MB'
DROP TABLE DummyTrans
SET NOCOUNT OFF

[未完待續]