How to search SQL Server Agent Command Text for a string

Question: I'd like to find all SQL Server Agent Jobs with a certain string within the Command section . There's been a change in reference to a database table, therefore I need to review all SQL Server Agent Jobs and check they are not referencing this database table. Is there a sql query I can use to search for the string? As an extra request - do you have any advice on how to run this query across multiple SQL Server Instances and create a report? Answer: Both of these tasks are possible . Firstly , let's look at t-sql... Read more →

Check SQL Agent Job Exceution Status and Messages Generated with a sql script

Question: How can I check SQL Agent Job Execution Status using t-sql? I want to be able to report on the Job Steps execution status , run time duration and Message Generated. Do you have a script example , which returns the above values based on the last 24 hrs. Answer: SELECT @@servername AS 'Server_Name', j.NAME AS 'Job_Name', CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END AS ExecutionStatus ,s.step_id AS 'Step' ,s.step_name AS 'Step_Name' ,msdb.dbo.agent_datetime(run_date, run_time) AS 'Run_DateTime' ,((run_duration / 10000 * 3600 +... Read more →

SQL Server Agent Top Tips

Scheduling How to schedule a SQL Server Agent Job to execute once a year ... SQL Agent Jobs – Schedule in seconds (SQL Server DBA) Status List enabled status of SQL Server Agent Jobs (SQL Server DBA) How write the a SQL Server agent Job status to the Windows Application log Send sql server agent job history through email notification General How to monitor Blocked Processes with SQL Alert and email ... Manage SQL Agent jobs without sysadmin (SQL Server DBA) Backup database job with SQL Server Agent (SQL Server DBA) Read more →

SQL Server Agent Job History Log Not showing any rows

Question: I’m just investigating a SQL Server Agent Job failure and noticed there were no records in the SQL Server Agent history log. I was surprised as the job has been running . Can you explain to me how I can investigate why my SQL Server Agent job history log is not displaying? Answer: It is possible a process has cleared out the msdb.dbo.sysjobhistory table. You can check the table by select * from msdb.dbo.sysjobhistory If statement returns 0 records , than investigate how and why the rows have been deleted.0 records suggests ALL the sql server agent job history... Read more →

Helpful sql commands to manage SQL Agent Jobs

Quite often you may not be available 24 x 7 , and need to delegate some actions for SQL Agent jobs to developers or Operations staff. The typical range of tasks in managing SQL Agent tasks can vary , but this list normally covers the range for the accidental | reluctant individual | DBA who needs to manage SQL Agent Jobs using t-sql --start the job manually --stop the job manually --check if the job is running i.e if it's not in the list it's not running --disable job --enable job --check the job last run time & finish time... Read more →