Finding the SQL Error Log Path and the SQL Server Home Path with T-SQL is straightforward. As a DBA I want to spend as little time as possible completing tasks , that I can otherwise script out and automate.

An example is The SQL Server Tivoli Monitoring agent which requires the SQL Error Log Path and the SQL Server Home Path , for installation and configuration purposes.

My first problem was to derive the t-sql to gain the information for one sql server instance . The second problem was to use a scripting framework to iterate through every sql server instance on the database server inventory

The t-sql to derive the error log file was straightforward

select ServerProperty('ErrorLogFileName')

The code for the SQL Server path is an "undocumented" method . "Undocumented" means Microsoft do not support it. "Undocumented" also means you have to figure out to how to use it without BOL.

The extended stored procedure “xp_instance_regread” , in the master database, derives values from the registry.

The code below returns the SQL Server home for a given SQL Server instance