--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

Delete Bakups older than 1 day in SQL Server

--Delete Bakups older than 1 day in SQL Server

DECLARE @DeleteDate datetime

 SET @DeleteDate = DateAdd(day, -1, GetDate()) 

  EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

   N'C:\Backup\msdb', -- folder path (trailing slash)

    N'bak', -- file extension which needs to be deleted (no dot)

     @DeleteDate, -- date prior which to delete 

     1 -- subfolder flag (1 = include files in first subfolder level, 0 = not) 

Generate and change all SQL databases to a different compatibility level - Alter SQL Compatibility Level

SELECT name, compatibility_level  

FROM sys.databases where compatibility_level not in(120) -- SQL Server 2012 compatibility_level is 120

---------------------------------

SELECT name, compatibility_level  

FROM sys.databases 

---------------------------

/* Start Of Code */

DECLARE @SQL VARCHAR(max)  = ''

,             @CompLevel int = 120 -- Need to change the compatibility_level as per SQL Server version

SELECT @SQL += 'ALTER DATABASE ' + quotename(NAME) + ' SET COMPATIBILITY_LEVEL = ' + cast(@CompLevel as char (3)) + ';' + CHAR(10) + CHAR(13)

FROM sys.databases

WHERE 

--COMPATIBILITY_LEVEL not in(120) 

--and 

 compatibility_level <> @CompLevel

PRINT @SQL

--EXEC (@SQL)

/* End Of Code */