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:

  1. It should run from command line (or through a UI)
  2. It should be standalone, require no installation, and not require Excel or Office to be installed.
  3. It should work with new (and old) versions of his scripts, and not require updating when the scripts are updated.
  4. It should not require a blank excel template file.
  5. It should identify and run Server and database specific queries automatically.
  6. 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


This project uses  EPPlus and the Command Line Parser Library.

The SQLDiagCmd git repository is available at github: SQLDiagCmd



16 Comments:

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

By OpenID feleryan, at February 06, 2013 2:19 am  

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)

By Blogger bhuppy, at February 06, 2013 5:33 am  

I've uploaded a fix. Same links.

By Blogger Mitch Wheat, at February 06, 2013 10:44 am  

Works great!!! thanks a ton!!!

By Anonymous Anonymous, at February 08, 2013 12:22 am  

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

By Blogger Todd, at February 26, 2013 6:57 pm  

Thanks Todd. I've fixed and uploaded a new version.

By Blogger Mitch Wheat, at February 26, 2013 7:26 pm  

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.

By Anonymous Anonymous, at March 08, 2013 4:34 am  

@Anonymous: and that command line is? If you want me to help (which I will) you need to give me information.

By Blogger Mitch Wheat, at March 08, 2013 7:50 am  

question: has this been tested using Excel 2013?

By Anonymous Anonymous, at March 14, 2013 11:29 pm  

No it has not been tested on 2013. You're more than welcome to :)

By Blogger Mitch Wheat, at March 15, 2013 7:44 am  

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)


By Anonymous George Johnston, at March 25, 2013 4:38 am  

@George: What error are you getting? (command line looks fine). Do you have .NET framework 4.0 installed?

By Blogger Mitch Wheat, at March 25, 2013 7:54 am  

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.

By Anonymous Anonymous, at April 04, 2013 12:57 pm  

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

By Blogger Christopher Petrich, at April 11, 2013 7:51 am  

No, Excel is not required. I have just reproduced the bug. Will fix asap...

By Blogger Mitch Wheat, at April 11, 2013 8:16 am  

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.

By Blogger Mitch Wheat, at April 11, 2013 6:20 pm  

Post a Comment



<< Home
    

Powered by Blogger