Jobs for cycling error logs

Couple of weeks ago I asked this question Error logs various questions and got the answer I needed. Based on the answer and some readings I want to create the following jobs:

• cycle the error log once it reaches a size in Mb or expands to say 2 weeks

• move the oldest error log to a different location once we reach the limit of archived error logs files (now we have the default, 6)

On the answer to my previous question I got the powershell snippet I'm posting at the end to get the size of the error logs. But is the first time I will attempt to do something with powershell so I do not know even where to start with this. Any help to setup these jobs? Maybe powershell is not needed and can be done using CmdExec or tsql? Is not that I know CmdExec, just asking on other approaches. The best one would be tsql as I've more experience with it.

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("YOUR_SQL_SERVER_NAME")
$SqlServer.EnumErrorLogs() |
    Select-Object Name, @{Name = "SizeMB"; Expression = {[System.Math]::Round($_.Size/1024/1024,2)}}

If it were me I would create a SQL Agent job for cycling the error log based on size or date. Run it a few times a day and you can even set up alerts to notify if the error log was cycled.

CREATE TABLE ##Temptable 
(
[Archive #] tinyint,
[Date] datetime,
[Log File Size (Byte)] INT
)
insert into ##Temptable exec xp_enumerrorlogs
IF (
    SELECT [Log File Size (Byte)] from ##Temptable where [Archive #] = 0
    ) > 5242880 --5MB
    BEGIN
    Exec sp_cycle_errorlog
    END
drop table ##Temptable

You could also change the IF statement or add an OR statement to look at the date and cycle if it is older than 2 weeks or however long you want.

IF (
    SELECT [Date] from ##Temptable where [Archive #] = 1
   ) < GETDATE() -14

EDIT: Archive for the date based cycle needs to be the previous error log. Log 0 will always return current date, since it is active, duh!

As for moving them, you can keep up to 99 log files. Do you think your extended information needs will exceed 100 logs? If not, I would move the error log location to a disk that can hold 100 full logs and change the maximum number of logs.

StackExchange Database Administrators Q#58281, answer score: 2