(update 2013/02/09: for some reason, running with only 6 threads doesn't bring back all the servers. Using 20, however, does. Odd, and not expected, and obviously I need a better fix)

(update 2013/02/08: if results from sp_blitz aren't saved for a particular server, run the invoke-sqlcmd2 command for sp_blitz against just that server, manually. For sp_blitz we've found a couple bugs and are sending them to Brent Ozar to fix. Right now, if there are any errors thrown, even if there are results, then invoke-sqlcmd2 doesn't work right.)

So, I recently found out WHY sp_blitz is awesome, and why "30 second takeover" is a horrid subtitle for it. (I always thought that it meant "break into a SQL server in 30 seconds", so I never went - NOPE).

Anyhow, sp_blitz itself is handy as heck - gets a list of common problems that a particular server has, when it gets handed to you and you basically "takeover" and become its DBA in a matter of 30 seconds.

So, the next thing I wanted to do was save the results to a centralized table. Actually raced a coworker to do it - he did one in SSIS, naturally I wanted it in Powershell. Later we plan to do "server thunderdome" and see whose runs fastest.

NOTE: this is almost exactly the same code as in my earlier post http://thebakingdba.blogspot.com/2012/12/powershell-run-query-against-multiple.html . The biggest change is the addition of a ServerName field (which was a lot harder to figure out than you'd think, so many thanks to Chad Miller for the fix, and Graimer for a shorter version). Given it wasn't a simple thing (for me) to add, and what I'm doing with it, I figure it'd be worth another post.

Future changes: I think I'm going to modify it so it looks at a folder, running each script from that folder and saving it to a table named for that script. Also getting it to return errors; between invoke-sqlcmd2 and the scriptblock, it basically eats any errors and you never see them. Need to get that working better.

2 comments:

Crazy useful, and I love that you're taking submissions for new things to check. We've already found one bug for either Sharepoint or Reporting Services, where the database name is longer than 50. Coworker should be mailing it to you.