sp_Blitz® Updated to v5

Happy holidays! Santa SQL comes bringing tidings of cheer with a new sp_Blitz® – but also doom and gloom about configuration problems in your servers.  Here’s some of the recent contributions from readers:

  • John Miner suggested tweaking checkid 48 and 56, the untrusted constraints and keys, to look for is_not_for_replication = 0 too.  This filters out constraints/keys that are only used for replication and don’t need to be trusted.
  • Ned Otter caught a bug in the URL for check 7, startup stored procs.
  • Scott (Anon) recommended using SUSER_SNAME(0x01) instead of ‘sa’ when checking for job ownership, database ownership, etc.
  • Martin Schmidt caught a bug in checkid 1 and contributed code to catch databases that had never been backed up.
  • Added parameter for @CheckProcedureCache.  When set to 0, we skip the checks that are typically the slowest on servers with lots of memory.  I’m defaulting this to 0 so more users can get results back faster.
  • Andreas Schubert caught a typo in the explanations for checks 15-17.
  • K. Brian Kelley added checkid 57 for SQL Agent jobs set to start automatically on startup.
  • Added parameter for @CheckUserDatabaseObjects.  When set to 0, we skip the checks that are typically the slowest on large servers, the user database schema checks for things like triggers, hypothetical indexes, untrusted constraints, etc.
  • David Tolbert caught a bug in checkid 2.  If some backups had failed or been aborted, we raised a false alarm about no transaction log backups.
  • Fixed more bugs in checking for SQL Server 2005. (I need more 2005 VMs!)
  • Ali Razeghi added checkid 55 looking for databases owned by <> SA.
  • Fixed bugs in checking for SQL Server 2005 (leading % signs)

Whew!  And there’s more to come – I’ve got another half-dozen improvements queued up that also require new web pages, so those will take a little while longer.

If you’ve already downloaded sp_Blitz®, you can run master.dbo.sp_BlitzUpdate to fetch the latest version from The Clouds.  Enjoy!

Previous Post
Consulting Lines: SQL Server Needs a Dog
Next Post
Silent CPU Killers

6 Comments. Leave new

  • Great stuff Brent, you never cease to amaze me and the community.

    Reply
  • Great idea Brent – thanks for the script! Here’s some feedback and a bug I came across qualifying this for use in my world and do some sanity checking on my DBs:
    1 [bug] In Finding ‘Stats Updated Asynchronously’ with Details like ‘has auto-update-stats-async disabled’ the where clause is probably reversed ‘WHERE is_auto_update_stats_async_on = 1’
    2 [nit] I didn’t want to define the sp_BlitzUpdate to I commented out that part.. might be a common reaction so perhaps a config value at top ‘enable auto update’
    3 [nit] I use usp_Name instead of sp_Name for my stuff … pretty nitty pick 🙂
    4 [meh]History Purge test checks for 90 days… we use 180 days… config variable?

    Thanks for the nice tool – as a small company I am unlikely to deal with the unwashed database making masses in the same way you do so this tool is really a best practices analyses script from where I sit. I don’t know that I’ll have anything to contribute in terms of new tests but I’ll send feedback when I have it.
    cheers
    Ram

    Reply
  • Michael Bliesner
    December 28, 2011 1:58 pm

    First off great script. I’ve been doing this line by line like your original blitz script. I’d like to suggest another check. Check for default autogrow values. I hate finding a database set to grow by 1MB and 10% on the log file.

    Reply
    • Michael & Ram – thanks, guys! I do get a lot of suggestions for code changes, but the thing that helps most is actual code contributions. 😀 I can only do so much with my limited time. Thanks though!

      Reply
      • Michael Bliesner
        December 29, 2011 4:05 pm

        Here’s the code I added.

        INSERT INTO #BlitzResults
        ( CheckID ,
        Priority ,
        FindingsGroup ,
        Finding ,
        URL ,
        Details
        )
        SELECT DISTINCT 50 as CheckID ,
        50 as Priority ,
        ‘Reliability’ As FindingsGroup ,
        ‘Default Growth rate on Data File’ as Finding ,
        ‘ ‘as URL ,
        ( ‘The ‘ + DB_NAME(database_id)
        + ‘ database has the default growth rate defined. This should be changed to a value appropriate for the database.’) As Details
        FROM sys.master_files
        WHERE growth = 10 and is_percent_growth = 0 and type_desc = ‘rows’

        INSERT INTO #BlitzResults
        ( CheckID ,
        Priority ,
        FindingsGroup ,
        Finding ,
        URL ,
        Details
        )
        SELECT DISTINCT 50 as CheckID ,
        50 as Priority ,
        ‘Reliability’ As FindingsGroup ,
        ‘Default Growth rate on Log File’ as Finding ,
        ‘ ‘as URL ,
        ( ‘The ‘ + DB_NAME(database_id)
        + ‘ database has the default growth rate defined. This should be changed to a value appropriate for the database.’) As Details
        FROM sys.master_files
        WHERE growth = 10 and is_percent_growth = 1 and type_desc = ‘log’

        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":""}