Here is a list of some of the most popular (and useful) free scripts and queries I’ve seen around the interweb. There are lot’s more and I’ll be adding them as I’m reminded of them.
On a request from twitter David created a list of the tools and scripts he uses as a PFE (Premier Field Engineer for Microsoft). This is an awesome list and I highly recommend taking a look!
These guys seem to be putting out a script a week these days but they are still some of the highest quality and amazingly useful. They are now open sourced!
- sp_Blitz A general health check of your instance.
- sp_BlitzIndex All you ever wanted to know about your indexes.
- sp_BlitzFirst Troubleshoot your slow server.
- There are probably a dozen of them now. You can go to the GITHUB page above to see the list!
- Paste the Plan Need to upload a plan to share with others? This is where you want to go.
- sp_WhoIsActive Want to know who working on your instance at the moment? This is it.
- Minionware The Midnight DBAs are producing an ever expanding set of DBA tools that are truly amazing. These tools are simple to install and configure but are extremely versatile. And best of all free! But be forewarned, they really like documentation. Hour after hour of video and hundreds of pages of documentation are available (so far).Thus far they have:
- Minion Reindex
- Minion Backup
- Minion Enterprise – This one isn’t free but I’ll mention it anyway because it’s part of the set.
- Minion CHECKDB
- SQL Server Backup, Integrity Check, and Index and Statistics Maintenance Need a place to start? Or to finish for that matter. Here it is.
- Wait statistics, or please tell me where it hurts This is an awesome blog entry on wait stats with a great query to tell you what your instance is waiting on.
- Statistics Parser This is a nice web based parser for the output of STATISTICS IO and STATISTICS TIME.
- SQL Generator Another great web tool from Richie, this one will help you build your PIVOT query for you. And if PIVOTs aren’t something that come naturally to you (they don’t for me) this can really save your bacon.
- sp_RestoreScriptGenie This is a very nice restore script generator. There is a T-SQL version and a PoSH version.
Note: It pulls the data from msdb so may not work in a DR situation.
- Get drive sizes using T-SQL or Powershell – I love these scripts since they not only include mounted drives but mount points as well.
- Shred the deadlock graph! – If you have to deal with deadlocks this script is a must. It shreds the graph into a table and can take inputs from a trace file, deadlock file, the system_health XE file target, a SQL Sentry deadlock collection or even the ring buffer.
- How’s My Plan? Analyze your query plan for possible problems.
Default Trace Queries
The default trace is running anyway, might as well make use of it. Just remember that it does overwrite itself over time (first in first out) so grab the data while it’s there.
- Permissions Changes – Use the default trace to track changes to permissions.
- Schema Changes – Use the default trace to track your schema changes.
- Check the difference between two pieces of text – This isn’t really database related but it’s a nice online tool you can use to compare two versions of a script.
- Where is my backup? This is a good query to find out where your backups are going.
- Looping through multiple servers in SSIS. I posted a three part loop through to create an SSIS package that can walk through multiple instances.
- Creating a comma delimited list in a query. Technically this is about the STUFF function but it has a great pattern for creating a comma delimited string from a query.
- Clean up your windows logins. The best method is in the comments.