Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Error Log retention Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 9:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 8:38 PM
Points: 94, Visits: 411
Hi Guys,
Understand that by default SQL Server has 7 error logs.
The oldest error log records will be deleted each time after SQL Server restarts.

I'm looking for a solution that will retain the error logs for one year. (due to some security policy)
Increasing the number of error logs doesn't help much as I won't be able to determine how many times SQL Server will be restarted a year.

Anyone have solution to it on how can I have a rule to only remove error logs older than one year?

thanks

Post #1473427
Posted Sunday, July 14, 2013 9:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:00 PM
Points: 1,307, Visits: 1,785
Just untick the max number of error log files so they aren't recycled. Then create a SQL agent task that deletes log files older than 1 year using powershell or cmd.

Unticking the option generates this code:
USE [master]
GO
EXEC xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'
GO

Post #1473428
Posted Sunday, July 14, 2013 10:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 8:38 PM
Points: 94, Visits: 411
Hi Andrew,
thanks for ur help.
I'll take a look.

Understand that it can be set by:
under management -> sql server logs - right click - configure

How about for SQL Server Agent error logs?
I don't find any setting with regards to it.
It has a total of 10 logs. (while sql server error log has 7)

thanks
Post #1473433
Posted Sunday, July 14, 2013 10:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:00 PM
Points: 1,307, Visits: 1,785
Just looked that one up, unfortunately looks like it cannot be changed. You'd need to setup a SQL agent job to copy out the log files to a seperate location and delete older than 1 year.
Post #1473437
Posted Monday, July 15, 2013 12:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 8:38 PM
Points: 94, Visits: 411
thanks!

By chance, do u have any script for: SQL agent task that deletes log files older than 1 year using powershell
Post #1473463
Posted Monday, July 15, 2013 12:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:00 PM
Points: 1,307, Visits: 1,785
There's plenty of examples on the net for that, eg
http://www.howtogeek.com/131881/how-to-delete-files-older-than-x-days-on-windows/
Post #1473473
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse