A few days ago, i ran into a really weird issue. On our clients server, the SQL Server Agent history was almost empty, and the history of the jobs was gone.
First thing i checked were the files themselves, located in a nonstandard directory. You should find them in program files\Microsoft SQL Server\MSSQL1%[name of instance]\MSSQL\Log. The file i found there were really small, 1 or 2 kb.
That’s not good! But why? It took some time figuring it out untill i remembered that one of the employees of the customer needed to do a check on a connection from SQL Server to anonther database platform. This was scheduled from SQL Server (because reasons) and ran every 10 seconds.
Wait, every 10 seconds. This generates a lot of logs. At that point i decided to check out the settings for the SQL Agent History:
It seems that this setting truncates logs as well. If you get more than a 1000 rows, it starts to clear out other logs. A job, or multiple jobs, that run every 10 secondes generate loads of rows.
After changing the settings, the server starts collecting log data again.
Thanks for reading, but i wonder, do you write logs to tables as well as to the default files? And what are your preferred settings? Let me know!
Reitse's blog 