Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action

……………………………………………………………………………………………………………………………………………………………………………….

 Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action.”

 If you receive this error message, first make sure that the SQL Server Agent service is running. To do this, follow these steps:

……………………………………………………………………………………………………………………………………………………………………………….

1.Click Start, click Run, type Services.msc, and then click OK.


2.In the Services window, locate the SQL Server Agent service.

3.Make sure that the value of the Status column of the SQL Server Agent service is Running.

then 


EXEC sp_configure ‘show advanced’, 1;

RECONFIGURE;

EXEC sp_configure ‘allow updates’, 0;

RECONFIGURE;

EXEC sp_configure ‘Agent XPs’, 1;

RECONFIGURE;

GO

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