none
DBCC SHRINKFILE 运行时间过长 RRS feed

  • 问题

  • 之前安装APEXSQL软件,在msdb中建立了三张相关的表来存放相关数据,然后APEXSQL一直跑,数据是一直往里面新增的,导致最终其中一张单表达到42g左右,从而msdb数据库达到42,949MB,现在所在的磁盘空间只剩下12%,大概是12304MB,周六用以下命令压缩msdb数据库,只压缩2G。

    DBCC SHRINKFILE(MSDBData,40960);

    但是运行了41个小时之后,还在运行,通过以下sql,查询的结果在后边,percent_complete一直是95.36671,不变。

    select t.text,r.status,r.command,databasename=DB_NAME(r.database_id),r.cpu_time,r.total_elapsed_time,r.percent_complete

    from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(r.sql_handle)t

    text status command databasename cpu_time total_elapsed_time percent_complete

    DBCC SHRINKFILE (N'MSDBData' , 40960) suspended DbccFilesCompact msdb 296 148690362 95.36671

    运行以下sql

    select * from master.sys.sysprocesses 
    where dbid=db_id('msdb') and spid<>@@spid

    spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch
    105 5824 0 0x0069 3662 SLEEP_TASK     4 1 343 17 2 2014-05-10 15:58:28.680

    2014-05-10 16:00:47.200

    该进程并没有被堵塞。

    目前该压缩命令还在跑,请问是什么情况?哪儿有问题么?

    2014年5月12日 1:33

答案

  • 挺好的

    Backup MSDB

    The first step is to do a full backup of MSDB.  You should always make a backup before you plan on doing any changes with a system database.  You should also have a plan to restore the system database just in case you have to implement it.

    To check what is using the space:

    USE [msdb]
    go
    SELECT object_name(i.object_id) as objectName,
    i.[name] as indexName,
    Sum(a.total_pages) as totalPages,
    sum(a.used_pages) as usedPages,
    sum(a.data_pages) as dataPages,
    (sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
    (sum ( a.used_pages) * 8 ) / 1024 as usedSpaceMB,
    (sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
    FROM sys.indexes i
    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
    GROUP BY i.object_id, i.index_id, i.[name]
    ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
    GO

    Disable all Agent Jobs

    Select * into msdb..DBA_Agent_jobs_Snapshot
    FROM msdb..sysjobs
    WHERE ENABLED = 1
    
    SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' 
    + cast(job_id as varchar(40)) + ''', @enabled=0'
    FROM msdb..DBA_Agent_jobs_Snapshot
    
    --Run output from above

    Shrink MSDB

    USE [msdb]
    GO
    --1GB was sufficient for this MSDB. 
    DBCC SHRINKFILE (N'MSDBData' , 1024)
    GO

    You can keep an eye on the compact status by running the following for your specific session:

    select percent_complete, * from sys.dm_exec_requests
    --session+_id of the shrink operation
    where session_id = 50

    Enable all Agent Jobs

    SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' 
    + cast(job_id as varchar(40)) + ''', @enabled=1'
    FROM msdb..DBA_Agent_jobs_Snapshot
    
    --Run output from above

    2014年5月15日 6:49

全部回复

  • Did you see blocking in the db? What's wait status of that session?
    2014年5月12日 2:00
  • select * from master.sys.sysprocesses
    where blocked>0

    执行以上sql后,没有任何数据返回。

    该session状态是 suspended,我上面的第一个sql 返回的结果有status的。

    2014年5月12日 2:26
  • Is apexsql session still running? Does table involved have clustered index? What's fragmentation of that table? How much free space in that file?
    2014年5月12日 3:05
  • apexsql 一直在跑,数据一直在往msdb中的相关表插入新的数据。相关表有三个,这三个都有聚集索引,在执行收缩命令之前已经重建索引了,所以索引碎片不高的,只有百分之3,现在索引碎片最高的是32.93,在msdb这个文件中,有99%的可用空间。因为之前我把其中一张单表达到42g左右的那张表truncate了,所以该表数据量目前不多的。
    2014年5月12日 3:34
  • DBCC SHRINKFILE for file ID 1 is waiting for the snapshot transaction with timestamp 22375174 and other snapshot transactions linked to timestamp 22375174 or with timestamps older than 902547848 to finish.

    数据库的错误日志有如上所示输出,这个应该怎么看?不知道被哪个session堵塞的。

    2014年5月12日 5:27
  • 您好,
    請參考:DBCC SHRINKFILE

    收缩操作被阻塞
    在基于行版本控制的隔离级别下运行的事务可能会阻塞收缩操作。 例如,执行 DBCC SHRINK DATABASE 操作时,如果在基于行版本控制的隔离级别下运行的大型删除操作正在进行中,则收缩操作将等到删除操作完成才会收缩文件。 出现这种情况时,DBCC SHRINKFILE 和 DBCC SHRINKDATABASE 操作会在第一个小时每五分钟将信息性消息(对于 SHRINKDATABASE 为 5202,对于 SHRINKFILE 为 5203)输出到 SQL Server 错误日志,之后每一个小时输出一次。 例如,如果错误日志包含以下错误消息,则会发生以下错误:
    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
    transaction with timestamp 15 and other snapshot transactions linked to
    timestamp 15 or with timestamps older than 109 to finish.
    这意味着收缩操作被时间戳早于 109 的快照事务阻塞,它是收缩操作所完成的上一事务。 它还说明 sys.dm_tran_active_snapshot_database_transactions 动态管理视图中的 transaction_sequence_num 或 first_snapshot_sequence_num 列包含值 15。 如果该视图中的 transaction_sequence_num 或 first_snapshot_sequence_num 列包含的数字小于收缩操作完成的上一事务 (109),则收缩操作将等待这些事务完成。
    若要解决此问题,请执行下列任务之一:
    终止阻塞收缩操作的事务。
    终止收缩操作。 如果终止收缩操作,则会保留任何已完成的工作。
    不执行任何操作,并允许收缩操作等到阻塞事务完成。


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    2014年5月12日 6:08
  • 上面那个我看过了,目前是我找不到堵塞收缩操作的事务。

    select * from master.sys.sysprocesses
    where blocked>0

    运行以上sql并没有发现堵塞的进程,所以目前不确定到底是哪个进程堵塞了这个操作。

    2014年5月12日 6:32
  • 给你一个脚本

    --自动生成收缩数据库文件脚本
    
    --设置要收缩的数据库
    USE [数据库名]
      ----★Do
    GO
    SET nocount ON
    CREATE TABLE #Data
        (
          ID INT IDENTITY(1, 1) ,
          DBNAME NVARCHAR(30) ,
          FileID INT NOT NULL ,
          [FileGroupId] INT NOT NULL ,
          TotalExtents INT NOT NULL ,
          UsedExtents INT NOT NULL ,
          [FileName] SYSNAME NOT NULL ,
          [FilePath] NVARCHAR(MAX) NOT NULL ,
          [FileGroup] NVARCHAR(MAX) NULL
        )
    
    INSERT  #Data
            ( FileID ,
              [FileGroupId] ,
              TotalExtents ,
              UsedExtents ,
              [FileName] ,
              [FilePath]
            )
            EXEC ( 'DBCC showfilestats WITH NO_INFOMSGS'
                )
    
    UPDATE  #Data
    SET     #Data.FileGroup = sysfilegroups.groupname ,
            [#Data].[DBNAME] = DB_NAME()
    FROM    #Data ,
            sysfilegroups
    WHERE   #Data.FileGroupId = sysfilegroups.groupid
    
    SELECT  ID ,
            DBNAME ,
            [FileGroup] ,
            'Data' FileType ,
            [FileName] ,
            TotalExtents * 64. / 1024 TotalMB ,
            UsedExtents * 64. / 1024 UsedMB ,
            [FilePath] ,
            FileID
    FROM    #Data
    ORDER BY [ID]
    
    
    
    DECLARE @i INT
     --用于循环
    SET @i = 1
    DECLARE @dbname NVARCHAR(100)
    DECLARE @filegroup NVARCHAR(200)
    DECLARE @filename NVARCHAR(200)
    DECLARE @fileid NVARCHAR(10)
    DECLARE @totalMB DECIMAL(20, 1)
     --总大小
    DECLARE @UsedMB DECIMAL(20, 1)
      --已使用大小
    DECLARE @CanshrinkSize NVARCHAR(100)
     --可收缩到的大小
    
    DECLARE @COUNT INT
      --保存#Data表的总行数值
    
    --获取#Data表的总行数
    SELECT  @COUNT = COUNT(*)
    FROM    #Data
    
    SELECT TOP 1
            @dbname = [DBNAME]
    FROM    [#Data] 
    PRINT 'USE [' + @dbname+']'
    PRINT 'GO'
    WHILE @i <= @COUNT
        BEGIN
            SELECT  @filegroup = [FileGroup] ,
                    @filename = [FileName] ,
                    @fileid = [FileID] ,
                    @totalMB = TotalExtents * 64. / 1024 ,
                    @UsedMB = UsedExtents * 64. / 1024
            FROM    #Data
            WHERE   [ID] = @i
            PRINT '--文件组:' + @filegroup + ';文件id:' + @fileid + ';总大小:'
                + CAST(@totalMB AS VARCHAR(100)) + 'MB;已使用大小:'
                + CAST(@UsedMB AS VARCHAR(100)) + 'MB;'
            SET @CanshrinkSize = CAST(CAST(@UsedMB + 1024 AS INT) AS NVARCHAR(100))
            PRINT 'DBCC SHRINKFILE ([' + @filename + '],' + @CanshrinkSize + ')'
                + '   --可收缩到的值为已使用的大小加1G' + CHAR(10)
            SET @i = @i + 1
        END
    
    DROP TABLE #Data
    


    我们都是这样做的,一般对大表进行分区,分区之后,当数据没有插入到能收缩的那个分区,那么我们就对该分区进行收缩

    又或者就算有数据插入到要收缩的那个分区但是已使用大小比较小,一般最后小于20MB,那么就算有数据插入,收缩也是很快的

    --文件组:FG_xxx_ClassId_03;文件id:5;总大小:501.0MB;已使用大小:1.3MB;
    DBCC SHRINKFILE ([FG_xxx_ClassId_03_data],1025)   --可收缩到的值为已使用的大小加1G

    如果没有分区,只有主文件组,并且已使用大小大于100MB,收缩也会非常慢

    我们对几TB的数据库也是这样做的

    2014年5月12日 8:12
  • 我把生产环境的msdb数据库备份到我本机,然后收缩,很快的,几分钟就收缩为200M。应该是在生产环境,有进程堵塞了收缩的操作,所以应该不是msdb数据库过大而导致收缩很慢。

    根据官网 http://msdn.microsoft.com/zh-cn/library/ms189493.aspx

    所写的,应该是以下日志导致的收缩命令一直在等待状态。

    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot transaction with timestamp 22375174 and other snapshot transactions linked to timestamp 22375174 or with timestamps older than 902547848 to finish.

    但是直接查以下sql,并没有发现任何堵塞。

    select * from master.sys.sysprocesses
    where blocked>0

    所以目前是哪儿堵了,还不确定。

    2014年5月12日 9:56
  • How many sessions in msdb on your server?
    2014年5月12日 14:12
  • SQLAgent - Generic Refresher                     1                                                                                              
    ApexSQL Log Connection Monitor                4                                                                                               
    SQLAgent - Job invocation engine                1
    DatabaseMail - DatabaseMail - Id<7880>     2                                                                                                                        

    目前没有收缩数据库,一共就8个session,program_name是上面4个。

    2014年5月13日 1:21
  • Try stop apexsql then shrink file.
    2014年5月13日 2:22
  • 按照rmiao大侠所说,先停一下ApexSQL 吧

    最好kill掉所有对msdb数据库的连接


    2014年5月13日 2:35
  • suspended 状态表示等待的是资源, 不是进程, 所以sysprocess查不到 blocked > 0 是正常的

    根据你提供的信息, 结合官网的说明

    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
    transaction with timestamp 15 and other snapshot transactions linked to
    timestamp 15 or with timestamps older than 109 to finish.
    这意味着收缩操作被时间戳早于 109 的快照事务阻塞,它是收缩操作所完成的上一事务。
    它还说明 sys.dm_tran_active_snapshot_database_transactions 动态管理视图中的 transaction_sequence_num 或 first_snapshot_sequence_num 列包含值 15。
    如果该视图中的 transaction_sequence_num 或 first_snapshot_sequence_num 列包含的数字小于收缩操作完成的上一事务 (109),则收缩操作将等待这些事务完成。

    你应该做下面的查询, 检查对应的操作是没有正常结束, 还是因为某些原因一直没有处理完成, 确保它们完成(或者强制终止)

    select * from sysprocesses
    where sid in(
    		select session_id from sys.dm_tran_session_transactions
    		where transaction_id in(
    				select transaction_id from sys.dm_tran_active_snapshot_database_transactions 
    				where transaction_sequence_num < 902547848
    					or first_snapshot_sequence_num < 902547848
    			)
    	)
    

    2014年5月13日 3:06
  • spid 误写成 sid 了

    select * from sysprocesses
    where spid in(
    		select session_id from sys.dm_tran_session_transactions
    		where transaction_id in(
    				select transaction_id from sys.dm_tran_active_snapshot_database_transactions 
    				where transaction_sequence_num < 902547848
    					or first_snapshot_sequence_num < 902547848
    			)
    	)
    
    

    2014年5月13日 3:09
  • @rmiao桦仔apexsql停掉了,收缩已经进行一个小时了,我只收缩100M,但是目前还是在运行。在错误日志中,以下错误已经没有了。

    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot transaction with timestamp 22375174 and other snapshot transactions linked to timestamp 22375174 or with timestamps older than 902547848 to finish.

    根据以下sql

    select * from master.sys.sysprocesses 
    where dbid=db_id('msdb') and spid<>@@spid

    跑出来三条记录,其中一条是收缩命令,另外两条的program_name和具体sql是:

    --SQLAgent - Generic Refresher  

    EXECUTE msdb.dbo.sp_help_alert @order_by = N'event_id DESC, severity ASC, message_id ASC, database_name DESC', @legacy_format = 1

    --SQLAgent - Generic Refresher  

    (@P1 int,@P2 uniqueidentifier,@P3 int)UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3

    请问以上这两个可以停吗?

    @邹建 根据您的sql,无论有没有停掉apexsql,跑出来的是另一个库的数据,跟msdb没有关系的。但目前apexsql停掉了,不再报上面那个错误了,应该跟这个没关系了吧。

    2014年5月13日 7:03
  • 现在msdb又出来一个进程,具体sql如下:

    msdb.dbo.sp_readrequest;1

    又开始报以下日志错误:

    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot transaction with timestamp 22375174 and other snapshot transactions linked to timestamp 22375174 or with timestamps older than 1066052358 to finish.

    根据邹大侠的sql,跑出来的session都是其他库的select语句。

    想问下msdb.dbo.sp_readrequest;1 这个是做什么用的呢?

    2014年5月13日 7:27
  • where transaction_sequence_num < 902547848
    or first_snapshot_sequence_num < 902547848
    是日志错误中,  with timestamps older than 1066052358 to finish这里面的数字

    sp_readrequest 从内容看, 应该是跟数据库邮件有关的东东

    2014年5月13日 10:24
  • 1. SQLAgent - Generic Refresher is system process, have to stop sql agent service to get rid of it. 

    2. regarding error '... is waiting for the snapshot transaction ...', it means shrink operation to be blocked by a transaction that is running under a row versioning-based isolation level. May need stop sql agent then shrink msdb file.

    2014年5月13日 13:31
  • 2014年5月15日 5:08
  • 挺好的

    Backup MSDB

    The first step is to do a full backup of MSDB.  You should always make a backup before you plan on doing any changes with a system database.  You should also have a plan to restore the system database just in case you have to implement it.

    To check what is using the space:

    USE [msdb]
    go
    SELECT object_name(i.object_id) as objectName,
    i.[name] as indexName,
    Sum(a.total_pages) as totalPages,
    sum(a.used_pages) as usedPages,
    sum(a.data_pages) as dataPages,
    (sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
    (sum ( a.used_pages) * 8 ) / 1024 as usedSpaceMB,
    (sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
    FROM sys.indexes i
    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
    GROUP BY i.object_id, i.index_id, i.[name]
    ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
    GO

    Disable all Agent Jobs

    Select * into msdb..DBA_Agent_jobs_Snapshot
    FROM msdb..sysjobs
    WHERE ENABLED = 1
    
    SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' 
    + cast(job_id as varchar(40)) + ''', @enabled=0'
    FROM msdb..DBA_Agent_jobs_Snapshot
    
    --Run output from above

    Shrink MSDB

    USE [msdb]
    GO
    --1GB was sufficient for this MSDB. 
    DBCC SHRINKFILE (N'MSDBData' , 1024)
    GO

    You can keep an eye on the compact status by running the following for your specific session:

    select percent_complete, * from sys.dm_exec_requests
    --session+_id of the shrink operation
    where session_id = 50

    Enable all Agent Jobs

    SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' 
    + cast(job_id as varchar(40)) + ''', @enabled=1'
    FROM msdb..DBA_Agent_jobs_Snapshot
    
    --Run output from above

    2014年5月15日 6:49