Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)}}
share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Ouch! Didn't knew about the xp_enumerrorlogs and the information it returns. Seems to me that this is what I'm looking for. Will test tomorrow ASAP. As for the number of logs, for now we want to keep the number low and cycle them. But will discuss the option of moving the logs to other drive, indeed is a good solution, both for space and perfomance point of view. –  Yaroslav Feb 5 at 18:15
    
Worked without flaws, thanks. If you could point on how to move the old files once the number of files limit is reached, extra points ;) –  Yaroslav Feb 6 at 8:04
    
@Yaroslav not sure about the file moves, still learning powershell myself. EDIT: Found a link blogs.technet.com/b/heyscriptingguy/archive/2013/04/19/… –  Cougar9000 Feb 6 at 14:35
    
I get it. I'm too learning powershell, but still on the first steps. And yes, I can move the files manually, but if it is possible to do it automatically inside the job, even better. –  Yaroslav Feb 6 at 14:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.