Blog ala Vidar

Databases with sizes

SELECT 
  name,
  (SELECT SUM(size * 8 / 1024.00) 
		FROM sys.master_files
		WHERE database_id = D.database_id 
		  AND type = 0) AS DATA_MBSize,
  (SELECT SUM(size * 8 / 1024.00) 
		FROM sys.master_files
		WHERE database_id = D.database_id 
		  AND type = 1) AS LOG_MBSize
	FROM sys.databases AS D
	WHERE database_id > 4
	ORDER BY 2 DESC

Jobs

DECLARE @weekDay TABLE (
	mask INT, 
	maskValue VARCHAR(32)
)
 
INSERT INTO @weekDay
SELECT 1, 'Sunday' UNION All 
SELECT 2, 'Monday' UNION All 
SELECT 4, 'Tuesday' UNION All
SELECT 8, 'Wednesday' UNION All
SELECT 16, 'Thursday' UNION All
SELECT 32, 'Friday' UNION All
SELECT 64, 'Saturday';
 
WITH myCTE
AS(
    SELECT 
	  S.name AS ScheduleName, S.schedule_id, JS.job_id, 
	  CASE WHEN S.freq_type = 1 THEN 'Once' 
            WHEN S.freq_type = 4 
                AND S.freq_interval = 1 
                    THEN 'Daily'
            WHEN S.freq_type = 4 
                THEN 'Every ' + CAST(S.freq_interval AS VARCHAR(5)) + ' days'
            WHEN S.freq_type = 8 THEN 
				REPLACE(REPLACE(REPLACE(( 
					SELECT maskValue 
						FROM @weekDay AS x 
						WHERE S.freq_interval & x.mask <> 0 
						ORDER BY mask FOR XML Raw), 
				  '"/><row maskValue="', ', '), 
				  '<row maskValue="', ''), 
				  '"/>', '') + 
				  CASE WHEN S.freq_recurrence_factor <> 0 
                        AND S.freq_recurrence_factor = 1 
                            THEN '; weekly' 
						WHEN S.freq_recurrence_factor <> 0 
							THEN '; every ' + CAST(S.freq_recurrence_factor AS VARCHAR(10)) + ' weeks' END
						WHEN S.freq_type = 16 
							THEN 'On day ' + CAST(S.freq_interval AS VARCHAR(10)) + ' of every '
								+ CAST(S.freq_recurrence_factor AS VARCHAR(10)) + ' months' 
						WHEN S.freq_type = 32 
							THEN CASE WHEN S.freq_relative_interval = 1 THEN 'First'
									WHEN S.freq_relative_interval = 2 THEN 'Second'
									WHEN S.freq_relative_interval = 4 THEN 'Third'
									WHEN S.freq_relative_interval = 8 THEN 'Fourth'
									WHEN S.freq_relative_interval = 16 THEN 'Last'
								END + 
								CASE WHEN S.freq_interval = 1 THEN ' Sunday'
									WHEN S.freq_interval = 2 THEN ' Monday'
									WHEN S.freq_interval = 3 THEN ' Tuesday'
									WHEN S.freq_interval = 4 THEN ' Wednesday'
									WHEN S.freq_interval = 5 THEN ' Thursday'
									WHEN S.freq_interval = 6 THEN ' Friday'
									WHEN S.freq_interval = 7 THEN ' Saturday'
									WHEN S.freq_interval = 8 THEN ' Day'
									WHEN S.freq_interval = 9 THEN ' Weekday'
									WHEN S.freq_interval = 10 THEN ' Weekend'
								END + 
								CASE 
									WHEN S.freq_recurrence_factor <> 0 
									  AND S.freq_recurrence_factor = 1 THEN '; monthly'
									WHEN S.freq_recurrence_factor <> 0 
										THEN '; every ' + CAST(S.freq_recurrence_factor AS VARCHAR(10)) + ' months' 
								END
							WHEN S.freq_type = 64 THEN 'StartUp'
							WHEN S.freq_type = 128 THEN 'Idle'
						END AS frequency,
	  ISNULL('Every ' + CAST(S.freq_subday_interval AS VARCHAR(10)) + 
			CASE WHEN S.freq_subday_type = 2 THEN ' seconds'
				WHEN S.freq_subday_type = 4 THEN ' minutes'
				WHEN S.freq_subday_type = 8 THEN ' hours'
			END, 'Once') AS subFrequency, 
	  REPLICATE('0', 6 - LEN(S.active_start_time)) + CAST(S.active_start_time AS VARCHAR(6)) AS startTime, 
	  REPLICATE('0', 6 - LEN(S.active_end_time)) + CAST(S.active_end_time AS VARCHAR(6)) AS endTime, 
	  REPLICATE('0', 6 - LEN(JS.next_run_time)) + CAST(JS.next_run_time AS VARCHAR(6)) AS nextRunTime, 
	  CAST(JS.next_run_date AS CHAR(8)) AS nextRunDate
		FROM msdb.dbo.sysschedules AS S
		INNER JOIN msdb.dbo.sysjobschedules AS JS ON S.schedule_id = JS.schedule_id
		WHERE S.enabled = 1
)
 
SELECT 
  J.name AS jobName, S.scheduleName, S.frequency, S.subFrequency, 
  SUBSTRING(S.startTime, 1, 2) + ':' + 
		SUBSTRING(S.startTime, 3, 2) + ' - ' + 
		SUBSTRING(S.endTime, 1, 2) + ':' + 
		SUBSTRING(S.endTime, 3, 2) AS scheduleTime, -- HH:MM
  SUBSTRING(S.nextRunDate, 1, 4) + '/' + 
		SUBSTRING(S.nextRunDate, 5, 2) + '/' + 
		SUBSTRING(S.nextRunDate, 7, 2) + ' ' + 
		SUBSTRING(S.nextRunTime, 1, 2) + ':' + 
		SUBSTRING(S.nextRunTime, 3, 2) AS nextRunDate
	FROM msdb.dbo.sysjobs AS J
	INNER JOIN myCTE AS S ON J.job_id = S.job_id
	WHERE J.enabled = 1
	ORDER BY nextRunDate

Sessions

SELECT
  S.session_id, S.login_time, S.host_name, S.login_name, S.status AS session_status, R.status AS request_status,
  S.cpu_time AS session_cpu_time, S.reads AS session_reads, S.writes AS session_writes, S.logical_reads AS session_logical_reads,
  C.num_reads AS connection_num_reads, C.num_writes AS connection_num_writes, C.last_read AS connection_last_read,
  C.last_write AS connection_last_write, R.request_id, R.command AS request_command, R.open_transaction_count AS request_open_transaction_count,
  R.open_resultset_count AS request_open_resultset_count, R.total_elapsed_time AS request_total_elapsed_time,
  R.row_count AS request_row_count
	FROM syS.dm_exec_sessions AS S
	LEFT OUTER JOIN syS.dm_exec_connections AS C ON C.session_id = S.session_id
	LEFT OUTER JOIN syS.dm_exec_requests AS R ON R.session_id = S.session_id