• Alejandro Santana - Tuesday, March 6, 2018 1:44 PM

    Hello!

    I'm fairly new to SQL Server and i have been wondering if you guys masters, professionals who have been in the IT Field as DBA for years give me some kind of idea or ideas for reports and health checks for everyday routine.
    ¿What kind of health check you guys do everyday, weekly or monthly to your SQL Server Database?
    ¿What kind of reports you guys made to check for instant diagnostics besides the SQL Server Log?

    Sorry for the english if misunderstood something, my native language is spanish.

    Greetings and thank you for your time.

    Realistically, the first thing I check is my email. If things are automated or monitored with notifications sent for faillures, errors, etc you'd find those.
    While I'm checking my email, the next thing I check is if all of the instances are up and available, you can connect to them and if the databases are online. If you use a Central Management Server, that can be done with one query doing a multiserver query of sys.databases.
    Backups are incredibly important. You usually need to do refreshes from production to lower environments which can be automated and that provides a way to check the validity of backups. For those that's aren't refreshed, a monthly test restore is a good practice if feasible. 
    I also check windows, sql server and sql agent logs. With some work, that can be automated to check for errors - especially with Windows Event logs. It's a bit more of a challenge with the SQL Server/Agent logs but still doable for the most part. I still manually check them whenever I connect to an instance.
    Then we have a lot of jobs that look for issues/problems and send out email for things like failed jobs, long running jobs, checking the backups on disk to make sure they are there and the older ones are being deleted.
    A lot of people will use a central repository database of different metrics for different servers, databases and those can be checked with notifications sent out if needed - and that can be automated as well. We've used that central database to store all the information about the servers and databases we support - server specs, databases and what apps use them or the general usage, who are the contacts, specific notes for databases/servers - such as using fulltext, using service broker, replication, log shipping, CDC, etc just to get a quick overview without manually running a bunch of queries. Then we have SSRS reports pointing to those with several parameters so you can quickly just pull up a report for everything or certain servers or things used by certain apps. etc.
    Most places have a DBA database or something similar on their servers - it's a good idea to have something like that for DBA only procedures, functions and other things like that. It can also be used for capturing baseline metrics for that instance as well as the basic CPU, disk, memory usage, storage. You can compare the current metrics against your baseline. It's a good idea to setup alerts and notifications for the alerts.
    You want to automate as much as you can. And have baselines for different metrics. Many metrics mean nothing if you don't have baseline measurements to compare them to. You will need to know if something was always an issue or just an issue today or this week.
    Over time you will be able to figure out more of what to check and how often. There should be a lot of list of activities like the replies you have here if you do a search. I'd go through a lot of those to pick up things. Search on something like: DBA Checklist or DBA Daily Procedures.
    I know I've seen quite a few of them in the past. And if you do things all the time for a log while, you do things without even thinking about it. So everyone is going to forget some things they do regularly. That's why I'd recommend checking as many lists as you can - before you get sick of doing it 🙂

    Sue