DBAs and SQL Server Logs

SQL Server Recovery Time. As I mentioned in my post on SQL Server 2012’s Indirect Checkpoints, one of my favorite things about SQL Server is checking on how well it does in terms of meeting the Recovery Interval goal – something that you can check on in the logs by looking for how long it takes the ‘Recovery is complete’ informational message to show up in the logs after SQL Server starts up. But, more than being a mere morbid curiosity about whether or not SQL Server is able to pull off the goal of recovering on time, there’s real value in checking on how long it takes for recovery to complete – because as Paul Randal recently pointed out, this interval is NOT guaranteed. As such, if you’re regularly seeing situations where the recovery interval takes longer than expected, you should do additional investigation of the cause. In some cases that’ll just be ‘how the cookie crumbles’ because you MIGHT be ‘stuck’ with lots of large and long-running transactions. But, in my experience, situations like that should be very rare and should be a known- quantity in the sense that this shouldn’t really be a surprise to you (meaning that due-diligence has been done to try and decrease the scope, size, and duration of transactions). Otherwise, the key point in keeping an eye on recovery times is to help make sure you’re avoiding any surprises when it comes to your RTOs.

CHECKDB Entries. Another thing that’s helpful to check within the Logs (at/around startup) is entries for the last time a successful DBCC CHECKDB operation is run against each of your production databases. Or, stated differently, if you’re not seeing these at/around startup, then you’re missing some huge opportunities to pro-actively protect your data from corruption. 

Login Failures. Login failures typically come in two forms – either from ‘innocent’ end-users or applications that are having what should be temporary or fleeting problems, and situations where malicious users or applications are trying to brute-force their way into your box. For the most part, you don’t typically need to worry or care about ‘innocent’ users and apps failing to login. Instead, your primary point of interest should be to watch for repeated attempts to login within VERY short time periods – as seen in the screenshot below:And, this is especially true in cases where SQL Server authentication is being used and when you have a SQL Server that is internet facing.As such, I can’t recommend enough that you PAY ATTENTION to these kinds of events if you’ve got SQL Server Auth enabled on an internet facing server – because watching for brute-force attacks in your log is SADLY the only line of defense that you really have – and that’s only a line of defense after you’ve done everything outlined in my post on protecting yourself from brute-force attacks that are very efficient at trying to hack their way into SQL Servers using SQL Auth that are internet-facing.

Backups. Another key thing to keep an eye on within the SQL Server logs is reports on backups. Or, more specifically, what you’ll want to keep an eye out for would be any errors or warnings about potential backup failures. Accordingly (and especially if you’re on a multi-tenant system (or any system) with lots of databases and lots of transaction log backups), you might want to actually DISABLE informational messages about SUCCESSFUL backups – just as a means of cutting down the noise to signal ratio so that problems are easier to spot when skimming.

Errors and other Warnings. Many times errors and warnings that end up in SQL Server’s logs will end up in the  Windows Application Event log as well – which is why checking them can be such a beneficial task.  But, the short story here is that if something is ailing, running into security/connectivity problems, or is about to break (or has already done so), then the log is going to be one of your first lines of defense against such an occasion taking you by surprise. Or, in other words, if you’re regularly checking your logs and something is running into problems, then you’re less likely to let such a problem go days/weeks/months unnoticed.

Aggressive Workset Trimming. This one sucks, and it’s something you hopefully won’t see in your SQL Server logs. But, by the same token, it’s also a perfect example of something that you CAN see in your logs that will be indicative of big/ugly problems. And, the short story is that it’s possible for the OS to tell SQL Server to ‘give up’ huge/contiguous blocks of memory (typically for a driver or other new/spun-up process) that will, in turn, force SQL Server to page large amounts of memory to disk. Only, SQL Server is literally too good to use paged memory (and for good reason – it’d be tantamount to performance suicide). So, instead, SQL Server just kind of takes it in the shorts when this happens and performance drops into the toilet as you see messages about how “A significant part of sql server process memory has ben paged out” in the log at the same times when performance starts to tank. Happily, though, this is fairly easy to correct via the instructions outlined in this KB article. (Though, you’ll sadly have to jump through additional hoops with an additional Trace Flag to get those work-around steps to actually work if you’re NOT running Enterprise Edition (i.e., if you’re running into these trimming problems on SQL Server Standard Edition).