petesql

-- Query to display current sessions and their details
-- Displays session details such as host, program, blocking session, CPU time, waits, and SQL text
SELECT 
     s.host_name, 
     s.program_name, 
     r.blocking_session_id AS 'Blk by', 
     COALESCE(
         QUOTENAME(DB_NAME(st.dbid)) + N'.' + 
         QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + 
         QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), 

petesql

-- Get AAG Latency
SELECT 
    CAST(DB_NAME(database_id)as VARCHAR(40)) database_name,
    Convert(VARCHAR(20),last_commit_time,22) last_commit_time
    ,CAST(CAST(((DATEDIFF(s,last_commit_time,GetDate()))/3600) as varchar) + ' hour(s), '
    + CAST((DATEDIFF(s,last_commit_time,GetDate())%3600)/60 as varchar) + ' min, '
    + CAST((DATEDIFF(s,last_commit_time,GetDate())%60) as varchar) + ' sec' as VARCHAR(30)) time_behind_primary
    ,redo_queue_size
    ,redo_rate
    ,CONVERT(VARCHAR(20),DATEADD(mi,(redo_queue_size/redo_rate/60.0),GETDATE()),22) estimated_completion_time

petesql

-- Get db log space usage information
CREATE TABLE #LogSpaceUsage (
    DatabaseName NVARCHAR(100),
    TotalLogSize_MB DECIMAL(18, 2),
    LogSpaceUsed_MB DECIMAL(18, 2),
    LogSpaceUsed_Percent DECIMAL(5, 2),
    LogSpaceFree_MB DECIMAL(18, 2)
)

-- Loop through all databases

petesql

-- MSSQL Quick Performance Troubleshooting Script 
SELECT 
     s.host_name, 
     s.program_name, 
     r.blocking_session_id 'Blk by', 
     Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid,st.dbid)), '') AS command_text, 
     s.session_id, 
     r.cpu_time, 
     r.logical_reads, 

petesql

-- Get db filenames, paths, sizes and growth info
SELECT DB_NAME(database_id) AS 'Database Name',
	file_id, name, physical_name, type_desc, state_desc,
	is_percent_growth, growth,
	CONVERT(bigint, growth/128.0) AS 'Growth in MB',
	CONVERT(bigint, size/128.0) AS 'Total Size in MB', max_size
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME(database_id), file_id OPTION (RECOMPILE);

petesql

-- Get CPU Utilization History for last 4hrs (one minute intervals)
DECLARE @ts_now bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info WITH (NOLOCK)); 
SELECT
	SQLProcessUtilization, 
	SystemIdle As SystemIdleProcess, 
	100 - SystemIdle - SQLProcessUtilization AS OtherProcessCPUUtilization, 
	DATEADD(ms, -1 * (@ts_now - timestamp), GETDATE()) AS EventTime
FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
              record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle, 
              record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization, timestamp

petesql

-- Get all SQL Agent Jobs with Job Steps
SELECT
    s.name AS JobName,
    dp.name AS JobOwner,
    sc.name AS JobCategory,
    s.description AS JobDescription,
    CASE s.enabled
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END AS IsEnabled,

petesql

-- List all SQL Agent Jobs
SELECT
    s.name AS JobName,
    dp.name AS JobOwner,
    sc.name AS JobCategory,
    s.description AS JobDescription,
    CASE s.enabled
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END AS IsEnabled,

petesql

-- Generate SQL for backing up all databases
DECLARE @BackupDirectory NVARCHAR(255) = 'D:\mssql_backups'; -- Replace with your desired backup directory
DECLARE @Compression BIT = 1; -- 1 for compression, 0 for no compression
DECLARE @NumberOfFiles INT = 2; -- Specify the desired number of backup files
DECLARE @BackupDate SMALLDATETIME = CONVERT(SMALLDATETIME, GETDATE(), 101); -- Get current date with MM/DD/YYYY format
DECLARE @FormattedBackupDate NVARCHAR(20) = REPLACE(REPLACE(CONVERT(NVARCHAR(20), @BackupDate, 106), ' ', '_'), ',', '');
DECLARE @BackupFiles TABLE (FileIndex INT, DatabaseName NVARCHAR(255), FileName NVARCHAR(255));

-- Generate backup file names excluding tempdb
INSERT INTO @BackupFiles

petesql

-- Generate SQL for running a full database backup 
DECLARE @DatabaseName NVARCHAR(255) = 'Jupiter'; -- Replace with your actual database name
DECLARE @BackupDirectory NVARCHAR(255) = 'D:\mssql_backups'; -- Replace with your desired backup directory

DECLARE @Compression BIT = 1; -- 1 for compression, 0 for no compression
DECLARE @NumberOfFiles INT = 1; -- Specify the desired number of backup files

DECLARE @BackupDate SMALLDATETIME = CONVERT(SMALLDATETIME, GETDATE(), 101); -- Get current date with MM/DD/YYYY format
DECLARE @FormattedBackupDate NVARCHAR(20) = REPLACE(REPLACE(CONVERT(NVARCHAR(20), @BackupDate, 106), ' ', '_'), ',', '');