How to collect TLOG usage status automatically ?

Yes , in SQLSERVER, we use "DBCC sqlperf(logspace)" to check transaction logfile status.
But , for example , we collect it every one hour and save result to a special named table. as record ?
That is not easy but still can.

Here is script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getsqlperf]
AS
BEGIN
DECLARE @SQL2 VARCHAR(MAX)
DECLARE @createtable VARCHAR(MAX)
DECLARE @inserttable VARCHAR(MAX)
DECLARE @inserttable2 VARCHAR(MAX)
DECLARE @@tablename2 VARCHAR(MAX)

SELECT @SQL2 = (select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''))
set @createtable='create table log'+@SQL2+' (dbname varchar(20),logSizeMB float,logSpaceUsedPct float,Status int);'
exec(@createtable)
set @inserttable='insert into log'+@SQL2+' EXEC ("DBCC SQLPERF(LOGSPACE)")'
set @inserttable2=(select replace(@inserttable,'"',''''))
exec(@inserttable2)
END
GO

That script can generate a table which record logfile usage status. If you execute that script you could see the result.

execute one time:

巴特西

How to collect TLOG usage status automatically ?

最新文章

热门文章