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!
6 Comments. Leave new
Great stuff Brent, you never cease to amaze me and the community.
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
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.
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!
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’
Awesome! Thanks, sir, I’ll add that in the next rev and credit you. Happy New Year!