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!
40 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!
Ah, interesting – thanks, we’ll file that as a bug and check it out. Have a great weekend!
Very nice! I like the additions to the script as well as the app.
another release of wonderful tool. Really appreciate your sharing Brent and Team!. While others keep their gun, you share the secret sauce
Love it!!!Love it!!!Love it!!!
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. 😉
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. 😉
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!
Fantastic. That worked for me too. Thank you.
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.
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!
Please share the link of sp_blitz app really would like to test in our enviroment.
We no longer offer the app, sorry.
The app was very handy and I still use it. Any particular reason that’s been phased out?
Just a support issue.
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;
John – we don’t currently have a tutorial on putting sp_BlitzWho into a table, but you can use this instead:
https://www.brentozar.com/archive/2016/07/logging-activity-using-sp_whoisactive-take-2/
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.
Mark, we can’t do support like this in blog comments, and your login issue may be beyond what we can trouble shoot easily.
Please head over here: http://firstresponderkit.org if you need to ask questions, etc.
Thanks!
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
Matthew – thanks! No, we leave deletes up to you. Some folks clean it out with truncate, for example.
thanks, whats your opinion on leaving this running regularily all the time? It seems to impact some servers worse than others. And whats your opinion if BlitzCache is using 1.2GB of data – where sp_Blitz also seems to be taking 27s in duration, and about 30 seconds in CPU usage.
Matthew – I’m not really a fan of collecting data you’re not using. Figure out what your biggest pain point is, and then collect data you need to solve that pain point.
For more in-depth explanations of how to collect the right data, that’s where our training classes come in, like this:
https://www.brentozar.com/training/diagnosing-slow-sql-servers-wait-stats/1-measure-change-sql-server-3-metrics-gather-23-minutes/
Hope that helps! Brent
I personally like to collect it once/day after core hours so if I have to dig in, I at least have the data. I’ll periodically (proactively) use the data to tune as time permits.
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?
Steve – definitely check out the readme for the documentation page: http://firstresponderkit.org There’s lots of info on there about the parameters, like this: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/tree/master#parameters-common-to-many-of-the-stored-procedures
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
Vijay – sure, check the readme for where to go for support.
I am doing this since long but today I have found an issue where one of our query got stuck with wait type QRY_PROFILE_LIST_MUTEX, though it was active and blocking this sp_blitz session it was hung. Is there any way we can overcome this issue, I know this happens when blitz run OUTER APPLY sys.dm_exec_query_statistics_xml(s.session_id) qs_live.
Bhargav – for support, read the readme on where to go. Thanks!
We run a job from a centrally managed server that kicks off sp_blitz across sever different SQL Server instances, writing the output to a database located on that centrally managed server. I ran into issues with deadlocks when writing to the database (snapshot isolation = true). In the jobstep, I put the call to sp_blitz inside of a try/catch that is inside of a retry loop, with a random delay. This runs on the weekend and I’m not concerned with resources used or running at an exact time. This seems to have taken care of the issue.
/*
Wait for random time between 0 and 30 minutes
This is to avoid deadlocks, since all instances start this at the same time.
This will attempt to perform the DB write 3 times. This is due to the fact that we still have deadlocks.
*/
DECLARE @finalTime char(12),
@timeint int,
@success bit = 0,
@RetryCnt int = 1
WHILE @RetryCnt <= 3 and @success = 0
BEGIN
BEGIN TRY
set @timeint = ABS(CHECKSUM(NEWID()) % 30); /* number of minutes to wait */
set @finalTime = '00:'+ RIGHT('00' + cast (@timeint as nvarchar), 2) + ':00.000'; /* convert to time format hh:mm:ss.mss */
WAITFOR DELAY @finaltime;
exec sp_blitz @CheckServerInfo=1,
@OutputSchemaName='dbo',
@OutputTableName='blitz',
@OutputDatabaseName =',
@OutputServerName=”;
set @success = 1;
END TRY
BEGIN CATCH
set @RetryCnt = @RetryCnt + 1;
if @RetryCnt > 3
THROW;
END CATCH
END /* WHILE */
John – this isn’t a great place to get support or give feedback to the open source scripts. Check the readme for more information on where to get support or give feedback. Thanks for understanding!
Got it.
I actually have DB Name and Server Name values. I put gt/lt symbols around dummy values and the page interpreted that as a tag.