sp_Blitz® v22: Output Results to a Table, Better Comments, and an App

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.

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!

Previous Post
Indexing Wide Keys in SQL Server
Next Post
Monitoring SSD Performance

34 Comments. Leave new

  • 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!

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

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

    Reply
  • Love it!!!Love it!!!Love it!!!

    Reply
  • 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

    Reply
    • 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. 😉

      Reply
  • sreekanth Bandarla
    May 13, 2013 4:32 pm

    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….

    Reply
    • 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. 😉

      Reply
  • 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!

    Reply
    • 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!

      Reply
      • 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!

        Reply
      • Steve Rhodes
        May 13, 2014 4:05 am

        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!

        Reply
  • 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.

    Reply
  • 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.

    Reply
    • 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!

      Reply
  • Please share the link of sp_blitz app really would like to test in our enviroment.

    Reply
  • John Bevilaqua
    December 8, 2017 11:12 am

    How about outputting the results of sp_WhoBlitz to a table as well so we can track issues every 5 minutes during a debugging cycle? Can someone run this for me on SQL 2014 or above to cast the table?
    DECLARE @tsql nvarchar(max);
    DECLARE @params nvarchar(max);
    DECLARE @browse_information_mode tinyint;
    SET @tsql = N’EXEC dbo.sp_BlitzWho;’
    SET @params = NULL;
    SET @browse_information_mode = NULL;
    DECLARE @cmd nvarchar(max);
    SET @cmd = ”;
    SELECT @cmd = CASE WHEN @cmd N” THEN @cmd + N’
    , ‘ ELSE N” END + frs.name + N’ ‘ + frs.system_type_name
    + CASE WHEN frs.is_nullable = 1 THEN N’ NULL’ ELSE N’ NOT NULL’ END
    FROM sys.dm_exec_describe_first_result_set(@tsql, @params, @browse_information_mode) frs;

    SET @cmd = N’CREATE TABLE dbo.QueueBlitzOutput (
    ‘ + @cmd + N’
    );
    INSERT INTO dbo.QueueBlitzOutput
    ‘ + @tsql;
    PRINT @cmd;

    Reply
  • When I attempt to save the results of sp_Blitz into a table using:
    sp_blitz @checkserverinfo = 1, @outputservername=’bhmdbha1′,@outputschemaname=’dbo’,@outputdatabasename=’raptor_production’, @outputtablename=’sp_blitz’, I get the following error message:
    Msg 18483, Level 14, State 1, Line 1
    Could not connect to server ‘BHMDBHA1’ because ” is not defined as a remote login at the server. Verify that you have specified the correct login name. .

    Could someone tell me what this is and how to get around it.

    I am running SQL Server 2014 Enterprise Edition.

    Reply
  • Brent, you are a legend!

    One thing I have noticed, is that after a long time of running, the rows count to query using the dashboard becomes quite onerous.

    I think there is no built in purging, is it not a good idea to force us to use one, or is there perhaps something wrong with the dashboard query that it wants to fetch all rows?

    Kind Regards

    Reply
  • I’m trying to output the results of sp_blitz to a table in a database on a different server. Are there other parameters I need to set besides these?
    @OutputServerName = ‘IS01’,
    @OutputDatabaseName = ‘DBA_Local’,
    @OutputSchemaName = ‘dbo’,
    @OutputTableName = ‘BlitzResults’

    Do I specify a login or does it just try to use the account I’m running from?

    Reply
  • VIJAY DEVARAPALLI
    March 1, 2019 1:26 pm

    Hello, I am trying to setup the sp_blitz in our production environment, i copied the code for sp_blitz to ssms ran the stored procedure to create sp_blitz, after that i tried to execute the stored procedure, and i am keep getting the errors below

    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 1184
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 1839
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 1921
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 2148
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 2209
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 2239
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 2338
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 2514
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 2786
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 2996
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 3035
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 3061
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Line 1
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 3479
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Line 1
    Cannot insert the value NULL into column ‘DatabaseName’, table ‘tempdb.dbo.#BlitzResults_______________________________________________________________________________________________________000000105583’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4012
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4016
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4020
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4026
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4030
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4034
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4038
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4042
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4052
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4056
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4060
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4064
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 515, Level 16, State 2, Procedure sp_Blitz, Line 4068
    Cannot insert the value NULL into column ‘Details’, table ‘tempdb.dbo.#DatabaseDefaults___________________________________________________________________________________________________000000105588’; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 1934, Level 16, State 1, Procedure sp_Blitz, Line 4531
    INSERT failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

    the same works in my QA environment

    Any help greatly appreciated
    Thanks
    Vijay

    Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}