快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

缩小所有数据库的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 =’%’)

开始

DECLARE @TSQLExec VARCHAR(MAX)=”;
开启NOCOUNT;

如果OBJECT_ID(’tempdb ..#Temp’)不为空
删除表#temp

创建表#temp(dbname sysname,dbid int,logFileSizeBeforeMB十进制(15,2),logFileSizeAfterMB十进制(15,2));

与FS


SELECT database_id,TYPE,SIZE * 8.0 / 1024 SIZE
从sys.master_files

插入#temp(dbname,dbid,logFileSizeBeforeMB)
选择
名称,database_id,
(从fs的WHERE TYPE = 1和fs.database_id = db.database_id中选择SUM(SIZE))LogFileSizeMB
FROM sys.databases数据库
在哪里database_id> 4
和名称类似@dbname;

SELECT @TSQLExec = CONCAT(
@TSQLExec,
‘采用 [‘,
d.NAME,
‘]; 检查点; DBCC SHRINKFILE([‘,
f.NAME +’])和no_infomsgs;’ ,
字符(13),字符(10))
从sys.databases d,
sys.master_files f
哪里d.database_id = f.database_id
AND d.database_id> 4
AND f.type = 1
和d.NAME喜欢@dbname;
打印@TSQLExec;
EXEC(@TSQLExec);

与FS


SELECT database_id,TYPE,SIZE * 8.0 / 1024 SIZE
从sys.master_files

更新一个
设置a.logFileSizeAfterMB =(从fs WHERE TYPE = 1 AND fs.database_id = db.database_id中选择SUM(SIZE))
从#temp a
内部连接sys.databases db on a.dbid = db.database_id
在哪里database_id> 4
和名称类似@dbname

SELECT * FROM #temp ORDER BY dbname
设置NOCOUNT OFF;
结束;

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

-缩小所有用户数据库的数据库日志
使用大师

执行dbo.usp_ShrinkAllLogsExcludeSysDBS

-仅针对数据库Test2收缩数据库日志
使用大师

执行dbo.usp_ShrinkAllLogsExcludeSysDBS’Test2′

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

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

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040