Are the SQL Server "Error Logs" (residing under SQL Server Agent) backed up?

Question

Are the SQL Server "Error Logs" (residing under SQL Server Agent) backed up?

Express12

SSCoach

Points: 18514
More actions
February 4, 2021 at 11:25 am

#3840893

Are the SQL Server "Error Logs" (residing under SQL Server Agent) backed up? (eg. when the master or msdb DB's are backed up)?
We have a requirement to provide recoverability for these logs in the event a hacker should gain access and clean their trail.
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Nope, not unless you do it yourself in some way.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Express12 SSCoach Points: 18514 More actions · Answer 2

thx Scott.. yep, I now understand the location for the SQL “Error Logs” -- which is established at SQL install time (99% of the time we use the default location of C:\Program Files\Microsoft SQL Server\MSSQLversion…\MSSQL\Log\) for over 200 instances we have..

I'll need to develop a separate backup strategy to copy the error log files off at specific intervals -- maybe once a day. (too bad our Ola H solution doesn't do that) I've been doing SQL Admin for quite a while but never looked into the location of the 'Error Logs' .. Learn something new everyday! thx again.

BT

MercurialBoy Grasshopper Points: 11 More actions · Answer 3

By itself, it is not saved, but you can find special services and applications that will save everything automatically.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Instead of farting around with files, build a database to hold the error logs in tables and do something like what the fellow at the following link did.

https://www.mlakartechtalk.com/how-to-load-sql-server-error-log-into-table-for-analysis/

You could use one table or many tables. Pick your poison.

The cool part then is that it could be backed up like any other database and it will be much easier to do analysis of various errors. For example, you could do a pretty slick deadlock report, which is particularly useful when you make an improvement to fix some deadlocks (did it actually work?) or clearly demonstrate that deadlocks went up after a given code release, etc, ad infinitum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)