SQL SERVER DBA, Linux and Azure: --create a tample table to gather the information of error log files in SQL Server

--create a tample table to gather the information of error log files in SQL Server

 --create a tample table to gather the information of error log files

CREATE TABLE #ErrorLog
(
       Archieve INT,
       Dt DATETIME,
       FileSize INT
)
GO

INSERT INTO #ErrorLog
EXEC xp_enumerrorlogs
GO

--delete all the old log files if the size of all the log files is larger than 30GB
DECLARE @i int = 1;                                                  
DECLARE @Log_number int;
DECLARE @Log_Max_Size int = 40*1024; --here is the max size (M) of all the error log files we want to keep, change the value according to your requirement
DECLARE @SQLSTR VARCHAR(1000);

SET @Log_number = (SELECT COUNT(*) FROM #ErrorLog);

IF (SELECT COUNT(FileSize/1024/1024) FROM #ErrorLog) >= @Log_Max_Size
BEGIN
       WHILE @i <= @Log_number
              BEGIN
                     SET @SQLSTR = 'DEL C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG.' + CONVERT(VARCHAR,@i);
                     EXEC xp_cmdshell @SQLSTR;
                     SET @i =@i + 1;
              END
END

DROP TABLE #ErrorLog

No comments:

Post a Comment