Few weeks ago I posted a poll here asking for how often do you review your Error logs. As promised, here comes the follow up post:)
Unfortunately, 45.45% out of 11 people said “Never and I’ll open Error log only If I want to investigate something” . IMHO, this is not a good practice(Especially if you want to be an outstanding DBA). Please don’t get me wrong…Am not saying that you are not an awesome and outstanding DBA! ( Well, People call you a DBA because you are already outstanding 🙂 )
Okay…….let me say this guys, Start reviewing your Error logs daily as the first thing when you get into your cubicle/desk/office. Trust me, there will be lot of surprises(at least a few) based on number of Servers(Instances I should say) you manage. SQL Server Error log is really one of the best buddies a SQL DBA has got for free besides default trace 🙂 . I simply love these two things in SQL Server which provide so much useful information in case of investigating something(At no additional cost/overhead).
A simple real life use case: SQL error log records all the failed login attempts by default, if you keep an eye(or even Just glance over it) you should know if there are any unwanted/unsafe login attempts(Which are failed) hitting your SQL Instance. For example…I see quite a few times, incoming connections from a particular host attempting to connect as “sa”. Error Log records IP address of the machine and the time stamp of course. I can just do a NSLOOKUP and check with respective owner of the machine on what’s happening. This is just one useful scenario, there are tons of use cases which you can come up with!
Believe me guys, there will be lot of surprises for you and lot of things you’ll discover about your own SQL Server Instance which you don’t know, just by reviewing your error logs on daily basis for a week or two!
Most of the times, I hear people saying ” I am responsible for managing multiple Instances, I really don’t have time to get into each of my SQL Server and review error log. It will eat up my entire day“. Well, you really don’t have to do this. All you have to do is just setup a job and automate which basically reads all the error logs from all of your servers and generates a fancy report and sends you an email.(All you need is to just create a simple SSIS package and SSRS for fancy reports) or may be Powershell could help you.
If you just have handful of SQL Instances to manage, see this blog post.
Basically, This will allow us to be “Proactive” and not being “reactive”. Preventing something bad happening to your database as opposed to fixing will really make you stand tall in front of your management and your peers. Am not saying, by reviewing Error logs daily you can literally prevent any issue from being happening. But you will be definitely able to reduce number of “OOPS…” moments!
IMHO, Even though part of DBA job is to troubleshoot/fix database related issues, our prime time goal as a DBA should be trying to prevent those issues from occurring at the first place!
Let me know if you need any help automating this process. I will be glad to point you in the right direction.
Cheers! Happy friday 🙂
2 thoughts on “Importance of reviewing your SQL Server Error Logs.”
Please can you help me how to generate this report using SSIS And SSRS? I really need to be able to set up this in my present environment. I will appreciate any help in this direction
I hope this could help, Author explained how to do this using SSIS.