Sql agent maintenance job run duration query

Run this across all registered servers for dates your server runs optimizations on.
You will get total time it took for the jobs to run in the two weeks. Drop the values in excel and plot graph if required. Courtsey Ken Simmons, he ran this in our production environment to show how reindexing only fragmented index took a lot of time out of optimization schedule. How he changed the optimizaiton plan is a different blog post.

select
STUFF(STUFF(RIGHT('00000000' + CAST(run_duration as varchar(10)) ,6),3,0,':'),6,0,':') AS
run_duration,
run_date
from msdb.dbo.sysjobs A Join msdb.dbo.sysjobhistory B
ON A.job_id = B.job_id
WHERE A.name like '%opt%' and B.step_id = 2 and (run_date = 20110212 or run_date = 20110205)

SSIS error running executable/batch file from network

So, I had this issue for sometime now, where an SSIS scheduled from a sql agent job, would hang at a particular step and not run at all. We figured it was due to the exe and bat file in SSIS which needed a user prompt confirmation to go to next step. I had looked online if there was an option to suppress it inside of SSIS but dint find one.

Finally my manager pointed me to this solution:
"The publisher could not be verified" prompt running executable from network
http://www.annoyances.org/exec/forum/winvista/t1151260847

Problem:
Running XP SP2 or higher, you try to run an executable located on another machine
on your network. Your accosted with a prompt: "The publisher could not be verified".
You are forced to confirm that you wish to run this program... every time you run
it.

Solution:
Run gpedit.msc

Go to User Configuration >> Administrative Templates >> Windows Components >> Attachment
Manager

Add "*.exe;*.bat;" to the "Inclusion list for moderate risk file types" setting.

which I configured on the server that runs the package (as above)
and that fixed it! No more prompts.

Sql agent Jobs (owner,lastrun,runstatus)

select * from msdb.dbo.sysjobs a
inner join sys.server_principals b
on a.owner_sid=b.sid where b.name like '%%'

SELECT j.[name],
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status,b.name as jobowner
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id AND jh.step_id = 0
inner join msdb.dbo.syscategories sc
on j.category_id = sc.category_id
inner join sys.server_principals b
on j.owner_sid=b.sid
--WHERE sc.[name] like '%DTS%'
GROUP BY j.[name], jh.run_status,b.name

Using registered servers tab in ssms

Register all sql servers under registeres servers in ssms,either by adding manually or using a regsrvr file

1) Right click local databases and import-->

2)Select the exported file which has a list of already existing servers exported from someone else's. It is in .regsrvr format.

Once all servers are registered a query can be run across all of them at one.

Very useful to see:
Exec sp_who2
Exec sp_whoisactive
server configurations etc