SQLServer删除数据库特别耗时

在老库删除数据库的时候，如果选中到了选项"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