send email

this evaluates jobs:

declare @tableHTML nvarchar(max) ,
@hdr varchar(max) ;

--------------------------
select case recent_jobs.run_status
when 1 then 'YES'
when 0 then 'NO'
end as run_status, msdb.dbo.sysjobs.name, msg.message, msg.run_date
into [#t]
from (
select max(instance_id) as [MOST RECENT], job_id, server,
run_status
from msdb.dbo.sysjobhistory
group by job_id, server, run_status
) as recent_jobs
inner join msdb.dbo.sysjobs
on recent_jobs.job_id = msdb.dbo.sysjobs.job_id
inner join msdb.dbo.sysjobhistory as msg
on recent_jobs.[MOST RECENT] = msg.instance_id
where ( msdb.dbo.sysjobs.enabled = 1 )

--------------------------

set @hdr = ( N'

Recent SQL Server Job Run

' + N'
<><><><>' + N' <><><><>' + N' <><><><>' ) select @tableHTML = @hdr + cast(( select td = #t.run_status, '', td = #t.name, '', td = #t.message, '', td = #t.run_date, '' from #t for xml path('tr') , type ) as nvarchar(max)) + N'

OK	name	message	date

' ;

exec msdb.dbo.sp_send_dbmail @recipients = 'it@nationalraisin.com',
@subject = 'SQL Daily Status Report', @body = @tableHTML,
@body_format = 'HTML' ;

drop table #t

Seriously! (Severity in Error Messages) Take time to be careful

SELECT error, severity, dlevel,
[description]
FROM sysmessagesWHERE
(msglangid = 1033)
-- hint-hint: the msglangid =1033 is for English..
You can use this list of errors and severities to make sure your scripts are neat and clean.

Function	Description
ERROR_NUMBER()	Returns the number of the error
ERROR_SEVERITY()	Returns the severity
ERROR_STATE()	Returns the error state number
ERROR_PROCEDURE()	Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE()	Returns the line number inside the routine that caused the error
ERROR_MESSAGE()	Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

whaa? Differential won't restore? (what bloke backed up last)

/*
We had to restore a full backup and a differential backup a while ago
To a local db to get some old data put back into the live database.
my manager & I had a heck of a time trying to find out where someone put the last back up.


This query helped us know a utility was still backing up to another location AFTER the job ran...
hence all the extents the DIFF were looking for were bye-bye into the rogue, full-backup.
*/

-------------copy below this line-----------------------

declare
@startDate DATETIME,
@endDate DATETIME,
@database sysname


-- enter your database and date range here:
select
@database = 'SSRC_V5SP1-2',
@startDate = '09/28/09',
@endDate = '09/30/09'


SELECT b.database_name,
b.backup_start_date,
b.backup_finish_date,
b.user_name,
f.logical_name,
f.physical_name,
mf.physical_device_name,
f.file_type,
f.file_size,
b.backup_size
FROM msdb.dbo.backupfile f,
msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE f.backup_set_id = b.backup_set_id
AND b.media_set_id = mf.media_set_id
AND b.backup_start_date BETWEEN @startDate
AND @endDate
AND b.database_name = COALESCE
(@database,database_name)
ORDER BY b.database_name,
b.backup_start_date

Last time statistics were done?

-- view the date the statistics were last updated:

select 'index Name' = i.[name],
'Statistics Date' = stats_date(i.[object_id], i.index_id)
from sys.objects o
inner join sys.indexes i
on o.name = 'Employee'
and o.[object_id] = i.[object_id]

-- if you need to update all the indexes:

update statistics HumanResources.Employee
with fullscan

Find fragmentation (globally)

Ok. As I am working through this certification, it is getting easier.
this is a global way to work with indexes.

select *
into #t
from sys.dm_db_index_physical_stats(null, null, null, null, null)


Then you can find the database name by selecting this:

select db_name(database_id) from #t

if you want to sift through all the gobbldygook, you can filter out non-fragmented items:

select db_name(database_id) from #t
where
fragment_count > 0 and
avg_fragmentation_in_percent > 0

... and you can build and analyze as you go.
Happy hunting!

Find Fragmentation on a specific table

-- How to find fragmentation (yet another way)
declare @MyDatabase sysname,
@MyTable sysname

set @MyDatabase = 'Adventureworks'
set @MyTable = 'HumanREsources.Employee'
select
index_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from
sys.dm_db_index_physical_stats(db_id(@MyDatabase),
object_id(@MyTable),
null,
null, 'detailed')
where
index_id <> 0


also, find row-level i/o, locking and latching issues and access method activity:
by the way, this is an excerpt from SQL Server Books online

DECLARE @db_id smallint;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO