As someone who is trying to use the DMVs as much as possible, there are few scripts that can give you a quick heads up
This one will give you an info about the full backups and tran log backups, how long it took it, what was the size of the backup,where it is located,expiration date(if it has),logical device (if it exists as well), and server name.
Keep in mind its filtered for backups for past 7 days, and it displays all databases on the server, but you can sort it up as u please
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.name AS backupset_name,
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
This one will show you records that are placed in a log buffer, in what state transaction was, how many records are logged, size in bytes ,and a query that executed it. In a nutshell it displays all inserts/deletes/update from an active transaction that are not committed/rolled back yet
DES.[login_name] AS [Login Name],
DB_NAME (DTDT.database_id) AS [Database],
DTDT.[database_transaction_begin_time] AS [Begin Time],
-- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],
WHEN 1 THEN 'Read/write'
WHEN 2 THEN 'Read-only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
END AS [Transaction Type],
WHEN 0 THEN 'Not fully initialized'
WHEN 1 THEN 'Initialized, not started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended'
WHEN 4 THEN 'Commit initiated'
WHEN 5 THEN 'Prepared, awaiting resolution'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Rolling back'
WHEN 8 THEN 'Rolled back'
END AS [Transaction State],
DTDT.[database_transaction_log_record_count] AS [Log Records],
DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],
DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],
DEST.[text] AS [Last Transaction Text],
DEQP.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions DTDT
INNER JOIN sys.dm_tran_session_transactions DTST
ON DTST.[transaction_id] = DTDT.[transaction_id]
INNER JOIN sys.[dm_tran_active_transactions] DTAT
ON DTST.[transaction_id] = DTAT.[transaction_id]
INNER JOIN sys.[dm_exec_sessions] DES
ON DES.[session_id] = DTST.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DEC.[session_id] = DTST.[session_id]
LEFT JOIN sys.dm_exec_requests DER
ON DER.[session_id] = DTST.[session_id]
CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST
OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;
-- ORDER BY [Duration ms] DESC;
And the last one is IO related with info on all databases: Log reads,writes, read and write stall (how much time sql server had to wait to write or read from a log) and Read/Write ratio on a log and data files. Keep in mind that these are cumulative stats, and they reset only after restart, but you can insert snapshots in a table and keep a track of it.
fs.database_id as [DB ID], fs.file_id as [File Id], mf.name as [File Name]
,mf.physical_name as [File Path], mf.type_desc as [Type], fs.sample_ms as [Time]
,fs.num_of_reads as [Reads], fs.num_of_bytes_read as [Read Bytes]
,fs.num_of_writes as [Writes], fs.num_of_bytes_written as [Written Bytes]
,fs.num_of_reads + fs.num_of_writes as [IO Count]
,convert(decimal(5,2),100.0 * fs.num_of_bytes_read /
(fs.num_of_bytes_read + fs.num_of_bytes_written)) as [Read %]
,convert(decimal(5,2),100.0 * fs.num_of_bytes_written /
(fs.num_of_bytes_read + fs.num_of_bytes_written)) as [Write %]
,fs.io_stall_read_ms as [Read Stall], fs.io_stall_write_ms as [Write Stall]
,case when fs.num_of_reads = 0
else convert(decimal(12,3),1.0 * fs.io_stall_read_ms / fs.num_of_reads)
end as [Avg Read Stall]
,case when fs.num_of_writes = 0
else convert(decimal(12,3),1.0 * fs.io_stall_write_ms / fs.num_of_writes)
end as [Avg Write Stall]
sys.dm_io_virtual_file_stats(null,null) fs join
sys.master_files mf with (nolock) on
fs.database_id = mf.database_id and fs.file_id = mf.file_id
join sys.databases d with (nolock) on
d.database_id = fs.database_id
fs.num_of_reads + fs.num_of_writes > 0;
Hope it gives you few startup points, in case your boss is not willing to invest in 3rd party tools, or you dont feel like setting up data collector tool