A reply to a Tweet on #sqlhelp prompted me to look this up. The question was on finding out the SQL Server BINN folder path. This can be done using WMI in the following manner for SQL Server 2008:
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM SqlService WHERE SQLServiceType = 1 and ServiceName = 'MSSQLSERVER'",,48)
For Each objItem in colItems
Wscript.Echo "SqlService instance"
Wscript.Echo "BinaryPath: " & MID(objItem.BinaryPath,1,InStr(objItem.BinaryPath,"sqlservr.exe")-1)
The SQL Server 2008/R2 WMI namespace has visibility for SQL Server 2005 also. So the above snippet of code can be used to retrieve the BINN path (PathName property contains the fully qualified path to the service binary file that implements the service) for the SQL instance. You can change the SQLServiceType to a different value to get the Binary Path folder for other services as well. The value is the above code is set to 1 for the Database Engine. The ServiceName parameter can be used to filter down the results.
For a named instance, you would need to change the service name to MSSQL$INSTA if your instance is called INSTA.
The same is possible through the much talked about Powershell as well.
Other ways to do this would be to use the xp_instance_regread XSP which is not recommended as it is an undocumented command. An example is show here.
Thanks to @afernandez for pointing out another way through a CLR TVF.
Just published a PS Script to calculate the TOP 5 folders for a UNC share. The need for this script arose when I needed to find out which folders were consuming the largest amount of disk space on a File Server. I know there are graphical utilities like DiskMon, Space Monger etc. but this script provides me the flexibility of automation like identifying the folder owners or deleting based on some criteria etc.
Click here to download the script.
Recently I had a Tweep (read:Twitter users) asking about how to disable a SQL Agent job using the Hash Tag #sqlhelp. The information out there is sketchy but there is a MSDB stored procedure to accomplish this: sp_update_job.
You would need to first verify if this job is currently executing or not.
Use @enabled = 1 to enable the job and to disable the job use @enabled = 0.
Example: To disable a job if it is not currently executing
DECLARE @jobid uniqueidentifier
SET @jobid = '83CE6BD4-80BB-4294-A06E-89BBC2AB8101' -- replace with appropriate job id
CREATE TABLE #tblJobInfo
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
INSERT INTO #tblJobInfo
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,SUSER_SNAME,@jobid
IF ((select [job_state] from #tblJobInfo where Job_ID = @jobid) = 4)
EXEC msdb..sp_update_job @job_id = @jobid,@enabled = 0
DROP TABLE #tblJobInfo
Please note that the above XSP used is undocumented and the script itself will not be supported by CSS in case you run into any issues. This is an AS-IS posting and please test the same in your environment before using it.
BOL Topic: sp_update_job
Monitoring Job Activity: http://msdn.microsoft.com/en-us/library/ms188272.aspx
I was working on a root cause analysis for an OOM (Out-of-Memory) issue for SQL Server 2008 R2 and I needed to schedule notifications when the available memory on the server fell below a certain value. SQL Server 2008 and above has a nifty little DMV to do just this. Read about it’s usefulness here:
The hidden gems among DMVs: sys.dm_os_sys_memory