在老库删除数据库的时候，如果选中到了选项"Delete backup and Restore History Information for databases"。发现删除操作要耗费很久的时间，甚至好几天。
查看一下正在执行的会话信息，看看数据库都在做啥：
SELECT s.session_id
,r.STATUS
,r.blocking_session_id 'Blk by'
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 'Wait Sec'
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 'Elaps Sec'
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
可以看到，数据库一直在执行下面的SQL语句：
CREATE PROCEDURE sp_delete_database_backuphistory @database_name sysname AS BEGIN SET NOCOUNT ON DECLARE @backup_set_id TABLE (backup_set_id INT) DECLARE @media_set_id TABLE (media_set_id INT) DECLARE @restore_history_id TABLE (restore_history_id INT) INSERT INTO @backup_set_id (backup_set_id) SELECT DISTINCT backup_set_id FROM msdb.dbo.backupset WHERE database_name = @database_name INSERT INTO @media_set_id (media_set_id) SELECT DISTINCT media_set_id FROM msdb.dbo.backupset WHERE database_name = @database_name INSERT INTO @restore_history_id (restore_history_id) SELECT DISTINCT restore_history_id FROM msdb.dbo.restorehistory WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id) BEGIN TRANSACTION DELETE FROM msdb.dbo.backupfile WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id) IF (@@error > 0) GOTO Quit DELETE FROM msdb.dbo.backupfilegroup WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id) IF (@@error > 0) GOTO Quit DELETE FROM msdb.dbo.restorefile WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id) IF (@@error > 0) GOTO Quit DELETE FROM msdb.dbo.restorefilegroup WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id) IF (@@error > 0) GOTO Quit DELETE FROM msdb.dbo.restorehistory WHERE restore_history_id IN (SELECT restore_history_id FROM @restore_history_id) IF (@@error > 0) GOTO Quit DELETE FROM msdb.dbo.backupset WHERE backup_set_id IN (SELECT backup_set_id FROM @backup_set_id) IF (@@error > 0) GOTO Quit DELETE msdb.dbo.backupmediafamily FROM msdb.dbo.backupmediafamily bmf WHERE bmf.media_set_id IN (SELECT media_set_id FROM @media_set_id) AND ((SELECT COUNT(*) FROM msdb.dbo.backupset WHERE media_set_id = bmf.media_set_id) = 0) IF (@@error > 0) GOTO Quit DELETE msdb.dbo.backupmediaset FROM msdb.dbo.backupmediaset bms WHERE bms.media_set_id IN (SELECT media_set_id FROM @media_set_id) AND ((SELECT COUNT(*) FROM msdb.dbo.backupset WHERE media_set_id = bms.media_set_id) = 0) IF (@@error > 0) GOTO Quit COMMIT TRANSACTION RETURN Quit: ROLLBACK TRANSACTION END
检查sys.dm_exec_requests,可以看到cpu和io都在忙着做delete操作。虽然gui端看上去是卡住了，后台还是一直在做删除操作的。
那么为何这么慢呢？
首要要明白msdb都存放了哪些数据。MSDB存储SQL Server代理任务、历史记录、日程表、数据库邮件、服务代理、备份和还原历史记录。如果不进行定期清理、没有常规的维护操作，MSDB 的大小可能会变得很庞大。默认情况下，SQL Server 不会清除历史表中的数据。主要有以下一些表：
·backupfile
·backupfilegroup
·backupmediafamily
·backupmediaset
·backupset
·restorefile
·restorefilegroup
·restorehistory
接下来看看msdb库的大小。检查发现msdb库已经有33GB大小了。查看库中各个对象的大小：
USE[msdb]
GO
SELECT
t.name TableName
,s.name SchemaName
,p.ROWS TotalRows
,CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalMB
,CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedMB
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 IN
('backupset','backupfile','backupfilegroup','backupmediafamily','backupmediaset','restorefile','restorefilegroup','restorehistory')
GROUP BY t.name, s.name, p.ROWS
ORDER BY TotalMB DESC, t.name
结果可以看到，与备份相关的表都很大了
如果表很大，可以看看每个月的历史记录。比如：
SELECT
DATEPART(YEAR,[backup_start_date]) BackupYear
,DATEPART(MONTH,[backup_start_date]) BackupMonth
,COUNT(*) BackupCount
FROM [msdb].[dbo].[backupset]
GROUP BY DATEPART(YEAR,[backup_start_date]), DATEPART(MONTH,[backup_start_date])
ORDER BY BackupYear, BackupMonth
那么如何删除备份和还原记录呢？
官方提供了两个存储过程来修建这些表的大小。一个(sp_delete_backuphistory)用于根据日期删除备份和还原记录；一个(sp_delete_database_backuphistory)根据数据库名删除备份和还原记录。
例如：
-- delete all backup/restore history prior to a specified date
use msdb
go
exec sp_delete_backuphistory '2023-12-04'
go
-- delete all backup history for a specific database
use msdb
go
exec sp_delete_database_backuphistory 'test'
go
在删除数据库的时候，如果同时选中了删除备份信息，就需用从上面这些表中删除指定库的对应记录。但是默认sqlserver没有给这些表创建合适的索引。从而导致在删除数据的时候，上面的存储过程执行特别慢。
查看一下msdb数据库上都有哪些索引：
SELECT t.name as [Table], ind.name as [Index], col.name as [Column], ind.type_desc [Index Type]
FROM sys.indexes ind
inner join sys.index_columns ic
on ind.object_id = ic. object_id and ind.index_id = ic. index_id
inner join sys.columns col
on ic.object_id = col.object_id and ic.column_id = col.column_id
inner join sys.tables t
on ind.object_id = t. object_id WHERE ind.is_unique = 0 and ind. is_unique_constraint = 0
and t.name in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 'restorehistory' ) ORDER BY t .name, ind.name
可以看到，只有这几个索引：
慢的问题，就可以通过在存储过程实用到的表列上新加一些索引来解决。比如：
/************************************************************************
* * Title: msdb index creation
* * Author: Geoff N. Hiten
* * Purpose: Index msdb database
* * Date: 12/12/2005
* * Modifications: *
************************************************************************/
use msdb
go
--backupset
Create index IX_backupset_backup_set_id on backupset(backup_set_id)
go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
go
Create index IX_backupset_media_set_id on backupset(media_set_id)
go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)
go
Create index IX_backupset_backup_start_date on backupset(backup_start_date)
go
--backupmediaset
Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)
go
--backupfile
Create index IX_backupfile_backup_set_id on backupfile(backup_set_id)
go
--backupmediafamily
Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
go
--restorehistory
Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
go
--restorefile
Create index IX_restorefile_restore_history_id on restorefile(restore_history_id)
go
--restorefilegroup
Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
go
检查一下是否有数据库已经被删除，但是备份记录还保存在msdb库中
use msdb
SELECT database_name AS [Database],
COUNT(backup_set_id) AS Orphans
FROM backupset
WHERE database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name;
以下是批量删除的脚本：摘自https://sqlsolutionsgroup.com/how-to-clear-backup-history/
USE [msdb]
GO
/* user variables */
DECLARE @DeleteRange NVARCHAR(100) = 'YEAR' /* HOUR,DAY,WEEK,MONTH,YEAR */
,@Retain INT = 3 /* same units as the @DeleteRange variable */
/* stuff we'll use later */
DECLARE @DATE DATE
,@loop INT
,@SQL NVARCHAR(MAX)
,@BatchStart DATETIME = GETDATE()
,@DeleteDate DATETIME
/* create log table */
IF OBJECT_ID(N'msdb.dbo.BackupDeleteLog') IS NULL
BEGIN
CREATE TABLE msdb.dbo.BackupDeleteLog
(
ID INT IDENTITY(1,1)
,BatchStart DATETIME
,LoopNumber INT
,DeleteDate DATETIME
,StartTime DATETIME
,FinishTime DATETIME
)
END
/* find the number of loops we need to do based upon @DeleteRange and @retain */
SET @SQL = N'SELECT @loopOUT = DATEDIFF(' + @DeleteRange + ', MIN(backup_start_date),GETDATE()) FROM [msdb].[dbo].[backupset]'
EXEC SP_EXECUTESQL @SQL, N'@DeleteRange nvarchar(100), @loopOUT int OUTPUT', @DeleteRange = @DeleteRange, @loopOUT = @loop OUTPUT
/* delete backup history in batches */
WHILE @loop >= @retain
BEGIN
SET @SQL = N'SELECT @DateOUT = DATEADD(' + @DeleteRange + ',-' + CONVERT(VARCHAR,@loop) + ',GETDATE())'
EXEC SP_EXECUTESQL @SQL, N'@DeleteRange nvarchar(100), @DateOUT datetime OUTPUT', @DeleteRange = @DeleteRange, @DateOUT = @DeleteDate OUTPUT
INSERT INTO msdb.dbo.BackupDeleteLog (BatchStart, LoopNumber, DeleteDate, StartTime)
SELECT @BatchStart, @loop, @DeleteDate, GETDATE()
--EXEC sp_delete_backuphistory @DeleteDate
SELECT @DeleteDate
UPDATE msdb.dbo.BackupDeleteLog SET FinishTime = GETDATE() WHERE LoopNumber = @loop AND BatchStart = @BatchStart
SET @loop = @loop - 1
END
/* check what we've just deleted */
SELECT * FROM msdb.dbo.BackupDeleteLog WHERE BatchStart = @BatchStart