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