数据库日志监控update.txt

监控SQL Server2000数据库

--The next two stored procedures are used for cleaning the log of Washing database
create procedure sp_backupdb
  @filepath sysname = null,
  @dbname sysname = null
with encryption
as
  if @filepath is null or len(@filepath) < 1 return 1
  if @dbname is null or not exists (select 1 from master..sysdatabases where name = @dbname) return 2
  declare @filename varchar(30), @devicename varchar(200)
  select @filename = replace((convert(char(8), getdate(), 112) +  convert(char(8), getdate(), 108)), ':', '')
  select @filepath = @filepath + '\' + @filename + '.thunder'
  select @devicename = 'washing backup at ' + @filename
  execute sp_addumpdevice N'disk', @devicename, @filepath
  declare @op nvarchar(3000), @retcode int
  select @op = ''
  select @op = @op + 'BACKUP DATABASE [' + @dbname + '] TO [' + @devicename + '] WITH  INIT, NOUNLOAD, NAME = ''' + @devicename + ''', NOSKIP, STATS = 10, NOFORMAT' + char(13) + char(10)
  execute (@op)
  select @retcode = @@error
  if @retcode <> 0 return @retcode
  select @op = ''
  select @op = @op + 'BACKUP LOG ['+ @dbname + '] TO [' + @devicename + '] WITH  NOINIT,  NOUNLOAD, NAME = ''' + @devicename + ''', NOSKIP, STATS = 10, NOFORMAT' + char(13) + char(10) 
  execute (@op)
  select @retcode = @@error
  if @retcode <> 0 return @retcode
go

create  PROC SP_ShrinkDB
(
	@i_iShrinkToSize INT = 1000
)
AS
	SET @i_iShrinkToSize = ISNULL(@i_iShrinkToSize, 1000)
	IF @i_iShrinkToSize < 50
		RETURN -5

	SET NOCOUNT ON
	IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
		RETURN -4--不必压缩

	BACKUP LOG washing WITH TRUNCATE_ONLY
	DECLARE @iShrinkTo INT
	SET @iShrinkTo = 20
	WHILE @iShrinkTo < 100
		BEGIN
			DBCC SHRINKDATABASE(washing, @iShrinkTo, TRUNCATEONLY )			
			IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
				RETURN 0
			SET @iShrinkTo = @iShrinkTo + 10
		END

	SET @iShrinkTo = 50
	WHILE @iShrinkTo < 100
		BEGIN
			DBCC SHRINKFILE (washing_log, @iShrinkTo)
			IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
				RETURN 0
			SET @iShrinkTo = @iShrinkTo + 10
		END

	IF NOT EXISTS(SELECT TOP 1 * FROM SYSOBJECTS WHERE [name] = 'VIRTUAL_LOG_FILE_WRAPPER')
		BEGIN
			CREATE TABLE VIRTUAL_LOG_FILE_WRAPPER (CHAR1 CHAR(4000))
			IF @@ERROR <> 0
				RETURN -1
		END

--	CREATE TABLE #temp1([Database Name] VARCHAR(100), [Log Size (MB)] INT, [Log Space Used (%)] INT, Status INT) 
--	INSERT INTO #temp1 exec('DBCC SQLPERF(LOGSPACE)')

	DECLARE @i INT, @j INT
	SET @i = 0
	WHILE (1 = 1)--???
		BEGIN
			BEGIN TRAN tranShrinkDB
			SET @j = 0
			WHILE @j < 100
				BEGIN
					INSERT VIRTUAL_LOG_FILE_WRAPPER VALUES ('VIRTUAL_LOG_FILE_WRAPPER')
					SET @j = @j + 1
				END
			IF @@ERROR <> 0
				BEGIN
					DROP TABLE VIRTUAL_LOG_FILE_WRAPPER
					ROLLBACK TRAN tranShrinkDB
					RETURN -2
				END
			SET @i = @i + 1
			DELETE FROM VIRTUAL_LOG_FILE_WRAPPER
			COMMIT TRAN tranShrinkDB
			IF @i % 100 = 0
				BEGIN
					IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
						BREAK
					BACKUP LOG washing WITH TRUNCATE_ONLY

					SET @iShrinkTo = 20
					WHILE @iShrinkTo < 100
						BEGIN
							DBCC SHRINKDATABASE(washing, @iShrinkTo, TRUNCATEONLY )			
							IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
								RETURN 0
							SET @iShrinkTo = @iShrinkTo + 10
						END
				
					SET @iShrinkTo = 50
					WHILE @iShrinkTo < @i_iShrinkToSize
						BEGIN
							DBCC SHRINKFILE (washing_log, @iShrinkTo)
							IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
								RETURN 0
							SET @iShrinkTo = @iShrinkTo + 10
						END
				

				END
		END
	DROP TABLE VIRTUAL_LOG_FILE_WRAPPER

	SET @iShrinkTo = 20
	WHILE @iShrinkTo < 100
		BEGIN
			DBCC SHRINKDATABASE(washing, @iShrinkTo, TRUNCATEONLY )			
			IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
				RETURN 0
			SET @iShrinkTo = @iShrinkTo + 10
		END

	SET @iShrinkTo = 50
	WHILE @iShrinkTo < @i_iShrinkToSize
		BEGIN
			DBCC SHRINKFILE (washing_log, @iShrinkTo)
			IF EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
				RETURN 0
			SET @iShrinkTo = @iShrinkTo + 10
		END

	IF NOT EXISTS (SELECT TOP 1 * FROM SYSFILES WHERE [name] = 'washing_log' AND [size] * (8192/1024) / 1024 < @i_iShrinkToSize)
		RETURN -3		

	SET NOCOUNT OFF

GO