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), ' ', '_'), ',', '');

petesql

-- Generate SQL script to disable enabled SQL Server Agent jobs
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @sqlScript NVARCHAR(MAX) = ''

DECLARE job_cursor CURSOR FOR
    SELECT job_id
    FROM msdb.dbo.sysjobs
    WHERE enabled = 1; -- Only select jobs that are currently enabled

OPEN job_cursor

petesql

-- SQL Server User & Permissions Audit Script
USE master
GO

DECLARE
    @errorMessage nvarchar(4000),
    @errorNumber int,
    @errorSeverity int,
    @errorState int,
    @errorLine int,