Having a record of the database backup history is particularly useful.  I use the following script a lot, but I find it’s main use is when I am logshipping and SOMEBODY breaks the recovery chain by taking an extra log backup.  This can give you an idea of who did it and where the log backup went.

SELECT TOP 50 bs.database_name, m.physical_device_name,
bs.user_name, bs.backup_start_date,
CASE bs.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END
AS BackupType
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily m ON bs.media_set_id = m.media_set_id
WHERE bs.database_name = 'AdventureWorks'
ORDER BY backup_start_date DESC, backup_finish_date
GO

If you are just after the last backup date of each database take a look at this one:

select T1.name as [DatabaseName],
MAX(T2.backup_finish_date) as [Last Backup Date]
from sys.databases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
on t2.database_name=t1.name
where t1.name not in ('model','tempdb')
and t2.type in ('D', 'I', 'G','F') — Data, incremental, file/filegroup or file/filegroup incremental
group by T1.name
order by t1.name

The script I use to monitor fragmentation is a very slightly modified version of the one found here which gives me a little additional information and excludes a bunch of indexes I probably don’t care about:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,indexstats.index_type_desc AS IndexType,indexstats.page_count AS [Page Count],
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id=indexstats.object_id
AND ind.index_id=indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent> 10
and indexstats.page_count>500
ORDER BY indexstats.avg_fragmentation_in_percent DESC

According to Paul Randall’s blog post on Where the Books Online Index Fragmentation Thresholds Come From, the generally accepted guidelines are to rebuild indexes larger than 1000 pages that have more than 30% fragmentation.  So in the query above I have trimmed out indexes with less than 500 pages and included page count in the output.  You can go to 1000 pages if you want, but I like to start with a more aerial view when I have to troubleshoot index fragmentation.  It’s also worth reorganising Indexes which are fragmented below 30% – according to the above article anywhere between 5 and 30% should be reorganised.

Paul also makes a really important point in the article quoted above, and that is that these numbers are made up to give us a starting point.  So use the script above freely, but don’t forget there are a lot of other factors involved in whether the indexes it returns are causing you problems, or your constant attempts to get them sitting nicely are actually doing nothing to improve your server performance and are just sucking up your time and effort.