This is a scary situation where you lost your SQL Server log. So a basic question is how this can happen and how as DBA you can prevent it?
Well, to answer first part (ie. How this can happen), we can consider a very simple but highly possible situation. SQL Server restarted multiple times in a short span of time. We know with each restart, we lose 1 oldest log. So theoretically if SQL Server restarts 6 times, we have a potential to lose all the logs before the first restart. Believe me, I saw this situation where DBA struggles to find a root cause of an issue as in a span of 1 hour, his Server restarted 6 times and he started his investigation next day with no log!
Next part is how to handle it. This is also a fairly simple thing. SQL Server is powerful enough to allow you to save the logs for future. Simplest and smartest way I can think is to dump your vital server’s log to a table. This will ensure that you will not lose the log no matter how many times SQL restart. This will also enable you to read the log using TSQL which will help you to play with data assuming you are comfortable with TSQL.
Now the last question what is the cost if you want to put the log in a table? As you probably guessed, this approach of putting logs in a table comes with a complex price of cost. You need to decide how much data you need to save and then do a capacity planning as the table where you will keep logs will grow with time. Also I recommend you should plan for an archival strategy too.
Now probably you want to have a direct working solution to get your hand dirty and to try this solution If so, click here for your answer…