SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Thursday, January 17, 2013
SQLDiagCmd: a Standalone Runner for Glenn Berry’s SQL Server Diagnostic Scripts
If I said that I use Glenn Berry’s SQL Server diagnostic queries a fair bit it would be an understatement. Glenn releases versions of these very useful TSQL scripts (comprised of 50 plus queries) and a blank Excel Template to save the results in.
Rather than running each query individually, highlighting the results in SSMS and then copying and pasting to Excel, I decided to write a standalone, no install required runner that would do the donkey work for me:
The only (optional) alteration to Glenn’s scripts, is a small addition to the comment line which describes each query, that is used as the worksheet name (if this is missing, it uses the text at the start of the comment, but this won’t be unique due to Excel’s ridiculous limitation of having a 31 character maximum length for worksheet names!).
So, for each comment preceding a query, instead of:
-- SQL Server Services information (SQL Server 2008 R2 SP1 or greater) (Query 5)
I've altered to explicitly specify what text to use as the worksheet name (in bold):
-- SQL Server Services information (SQL Server 2008 R2 SP1 or greater) (Query 5)(SQL Server Services Info)
I’ve emailed Glenn to see if I can get this addition in future scripts.
You can download version 1.0 of the zipped executable from here: SQLDiagCmd.zip (targets .NET Framework 4.0)
OK, How do I run it?
Download Glenn’s diagnostic script that targets your version of SQL Server and download SQLDiagCmd.exe.
Assuming they are in the same directory:
SQLDiagCmd.exe -E -S MUTLEY -i ".\SQL Server 2008 Diagnostic Information Queries (January 2013).sql"
-o c:\temp -d "AdventureWorks2008R2;AnotherUserDatabase" -A
Typing SQLDiagCmd.exe –-help displays usage and parameters:
Usage: SQLDiagCmd -E -S MUTLEY -i queries.sql -o C:\outputfolder -d databaselist –A
Hi there. I just recognized your tool since Glenn mentioned it in his most recent blog post. I'll definitely try your tool tomorrow.
i am getting this error.i have checked permission to c:\temp.
I've uploaded a fix. Same links.
Works great!!! thanks a ton!!!
It is creating the excel sheet but each one has "Keyword not supported: 'username'." in it. I suspect it may be that I am trying to connect to a database on a non standard port. I've copied my command line below.
Thanks Todd. I've fixed and uploaded a new version.
Looks like could be great tool. But keeps giving me just summary of syntax, & not telling me what it doesn't like about cmd parameters.
@Anonymous: and that command line is? If you want me to help (which I will) you need to give me information.
question: has this been tested using Excel 2013?
No it has not been tested on 2013. You're more than welcome to :)
@George: What error are you getting? (command line looks fine). Do you have .NET framework 4.0 installed?
No, Excel is not required. I have just reproduced the bug. Will fix asap...
Hi, apologies to all who downloaded the non-working SQLDiagCmd (I'd introduced a bug last upload). I've uploaded a new version, which you can download from any of the links.
MSN, Email: mitch døt wheat at gmail.com