Sql-server – History from SQL Agent Jobs (SQL Server 2005)

sql serversql-server-2005

how can I able to see all available records from SQL Agent Jobs, because in SSMS is dispayed only reduced number of records
are these records stored in system databases, not only in plain log files
how can I get records generated by SQL Agent Jobs only
is this history settable,

enter image description here

Best Answer

You need msdb.dbo.sysjobhistory and a few JOINs of course to read the data

To change how SQL Server Agent uses this table, use this stored procedure with suitable values

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100

Related Solutions

Sql-server – Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

For logins, this is better IMHO:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.

enter image description here

I'd do the logins first, in case any of the jobs involve any of the logins.

SQL Server 2005 – SQL Agent Jobs Appearing as Job ID Only

SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names:

They are from reporting service subscriptions.

Below SQL will give you list of the job names against the actual reports

select ScheduleID
    ,Path
    ,name
    ,s.Description
from ReportServer.dbo.catalog c
inner join ReportServer.dbo.Subscriptions s on c.ItemID = s.Report_OID
inner join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

Even better (got it from this link):

A SQL Server Agent job is created to control the scheduled execution of the report, and this is stored in the sysjobs and sysjobsteps of the MSDB database. The agent job name is a guid value that represents the ScheduleId (yes, in case you've not yet noticed, this makes your Agent Job list messy with a bunch of guids that make it hard to work with your non-subscription jobs, we are hearing you:).

select 'Report' = c.Path
    ,'Subscription' = s.Description
    ,'SubscriptionOwner' = uo.UserName
    ,'SubscriptionModBy' = um.UserName
    ,'SubscriptionModDate' = s.ModifiedDate
    ,'ProcessStart' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessStart)
    ,'NotificationEntered' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.NotificationEntered)
    ,'ProcessAfter' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessAfter)
    ,n.Attempt
    ,'SubscriptionLastRunTime' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.SubscriptionLastRunTime)
    ,n.IsDataDriven
    ,'ProcessHeartbeat' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessHeartbeat)
    ,n.Version
    ,n.SubscriptionID
from Notifications n
inner join Subscriptions s on n.SubscriptionID = s.SubscriptionID
inner join catalog c on c.ItemID = n.ReportID
inner join Users uo on uo.UserID = s.OwnerID
inner join Users um on um.UserID = s.ModifiedByID

Best Answer

Related Solutions

Sql-server – Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

SQL Server 2005 – SQL Agent Jobs Appearing as Job ID Only

Related Question