seven jobs you should be running #sqlsat126
TRANSCRIPT
Seven SQL Agent Jobs You Should Be Running
Mike Hillwig Boston, MA
Mike Hillwig
• AKA The Cranky DBA • SQL Server DBA • Working with SQL Server since SQL 7 • Senior DBA at hosFng division of a financial soIware company
• Know just enough Oracle to be dangerous • Resume includes Acme Packet, Shawmut Design and ConstrucFon, Equitable Resources
Photo Credit: Nedra Isenberg
`
Obligatory Social Networking Slide
• crankydba.com • sqlserverpedia.com • twiVer.com/mikehillwig • anysocialmediasite.com/mikehillwig
Housekeeping
• Slides and scripts at sqlsaturday.com • Blog post at crankydba.com/go/7jobs • Please complete your evaluaFons • Don’t let me talk too fast
Beware of the Blogs
• There is some amazing advice out there. But…
• Anybody can put bad advice on the internet
• Trust people you know • Assume I’m an idiot
• Test everything in your own test environment first.
• I don’t trust people who say “ALWAYS” or “NEVER”
WITFM?
Who are YOU?
• Windows admin turned accidental DBA • Junior DBA learning to monitor your environment
• Mid-‐Level DBA who just inherited a bunch of servers and need a sanity check
• Senior DBA who needs a framework for monitoring servers
• Any DBA doesn’t have monitoring tools
WHAT’S IN THIS FOR ME?
Windows Server Admins
• A beVer understanding of this SQL Server Agent thingie
Junior DBAs
• Ready-‐to-‐use scripts that you can deploy in your environment
More Senior DBAs
• Framework for building your own alerts
What is this SQL Agent thingie?
• SQL Server’s tool for automaFng processes • Allows you to schedule things to run – SQL Scripts – Windows executables – WMI scripts
• Can even watch for certain errors or situaFons and take acFon
What are we doing with it?
• Simple cause and effect • If you see something, say something • Run a query that looks for problems • If problem is found, call for help
Spam vs Mail
• Today’s examples send mail • In a perfect world, you’d log a Fcket with your support system
• But for the sake of simplicity in our scripts, we’re sending mail today
• Make sure your SQL Agent can send mail – Enable Mail XPs
What Seven Things Cause Our Dogs to bark?
1. Seeing themselves in the mirror 2. Autoshrink enabled. 3. Databases never backed up or no full backup
in X hours 4. No transacFon log backup in X hours 5. Default File Growths 6. Databases with datafiles that don’t grow 7. Failed jobs in the past 24 hours
Enough Talk
• Lets demo!