When it is necessary to provide reports on activity occurring on the server, it pays to do a little prep work. Be Prepared. It is not an easy task to be able to go back in time and report on data that isn’t captured. The little prep work that one may need to do is well worth the effort in the end.
Very much like what you may find with the airline industry, the black box recorder in SQL Server is not just a single method (device) implemented to capture all of the desired data points.
Despite the controls in place there always seems to be some change that is unknown or even some cowboy that makes changes as s/he sees fit and forgets to notify people or follow process.
In this article I introduce you to some basics in regards to default behaviors and settings in tempdb along with some best practices. It is advisable to investigate from time to time some of these recommendations and confirm what we are really being told so we can avoid confusion and mis-interpretation.
In this article I will explore altering the default logs directory location. Some may say this is no big deal and you can just use the default location used during install. Fair enough, there may not be massive need to change that location
With the evolution of SQL Server, there is an evolution in Extended Events. New features in SQL Server also means there may be some additional default sessions.
A database can be offline at the time that you take over responsibility of the database. You will be asked when it was taken offline. This script will help you get a reasonable deduction for that offline event in the absence of better data.
The diagnostics process will trap various server related health (diagnostics) information related to the SQL Server instance in an effort to try and detect potential failures and errors.
In this article, the focus will be directed to the next leg of the black box recorder – or the system_health Extended Event Session.
I have personally participated in quite a large number of these TSQL Tuesday events – despite not being every single one. This is a roundup of my personal participation across the years.