Missing SQL Agent job history?

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:

Default settings

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s