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

Merge replication identity range crisis …

--Find the merge replication identity ranges/seed/crisis thresholds ...

SET NOCOUNT ON
declare @filterPct smallint
select @filterPct = 0
create
table #ranges
(objectId int, tableName sysname, columnName sysname, rangeMin int, rangeMax int, currentMax int NULL)
INSERT
INTO #ranges
-- find replication tables and columns which are identity columns
select
objectId , tableName , columnName
, convert(int, substring(minStr, 0, patindex('%and%', minStr))) minValue ,
convert(int, substring(maxStr, 0, len(maxstr))) maxValue ,
NULL from
(
select
o.id objectId ,
object_name(o.id) tableName
, o.name columnName
, o.colId
, com.text
, substring(com.text, patindex('%>%', com.text) + 1, 20) minStr
, substring(com.text, patindex('%<%', com.text) + 1, 20) maxStr
from
msrepl_identity_range r inner
join
syscolumns o on r.objid = o.Id inner
join
sysconstraints con on o.id = con.id and o.colid = con.colId inner
join
syscomments com on con.constId = com.id
where o.status = 0x80 ) t1
order by 2,3
DECLARE @objectId int, @sqlStr nvarchar(4000)
SELECT
TOP 1 @objectId = objectId from #ranges order
by objectId
WHILE (@@rowcount
> 0)
BEGIN
SELECT @sqlStr =
'update #ranges set currentMax = (select max('
+ columnName +
') from '
+ tableName +
'(nolock) where '
+ columnName +
' BETWEEN '
+
convert(varchar, rangeMin)
+
' AND '
+
convert(varchar, rangeMax)
+
') WHERE '
+
' objectId = '
+
convert(varchar, @objectId)
from #ranges where objectId = @objectId
EXEC
sp_executesql
@sqlStr
SELECT
TOP 1 @objectId = objectId from #ranges where objectId > @objectId order
by objectId
END
select
@@servername serverName
, tableName
, (rangeMax - rangeMin) rangeSize
, (rangeMax - currentMax) rangeAvailable
, (currentMax - rangeMin) rangeUsed
, (convert(decimal(10,2), (currentMax - rangeMin)) / (rangeMax - rangeMin)) * 100 rangeUtilisation
from
#ranges
where
(convert(decimal(10,2), (currentMax - rangeMin)) / (rangeMax - rangeMin)) * 100 > @filterPct
drop table #ranges

Script all user database tables …

--Script all user database tables.

set nocount on
use
master
go
declare @DBNAME varchar(128)
declare @TabName varchar(128)
declare @TabID int
declare @DDLDEF VARCHAR(8000)
declare @ColName varchar(1000)
declare @Count int
declare @FileGroup varchar(128)
declare DBCur cursor
for
select name from sysdatabases where name not in ('tempdb', 'master', 'pubs', 'Northwind', 'msdb', 'model')
order by name
OPEN DBCur
fetch next from DBCur into @DBNAME
while @@FETCH_STATUS =0

begin
exec('use ' + @DBNAME)
PRINT '/*Scripting for ' + @DBNAME + ' done on ' + CAST(GetDate() as varchar(50)) + '*/'
declare TabNameCur cursor
for
select '[' + su.name + '].[' + so.name + ']', so.id , sfg.groupname
from sysusers su inner join sysobjects so
on su.uid = so.uid
inner join sysindexes si on si.id = so.id
inner join sysfilegroups sfg on sfg.groupid = si.groupid
where so.type = 'U'
order by su.name, so.name
open TabNameCur
fetch next from TabNameCur into @TabName, @TabID, @FileGroup
while @@FETCH_STATUS = 0
begin
set @DDLDef = Char(13) + 'CREATE TABLE ' + @TabName + '(' + Char(13)
/* Must incorporate all datatypes */
declare ColNameCur cursor
for
select '[' + sc.name + ']' + ' ' + st.name +
CASE when st.name = 'char' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'nchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'nvarchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'varbinary' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'binary' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'varchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'decimal' then '(' + CAST(sc.LENGTH as varchar(50)) + ','
+ CAST(sc.PREC as varchar(50)) + ')'
when st.name = 'numeric' then '(' + CAST(sc.LENGTH as varchar(50)) + ',' +
CAST(sc.PREC as varchar(50)) + ')'
else ''
end
+ ' ' +
CASE when sc.ISNULLABLE = 1 then 'NULL' else 'NOT NULL' end
from syscolumns sc
INNER
JOIN systypes st on sc.xtype = st.xtype
where sc.id = @TabID
order by sc.colid
open ColNameCur
fetch next from ColNameCur into @ColName
set @Count = 1
while @@FETCH_STATUS = 0 -- ColNameCur
begin
if @Count = 1
begin
set @DDLDef = @DDLDef + ' ' + @ColName + Char(13)
set @Count = @Count + 1
end
else
begin
set @DDLDef = @DDLDef + ' , ' + @ColName + Char(13)
set @Count = @Count + 1
end
fetch next from ColNameCur into @ColName
end
/* ColNameCur @@FetchStatus */
set @DDLDef = @DDLDef + ') ON [' + @FileGroup + ']' + Char(13) + 'GO'
PRINT @DDLDEF
set @DDLDef = ''
close ColNameCur
deallocate ColNameCur
fetch next from TabNameCur into @TabName, @TabID, @FileGroup
end
close TabNameCur
deallocate TabNameCur
fetch
next
from DBCur into @DBNAME
end
close DBCur
deallocate DBCur

Database file properties …

--Database file logical ids, physical location, sizes and growth factors.

select ltrim(+'['+m.name+']')
as database_name, ltrim(rtrim(substring(s.name, 1,200))) as logical_file_name, ltrim(rtrim(s.filename)) as physical_file_path, case
when s.maxsize = -1 then convert(varchar(200), s.maxsize)+space(1)+'[Unlimited File Growth]' else convert(varchar(200), convert(decimal(18,2), s.maxsize/1024*8))+space(1)+'(MB)' end as maximum_allowable_size, case when convert(varchar(200),convert(decimal(18,2),s.size*8/1024)) < = '0' then
'0' else convert(varchar(200),convert(decimal(18,2),s.size*8/1024))+
space(1)+'(MB)' end as current_size, case
when s.status & 0x100000 = 0x100000 then ( select
convert(varchar(200), cast(growth as varchar(10)))+'%' from sysfiles where fileid = s.fileid) else cast(convert(varchar(200), s.growth*8/1024) as
varchar(10))+'(MB)' end as growth_factor, case
when s.status & 0x100000 = 0x100000 then 'Autogrow' else 'Fixed Growth' end as
status from master .. sysaltfiles s inner
join master .. sysdatabases m on m.dbid = s.dbid group
by m.name,s.fileid,s.name,s.filename, s.maxsize,s.size,s.growth,s.status order
by 1