Hello There ,
Did you ever thought of durability of the SQL Error Log and Archived versions it keeps for analysis and diagnostics. In default SQL provides 6+1 archives i.e n+1 (n is always no of archives+1 is always existing one) so here is the caveat . For some this default works very well but what about the heavy OLTP systems with multiple jobs running and log will be very busy collection data where about of the server. in this scenario log keeps rolling very fast and when ever there are trace flags on.
so here in this blog i am going to explain you about the tweaks we can make to the Error Log which saves tons of time and effort during analysis of any serious event.
In earlier versions of SQLServer till 2008r2 we can tweak the No.of versions of Log and manually executing the query sp_cycle_errorlog to keep the sanity by archiving it. But since SS2012 this tweaking has been more elevated way and even MS gave us DBA’s a room to make changes to the size it can grow, Yes that’s right we can restrict the size of error log. And i will also provide you the blog references.
So here are the things i am going to explain you,
1. Change the no.of Archives of Error Log.
2. Add cycling agent job to manual archive.
3. Change the size of the error log.
Chage of no.of Archives …
This is actually a registry change which we make through T-SQL instead of manually doing it by opening the regedit.