数据库同步工具
sqlserver,Mysql数据同步软件

缩小所有数据库的SQL Server事务日志

购买事宜请联系QQ:1793040

问题

我需要快速生成 T-SQL命令以缩小所有SQL Server用户 数据库日志文件或将单个数据库日志文件缩小到原始大小或尽可能小。您可以提供脚本吗?

我的解决方案涉及在名为dbo.usp_ShrinkAllLogsExcludeSysDBS的SQL Server数据库中创建T-SQL 存储过程,该过程将获取数据库名称的参数。该参数将默认为’%’,表示如果未指定数据库,则所有用户数据库都在范围内。

该过程使用VARCHAR(max)参数(@TSQLExec),以便动态执行T-SQL收缩语句(或语句块)。

为每个用户数据库执行的代码将包含CHECKPOINT语句和DBCC SHRINKFILE语句。在SQL Server数据库中发出手动CHECKPOINT语句将把当前内存中已修改的页(称为脏页)和事务日志信息从内存写入磁盘,并将该信息记录在事务日志中。

该 DBCC SHRINKFILE语句将收缩当前数据库的指定数据或日志文件的大小。在这种情况下,我将不使用任何目标数据库文件大小。如果未指定此参数,则DBCC SHRINKFILE语句会将文件大小减小到其创建大小。我不使用目标尺寸参数以避免任何错误。

该过程将sys.databases和sys.master_files系统视图结合在一起,以便为所有用户数据库生成CHECKPOINT和DBCC SHRINKFILE语句。它过滤系统数据库(DBID> 4),因为不建议将这些语句用于系统数据库。

SQL Server收缩数据库T-SQL代码

-================================================ ================================ -作者:Eli Leiba-创建日期:2020-03- 步骤名称:dbo.usp_ShrinkAllLogsExcludeSysDBS- 说明:此过程收缩所有用户数据库日志文件或特定用户数据库日志-======================== ================================================== ======= 创建过程dbo 。usp_ShrinkAllLogsExcludeSysDBS (@dbname SYSNAME = '%' )AS BEGIN





  


 
DECLARE @TSQLExec VARCHAR (MAX )= '' ; 设置NOCOUNT ON ;   

 
IF OBJECT_ID ('tempdb中..#温度' )IS NOT NULL DROP TABLE #TEMP   
        

CREATE TABLE #TEMP (DBNAME数据类型为sysname ,DBID INT ,logFileSizeBeforeMB 小数(15 ,2 ),logFileSizeAfterMB 小数(15 ,2 )); 

WITH FS
 AS (SELECT database_id的,TYPE ,SIZE * 8.0 / 1024 SIZE
     FROM SYS 。master_files
 )INSERT INTO #TEMP (DBNAME ,DBID ,logFileSizeBeforeMB )SELECT  
    姓名,database_id的,(SELECT SUM (SIZE )FROM FS WHERE TYPE = 1 AND FS 。

      
 

        database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
WHERE database_id > 4
AND NAME LIKE @dbname;
 
SELECT @TSQLExec = CONCAT (
  @TSQLExec,
  'USE [',
  d.NAME,
  ']; CHECKPOINT; DBCC SHRINKFILE ([',
  f.NAME + ']) with no_infomsgs;' ,
  Char (13),Char (10))
FROM sys.databases d,
     sys.master_files f
WHERE d.database_id = f.database_id
      AND d.database_id > 4
      AND f.type = 1
      AND d.NAME LIKE @dbname;
PRINT @TSQLExec;
EXEC (@TSQLExec);
 
WITH fs
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
UPDATE a
set a.logFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
FROM #temp a
内部连接系统。数据库DB 的一个。dbid = db 。database_id的
 WHERE database_id的> 4 AND NAME LIKE @dbname  

 
SELECT * FROM #temp ORDER BY dbname
 SET NOCOUNT OFF ; END ; 走 

以下是执行此存储过程的示例:

-收缩所有用户数据库的数据库日志USE master
 GO EXEC dbo 。usp_ShrinkAllLogsExcludeSysDBS 
 GO



-仅针对数据库Test2 USE master
 GO EXEC dbo 收缩数据库日志。usp_ShrinkAllLogsExcludeSysDBS 'Test2的' GO


这是针对所有数据库运行的结果。您可以看到数据库Test2的日志从8072MB增长到8MB,但其他日志没有更改,因为它无法进一步缩小日志文件。

输出

下一步

  • 您可以在主数据库中创建并编译此简单过程,并将其用作简化SQL工具来缩小所有用户数据库日志文件或缩小特定用户数据库日志。
  • 该过程已针对SQL Server 2014和2017进行了测试,但应适用于SQL Server 2005及更高版本。

未经允许不得转载:数据库同步软件|Mysql数据同步软件|sqlserver数据库同步工具|异构同步 » 缩小所有数据库的SQL Server事务日志

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

syncnavigator 8.6.2 企业版

联系我们联系我们