SQLDiagCmd: a Standalone Runner for Glenn Berry’s SQL Server Diagnostic Scripts

by

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:

  • It should run from command line (or through a UI)
  • It should be standalone, require no installation, and not require Excel or Office to be installed.
  • It should work with new (and old) versions of his scripts, and not require updating when the scripts are updated.
  • It should not require a blank excel template file.
  • It should identify and run Server and database specific queries automatically.
  • It should be able to target multiple databases at once.

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 
SQLDiagCmd -S MUTLEY -U username -P password -i queries.sql -o C:\outputfolder -d databaselist –A

 

-S, –servername Required. Server name or instance to run queries against.
-E Use a trusted connection (Windows Authentication).
-U, –username Username for SQL Login.
-P, –password Password for SQL Login.
-i, –inputfile Required. Query diagnostic file to run.
-o, –outputfolder Required. Folder location to write results file.
-A, –autofit Auto-fit Excel columns.
-t, –timeout Query timeout in seconds. Defaults to 360 seconds (for longer running queries).
-d, –databases Semicolon separated list of specific databases to examine. Separate each database with a semicolon. Do not include spaces between databases and semicolon.
–help Display this help screen

 
The SQLDiagCmd git repository is available at github: SQLDiagCmd

This project uses  EPPlus and the Command Line Parser Library.

17 thoughts on “SQLDiagCmd: a Standalone Runner for Glenn Berry’s SQL Server Diagnostic Scripts”

  1. Hi there. I just recognized your tool since Glenn mentioned it in his most recent blog post. I'll definitely try your tool tomorrow.
    Thank you for the work

  2. i am getting this error.i have checked permission to c:\temp.
    Unhandled Exception: System.IO.DirectoryNotFoundException: Could not find a p
    of the path 'C:\Temp\20130206_082620_TCNB08\SQL2012.xlsx'.
    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
    at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access
    nt32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOption
    ptions, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boo
    n useLongPath)
    at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess acces
    FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boole
    bFromProxy)
    at System.IO.FileStream..ctor(String path, FileMode mode)
    at SQLDiagRunner.Runner.ExecuteQueries(String servername, String username,
    ring password, String scriptLocation, String outputFolder, IList`1 databases,
    olean useTrusted, Boolean autoFitColumns, Int32 queryTimeoutSeconds)
    at SQLDiagCmd.RunnerProxy.Run()
    at SQLDiagCmd.Program.Main(String[] args)

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

    C:\SQLDiagCmd>sqldiagcmd -S 192.168.100.1,12345 -U todd -P password -i
    queries.sql -o c:\sqldiagcmd -d “Northwind” -A

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

  11. Hi,

    Thanks for sharing your script with the SQL Server Community.

    I'm having issues getting it to run. Could you please advise?

    Script dirtectory =
    C:\SQL Server Performance Audit Tools\Glenn Berry\2008
    Script Version=SQL Server 2008 R2 Diagnostic Information Queries (February 2013)

    Command line i'm trying is

    SQLDiagCmd.exe -S CEASE -U administrator -P Password -i “C:\SQL Server Performance Audit Tools\Glenn Berry\2008\SQL Server 2008 R2 Diagnostic Information Queries (February 2013)” -o “C:\SQL Server Performance Audit Tools\Glenn Berry\2008” -d “AdventureWorks2008R2” -A

    I'm getting the SQLDiagCmd usage displayed.

    Thanks (from a snowy Belfast)

  13. Hi,
    SQLDiagUI.exe works OK, but ignores the list of databases, and just does stats for master.

    I cannot get SQLDiagCmd.exe to work. using the same parameters as SQLDiagUI.exe I'm just getting the SQLDiagCmd usage displayed. No errors, just usage.

  14. hello,

    this returns the Help screen:
    O:\DBATools>SQLDiagCmd.exe -E -S “deldbolyProd01” -i “.\SQLDiag2008R2.sql” -o o
    :\dbatools -d “ESITDMS;ESITDMS_Reporting” -A

    is excel required on the machine?
    .Net4 is installed

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

  17. Pingback: SQLDiagCmd Updated – Mitch Wheat

Comments are closed.