List all SQL jobs in Job Activity Monitor with Sp_help_job

Question: How can I list all jobs that appear in the SQL Server Agent Job Activity Monitor? Answer: To list all SQL jobs as listed in the Job Activity Monitor use the msdb.dbo.sp_help_job stored procedure. Using msdb.dbo.sp_help_job The stored procedure accepts parameters, and if you’d like to view the parameters link here. Otherwise running the stored procedure - msdb.dbo.sp_help_job - without any parameters will list all the jobs and associated details - such as status, name,description,owner_sid,date_created,date_modified,enabled Another option is msdb.dbo.sysjobs_view USE msdb ; GO EXEC dbo.sp_help_job ; GO Read More on SQL Server Agent job history SQL Server - Send... Read more →

View DTS packages with Powershell – DTS to SSIS planning

Question: How can I gather DTS package counts from a large number of SQL Server Instances, (over 500) ? We’re in the planning stage of a SQL Server 2012 upgrade . DTS is discontinued in SQL Server 2012, and I’m sizing the amount of effort required for upgrading DTS to SSIS. Answer: Estimating the amount of effort of DTS to SSIS is critical SQL Server 2012 SSIS upgrade consideration. Your immediate requirement is to document the package count but further effort will be required by developers to analyse the packages . Using Powershell , it is easy to iterate through... Read more →

SQL Server - Get datetime values with agent_datetime function and msdb..sysjobhistory

Question: How can I programmatically return SQL Agent jobs which failed in the last 24 hrs. I was running the following code and it didn’t return the results as expected ? This job is part of some automated DBA tasks SELECT * FROM msdb..sysjobhistory WHERE run_status NOT IN (1,4) DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 24 Answer: The code above will return failed jobs via the WHERE run_status NOT IN (1,4) , but not for the timeframe you expect. The reason: The code doesn’t account for sjh.run_date being an INT. The CONVERT is not having the desired result. Use the msdb.dbo.agent_datetime function.... Read more →

SQL Server MSDB size checklist

Maintaining the SQL Server msdb size should be a regular DBA task. The exact tasks to complete , depend on how you use SQL server A typical question I receive is: “The MSDB database has grown till 10 GB and now I'm experiencing no more space on disk C:.” Note: There are different situations where you won’t want to delete history. Plan ahead – and only apply these procedures in accordance with system policies Exceute SP_DELETE_BACKUPHISTORY A system stored procedure . Maintains the size of backup and restore history tables. It takes one argument : @oldest_date Execute SP_PURGE_JOBHISTORY A system... Read more →