SQL Server DBA Top 10 automation tasks

SQL Server Automation is one of the best ways for a DBA to achieve more in less time. If a task is repeatable – then why run it manually?

A word of warning!  There are automated jobs out in the SQL Server world that are causing more damage then help. I have seen plenty of Daily Shrink Database Jobs – that have done nothing but cause problems.

SQL Server Agent is a great framework for scheduling and managing jobs- but there are other schedulers . In larger environments I’ve used AutoSys  and  cron jobs (for DB2 management)  for scheduling. I also use Powershell – which lends itself to SQL Server 2005 , SQL Server 2008 , and also other aspects of the stack , such as Windows

Which ever method used , reporting is key to managing the jobs – is it a success or not?

I try and automate:

Backups

Index maintenance

DBCC CheckDB

Daily Health Checks

Disk Space

Exception based checked

Check keywords in SQL Server Logs such as “Error” - Read SQL Server - Powershell and SQL Error Logs

Powershell SQL Server Security Audit

SQL Server BackUp Review with Powershell

Failed SQL Server Agent Jobs -Read SQL Server - Powershell - List Failed SQL Server Jobs

 You can display some great value as a DBA by automating . The automation mentality allows you to scale very well. When organising automation jobs – consider the consequences for security. Be careful to not allow to many privileges.

Read More

Window Perfmon scripting, SQL Server perfmon and how to perfmon

SQL Server - Top 10 DBA mistakes