1

I can not find my SQL Server logs for all days in the past. In only found for last 4 days. Is it because that I restarted the server and logs are dumped if the hosting server is restarted?

How can I keep entire logs?

I am using the GUI to view the logs.

1

You have to configure using SSMS GUI :

enter image description here

Make sure you have error logs set to manageable size.

Check MSDN : Configure SQL Server Error Logs and kb 2199578 for managing SQL Server error log.

EDIT:

Below is what we do in our PROD environments:

  1. Recycle error logs using master.sys.sp_cycle_errorlog with a SQL Agent job.
  2. If you need more than 99 days of log (since you mentioned in your comment - The maximum is 99 which will be reached very soon), you can use PowerShell to move the files to a safe location. I would doubt that you would need error logs past 99 days ! Just make sure that you keep them in a manageable size, else you it would be difficult for log viewer to open very large files.
  • I would add: create a SQL Agent job to cycle the ERRORLOG every day and set the number of errorlog files to the number of days you want to keep. Maximum is 99. The job should simply invoke master.sys.sp_cycle_errorlog. The same concept applies to the SQL Server Agent Errorlog – spaghettidba Nov 5 '15 at 16:39
  • The maximum is 99 which will be reached very soon . – Java Main Nov 5 '15 at 16:41
  • In around 99 days I would say. Do you need more than 99 days of ERRORLOG? I don't think so. In case, you can always copy the files elsewhere and keep them indefinitely. – spaghettidba Nov 5 '15 at 16:43
  • @spaghettidba was adding that info already. Thanks ! – Kin Shah Nov 5 '15 at 16:47
  • @JavaMain see my updates above. – Kin Shah Nov 5 '15 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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