Agent Job Schedules ...

--list sqlagent jobs and their schedules.
select @@servername
as servername,
j.name
as jobduty,
c.name
as jobdescription,
case j.enabled when 1 then
'yes'
else
'no'
end
as enabled,
j.description as categorydescription,
case s.freq_type when 1 then
'once'
when 4 then
'daily'
when 8 then
'weekly'
when 16 then
'monthly'
when 32 then
'monthly relative'
when 64 then
'when sql server agent starts'
when 128 then
'start whenever the cpu(s) become idle'
end
as frequency,
case s.freq_type when 1 then
'one shot'
when 4 then
'every '
+
convert(varchar,s.freq_interval)+
' day(s)'
when 8 then
'every '+
convert(varchar,s.freq_recurrence_factor)+
' weeks(s) on '
+
case s.freq_interval
when 1 then
'sunday'
when 2 then
'monday'
when 4 then
'tuesday'
when 8 then
'wednesday'
when 16 then
'thursday'
when 32 then
'friday'
when 64 then
'saturday'
else
'- daily basis'
end
when 16 then
'day '
+
convert(varchar,s.freq_interval)+
' of every '
+
convert(varchar,s.freq_recurrence_factor)
+
' month(s)'
when 32 then
'the '
+
case s.freq_relative_interval when 1 then
'first'
when 2 then
'second'
when 4 then
'third'
when 8 then
'fourth'
when 16 then
'last'
else
'-'
end+
+
case s.freq_interval when 1 then
' sunday'
when 2 then
' monday'
when 3 then
' tuesday'
when 4 then
' wednesday'
when 5 then
' thursday'
when 6 then
' friday'
when 7 then
' saturday'
when 8 then
' day'
when 9 then
' weekday'
when 10 then
' weekend day'
else
'-'
end+
+
' of every '
+
convert(varchar,s.freq_recurrence_factor)
+
' month(s)'
else
'-'
end
as test1,
case s.freq_subday_type
when 1 then
'occurs once at '+
cast(stuff(stuff(right('000000'
+
cast(s.active_start_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
when 2 then
'occurs every '+
convert(varchar,s.freq_subday_interval)+
' seconds(s) starting at '+
cast(stuff(stuff(right('000000'
+
cast(s.active_start_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
+
' ending at '+
cast(stuff(stuff(right('000000'
+
cast(s.active_end_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
when 4 then
'occurs every '+
convert(varchar,s.freq_subday_interval)+
' minute(s) starting at '+
cast(stuff(stuff(right('000000'
+
cast(s.active_start_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
+
' ending at '+
cast(stuff(stuff(right('000000'
+
cast(s.active_end_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
when 8 then
'occurs every '+
convert(varchar,s.freq_subday_interval)+
' hour(s) starting at '
+
cast(stuff(stuff(right('000000'
+
cast(s.active_start_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
+
' ending at '
+
cast(stuff(stuff(right('000000'
+
cast(s.active_end_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
else
'-'
end
as test2,
case
when s.freq_type = 1 then
'on date: '+
cast(stuff(stuff(right('000000'
+
cast(s.active_start_date as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
+
' at time: '+cast(stuff(stuff(right('000000'
+
cast(s.active_start_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,
':')
as
varchar(20))
when s.freq_type < 64 then
'start date: '+
substring(convert(varchar(11), s.active_start_date),7,8)+'/'+substring(convert(varchar(11), s.active_start_date),5,2)+'/'+substring(convert(varchar(11), s.active_start_date),1,4)
+
' end date: '
+
substring(convert(varchar(11), s.active_end_date),7,8)+'/'+substring(convert(varchar(11), s.active_end_date),5,2)+'/'+substring(convert(varchar(11), s.active_end_date),1,4)
else
'-'
end
as test3,
substring(convert(varchar(11), js.next_run_date),7,8)+'/'+substring(convert(varchar(11), js.next_run_date),5,2)+'/'+substring(convert(varchar(11), js.next_run_date),1,4)
+
' '+
cast(stuff(stuff(right('000000'
+
cast(js.next_run_time as
varchar(16)), 6), 3, 0,
':'), 6, 0,':')
as
varchar(20))
from msdb.dbo.sysjobs j (nolock)
inner
join msdb.dbo.sysjobschedules js (nolock)
on j.job_id = js.job_id
inner
join msdb.dbo.sysschedules s (nolock)
on js.schedule_id = s.schedule_id
inner
join msdb.dbo.syscategories c (nolock)
on j.category_id = c.category_id
where 1 = 1
order
by j.name

Email Disk Space ...

declare @tablehtml nvarchar(max);

declare @sub varchar(200)
select @sub = 'sql server disk space report'
declare @hr int
declare @fso int
declare @drive char(1)
declare @odrive int
declare @totalsize varchar(20)
declare @mb numeric;
set @mb = 1048576
create table #drives (drive char(1) primary key, freespace int null, totalsize int null)
insert #drives(drive,freespace)
exec master.dbo.xp_fixeddrives exec @hr=sp_oacreate
'scripting.filesystemobject',@fso out
if @hr <> 0 exec sp_oageterrorinfo
@fso
declare dcur cursor local fast_forward for
select drive from #drives order by drive
open dcur

fetch next from dcur into @drive
while @@fetch_status=0
begin
exec @hr = sp_oamethod @fso,'getdrive', @odrive out, @drive
if @hr <> 0 exec sp_oageterrorinfo @fso exec @hr =
sp_oagetproperty
@odrive,'totalsize', @totalsize out
if @hr <> 0 exec sp_oageterrorinfo
@odrive update #drives set totalsize=@totalsize/@mb where
drive=@drive

fetch next from dcur into @drive
end
close dcur
deallocate dcur
exec @hr=sp_oadestroy @fso if @hr <> 0 exec sp_oageterrorinfo @fso
set @tablehtml =
n'<html><body bgcolor=white><table width=100% border=1 bordercolor=#66ff99 align=center>'
+ n'<tr bgcolor=#006633 align=left><th colspan=5><font color=white size=+2 face=arial><b>sql agent physical space report -'+space(1)+ltrim(upper(@@servername))+space(1)+'-'+space(1)+convert(varchar(11),
getdate(), 103)+'</th></tr>'
+ n'<tr bgcolor=#33cc66 align=left>'+ n'<th><font color=white size =+1 face=arial><b>server name</th>'+ n'<th><font color=white size =+1 face=arial><b>partition</b> '+ n'<th><font color=white size =+1 face=arial><b>free space</th> '
+ n'<th><font color=white size =+1 face=arial><b>total space</th> ' + n'<th><font color=white size =+1 face=arial><b>%age free space</b></th></tr> '
+ n'<tr><td bgcolor=yellow font face=arial size=+1>'
+ cast((
select td =
ltrim(upper(@@servername)), '',
td = ltrim(upper(drive))+'\:', '',
td = convert(varchar(20), convert(decimal(18,2), freespace/1024))+space(1)+'gb', '', td = convert(varchar(20), convert(decimal(18,2), totalsize/1024))+space(1)+'gb', '', td = case when cast((freespace/(totalsize*1.0))*100.0 as int) < 50 then
convert(varchar(20), cast((freespace/(totalsize*1.0))*100.0 as int))+space(1)+'%'
else convert(varchar(20), cast((freespace/(totalsize*1.0))*100.0 as
int))+space(1)+'%'
end , ''
from #drives
order by drive
for xml path('tr'), type) as nvarchar(max) ) +
n' ' + n'</table><br></br>'+ n'
<center><font color=gray size =-1><a href="http://uk-sql.blogspot.com/" target="_new">blog</a>'
+ n'</body></html>' ;

exec msdb.dbo.sp_send_dbmail
@profile_name = 'someprofilename',
@recipients = 'someone@somewhere.com;someoneelse@somewhereelse.com',
@subject = @sub,
@body = @tablehtml,
@body_format = 'html';
drop table #drives