Blog

Our free server troubleshooting tool, sp_Blitz®, just learned some new tricks. If you call it like this:

EXEC dbo.sp_Blitz
@OutputDatabaseName = ‘DBAtools’,
@OutputSchemaName = ‘dbo’,
@OutputTableName = ‘BlitzResults’

It will create the table DBAtools.dbo.BlitzResults (if it doesn’t already exist) and insert the results. The table includes columns for the server name and the check date, so you can query this table to see what’s changed since the last time you ran sp_Blitz®. Plus, there’s more:

Added new checks for disabled CPU schedulers (due to licensing or affinity masking), extended stored procedures, disabled remote access to the DAC, databases in unusual states like suspect or emergency, logins with CONTROL SERVER permission.

Easier readability – whenever anyone’s asked how to add checks, I’ve always just said, “Read the code.” Last week, I read the code with fresh eyes and realized it could use some cleanup and comments. I used Red Gate SQL Prompt to clean up the formatting, which is better but could still use some work. The code is nearing 4,000 lines, so I moved the old changes out of the proc and into a new online changelog. I put in some time to explain what the proc is doing, and that should help people reading it from the first time. I’ve also started an sp_Blitz® Documentation page where I’ll be explaining some of the more advanced uses.

New downloadable sp_Blitz® app for Windows – over and over, we heard from people that they wanted a quick way to generate a print-friendly copy of the sp_Blitz® results. Jeremiah worked with a developer and built our first app:

sp_Blitz® Windows App

sp_Blitz® Windows App

The app creates sp_Blitz® in TempDB on the server of your choice, executes it, shows you the results, and can output the results to PDF – complete with links to the explanations for each result.

We’ve got lots of ideas for the app, but we wanted to get it out there first and let you help drive it. If there’s something you’d like to see added or changed, leave a comment here or contact us. The whole point of this thing is to make your life easier. I wish this tool would have been available back when I was a production DBA struggling to figure out what was going on in my environment, so every little improvement helps. Hopefully this can save you from the stress I had back then. Get it from the download page and enjoy!

↑ Back to top
  1. FYI – in some cases when I click the More Info it doesn’t go to the corresponding page on your site. One example is a VLF warning – it goes to your Elevated Database Permissions page.

    Great like program though – love it!

  2. Pingback: Giving Credit Where Credit Is Due | The SQL Corner

  3. Pingback: Giving Credit Where Credit Is Due - SQL Server - SQL Server - Toad World

  4. Very nice! I like the additions to the script as well as the app.

  5. another release of wonderful tool. Really appreciate your sharing Brent and Team!. While others keep their gun, you share the secret sauce

  6. Love it!!!Love it!!!Love it!!!

  7. In v22 how does the database and table get built initially from

    EXEC dbo.sp_Blitz
    @OutputDatabaseName = ‘DBAtools’,
    @OutputSchemaName = ‘dbo’,
    @OutputTableName = ‘BlitzResults’

    With this code? (Below) It appears it already has to exist in order to be populated. Note: It is Monday morning and I may be missing something……..

    /* @OutputTableName lets us export the sp_Blitz® results to a permanent table */
    IF @OutputDatabaseName IS NOT NULL
    AND @OutputSchemaName IS NOT NULL
    AND @OutputTableName IS NOT NULL
    AND EXISTS ( SELECT *
    FROM sys.databases
    WHERE [name] = @OutputDatabaseName )
    BEGIN

    • Greg – the database has to already exist. We don’t create the database. That’s what the EXISTS check is for – checking the existence of the database. Keep reading in the code and you’ll see where the table gets created. It may make more sense on Tuesday. ;-)

  8. Awesome and very Handy!
    How about making the app more smart by introducing a little color coding(RED) when saved as a PDF, for very dangerous settings to get immediate attention? For Eg, if you find Auto_Shrink ON, Page_Verify option turned Off on any database….

    • Sreekanth – thanks, that’s an interesting suggestion. Right now I think we’ll keep it focused on black & white just to keep printing easier, though. Red printouts tend to look pretty bad. ;-)

  9. My company uses separate admin user accounts to connect to SQL Servers. This means that we must open SSMS and other tools by right clicking, then selecting “Run as different User”. This application does not seem to allow that. Is this still in the works? I’ll stick with the sp_Blitz script for now, which does work great, and I appreciate it!

    • Andrew – no, this isn’t something we’ll be implementing in the near term. You’ll be best off with the stored proc. Thanks for asking though!

      • Just want to “vote” for Andrew’s comment and say that v22 looks good, like the DAC control.

        I’m so looking forward to sp_blitzindex integrated in the app and the new feature where the app connects to the internets greatest sql script repository where all the MCM’s & MVP’s of the world upload their stuff, I then select the script I want to work with in the app and just fire away.
        Bang bang bang..

        Did I just got carried away? =D

        Thank you Brent Ozar Unlimited!

      • This is possible but you need to perform it in two parts. You will first need to launch ClickOnce (dfsvc.exe) as the admin user you are going to impersonate, and then secondly you can launch the deploy part with rundll32. Here’s what I did:

        (From the command line, doesnt need to be elevated)
        1. runas /user:domain\user “C:\Windows\Microsoft.NET\Framework64\v4.0.30319\dfsvc.exe”
        2. runas /user:domain\user “rundll32 c:\Windows\System32\dfshim.dll,ShOpenVerbApplication http://u.brentozar.com/sp_blitz/sp_Blitz.application

        You will get:
        “Attempting to start C:\Windows\Microsoft.NET\Framework64\v4.0.30319\dfsvc.exe as user “DOMAIN\adminuser” …

        followed by:
        Attempting to start rundll32 c:\Windows\System32\dfshim.dll,ShOpenVerbApplication http://u.brentozar.com/sp_blitz/sp_Blitz.application as user “DOMAIN\adminuser” …

        This worked perfectly for me. Good luck!

  10. Thank you all very much for working so hard on organizing all this stuff, and always pushing the quality bar a little higher. It’s a huge help to us everyday DBA’s.

  11. Hey Brent, I just wanted to let you know of a little flaw I found in the latest sp_blitz. This was my execution:

    EXEC [master].[dbo].[sp_Blitz]
    @CheckUserDatabaseObjects = 1,
    @CheckServerInfo = 1,
    @CheckProcedureCache = 1,
    @OutputType = ‘TABLE’,
    @OutputDatabaseName = ‘TestComlumbus’,
    @OutputSchemaName = ‘dbo’,
    @OutputTableName = ‘BlitzResults’

    It ran successfully, or so it seemed. I am in Chicago, I am running it remotely, to a server in New Jersey. I connected to them LogMeIn, and seemingly ran it successfully. I analyze the output a bit, and then I want to dig a little deeper, so I attempted a query directly to BlitzResults, in their TestColumbus database. No object found. I checked TestColumbus, no new tables were created. Then I saw the error in my syntax — It is ‘TestColumbus’, not ‘TestComlumbus’.

    I corrected the @OutputDatabaseName, it ran just fine. Blitzresults was created successfully.

    There was no error, the execution seemed to work just fine. Maybe just output something if/when somebody passes a non-existent database to @OutputDatabaseName.

    • Rebecca – yep, that’s actually by design. If you don’t pass in a database name, we don’t create the database, and we don’t bother with errors to return to users. It’s tough because I gotta draw the line between adding error messages vs adding features, and I usually go for the latter. Like you noticed, this is the kind of thing that’s fairly rare, and it’s easy for users to troubleshoot. Thanks though!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php