Introduction
Today’s post is going to be a quick one to get back in the saddle again. I have been on an involuntary hiatus from blogging due to production issues. Things are getting better so it seems like time to get back to blogging.
The script for today is one that I use in all of my job creation scripts as well as a script I have to go back and clean up job output file paths. It gets the location of master on a SQL 2005 or SQL 2008 server, using that to find the Log directory.
The Script
This script is quite simple so I will not spend a ton of time here. It just looks at the location of master, goes up 1 level and swaps in “Log” for “Data” in the directory name. Please note that this may not work well in your environment if you have moved master after installing SQL. Thanks to Argenis Fernandez (Blog|Twitter) for pointing out the ineffcient way I was doing this before, this script now just looks at the location of the SQL Server Error Log and uses the same directory. Warning: This is using undocumented functionality, but the best things in SQL tend to be undocumented.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE PROCEDURE [dbo].[sp_dba_GetJobOutputFileDirectory] @log_directory varchar(400) OUTPUT, @showResults BIT = 1 AS BEGIN SELECT @log_directory = REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(512)), 'ERRORLOG', '') IF @showResults = 1 SELECT @log_directory END GO |
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.