MyClearLog.sql

出自ProgWiki
(重新導向自SQL:StoredProcedure:MyClearLog
跳至導覽 跳至搜尋

用途

  • 資料庫的Log清空

程式碼

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[MyClearLog] (
	@db AS nvarchar(128) = null
) AS
BEGIN
 
	SET NOCOUNT ON;
 
	DECLARE @GetLogNameCmd as nvarchar(255);
	DECLARE @ClearLogCmd as nvarchar(255);
	DECLARE @ParmDefinition as nvarchar(255);
 
	DECLARE @DatabaseName as nvarchar(128);
	DECLARE @LogName as nvarchar(128);
 
	DECLARE @datestr NVARCHAR(255)
	SET @datestr = CONVERT(NVARCHAR, GETDATE(), 112)
 
 
	if (not @db is null) and (@db<>'')
		begin
			--單筆Log清除模式
 
			set @DatabaseName = @db;
			set @LogName = '';
 
			--抓@LogName
			SET @GetLogNameCmd = 'SELECT top 1 @Name=a.name FROM ['+@DatabaseName+'].[sys].[database_files] as a where a.type=1;';
			SET @ParmDefinition = N'@Name nvarchar(128) output';
			EXEC sp_executesql @GetLogNameCmd, @ParmDefinition, @LogName output;
 
			--print @GetLogNameCmd +char(13);
			--print @LogName +char(13);
 
			if (not @LogName is null) and (@LogName<>'') 
			begin
				--清Log
				SET @ClearLogCmd ='USE '+@DatabaseName +';'+char(13)
							+'BACKUP LOG '+@DatabaseName+' TO DISK = N''D:\Backup\DBLog\'+@DatabaseName+'\'+@DatabaseName + '_'+@datestr + '_log.bak'';'+char(13)
							+'DBCC SHRINKFILE (N''' +@LogName +''', 1);'+ char(13);
 
				--print @ClearLogCmd +char(13);
				EXEC sp_executesql @ClearLogCmd;
 
			end
		end
	else
		begin
			--所有的User資料庫的Log清除模式
			DECLARE db_cur cursor FOR
			select a.[name] 
				from master.sys.databases as a
				where a.owner_sid <> 0x01 
				order by a.[name];
 
			OPEN db_cur
			FETCH next FROM db_cur INTO @DatabaseName
				WHILE @@FETCH_STATUS = 0
					BEGIN
 
						set @LogName = '';
 
						--抓@LogName
						SET @GetLogNameCmd = 'SELECT top 1 @Name=[name] FROM ['+@DatabaseName+'].[sys].[database_files] as a where type=1;';
						SET @ParmDefinition = N'@Name nvarchar(128) output';
						EXEC sp_executesql @GetLogNameCmd, @ParmDefinition, @LogName output;
 
						if (not @LogName is null) and (@LogName<>'') 
						begin
							--清Log
							SET @ClearLogCmd ='USE '+@DatabaseName +';'+char(13)
							+'BACKUP LOG '+@DatabaseName+' TO DISK = N''D:\Backup\DBLog\'+@DatabaseName+'\'+@DatabaseName + '_'+@datestr + '_log.bak'';'+char(13)
							+'DBCC SHRINKFILE (N''' +@LogName +''', 1);'+ char(13);
 
							--print @ClearLogCmd;
							EXEC sp_executesql @ClearLogCmd;
						end
 
						FETCH next FROM db_cur INTO @DatabaseName
					END
			CLOSE db_cur
			DEALLOCATE db_cur
 
		end
END

用法

  1. 資料夾 D:\BACKUP\DBLog\ 需在「安全性」加入「NT Service\MSSQLSERVER」的完全控制
  2. 使用 SQL Agent 自訂維護計劃,在資料庫使用的離峰時間進行清理Log