— Check progress of any running BACKUP or RESTORE operations
SELECT
r.session_id,
r.command, — BACKUP DATABASE, RESTORE DATABASE, etc.
r.percent_complete, — Progress percentage
r.start_time, — When the operation started
r.estimated_completion_time / 1000 AS est_completion_seconds,
DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) AS est_completion_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
r.wait_type,
r.wait_time / 1000 AS wait_seconds,
r.last_wait_type,
t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN (‘BACKUP DATABASE’, ‘BACKUP LOG’, ‘RESTORE DATABASE’, ‘RESTORE LOG’, ‘RESTORE HEADERONLY’)
ORDER BY r.start_time;

/*
— This script generates alter statements for the user databases in an instance and takes away any hard limits.
— The rule is minimum autogrow increment 100MB, between 500-5000MB, 500MB increment and above 5000MB, 1000MB increment
*/

SET NOCOUNT ON;

SELECT
d.name AS DatabaseName,
mf.name AS FileName,
mf.type_desc AS FileType,

— Current size
CAST(CAST(mf.size AS BIGINT) * 8 / 1024 AS BIGINT) AS SizeMB,

— Current FILEGROWTH
CASE
WHEN mf.is_percent_growth = 1
THEN CAST(mf.growth AS NVARCHAR(10)) + ‘%’
ELSE CAST(CAST(mf.growth AS BIGINT) * 8 / 1024 AS NVARCHAR(20)) + ‘MB’
END AS CurrentFileGrowth,

— Current MAXSIZE
CASE
WHEN mf.max_size = -1
THEN ‘UNLIMITED’
ELSE CAST(CAST(mf.max_size AS BIGINT) * 8 / 1024 AS NVARCHAR(20)) + ‘MB’
END AS CurrentMaxSize,

— Proposed growth
CASE
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 < 500 THEN 100
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 BETWEEN 500 AND 5000 THEN 500
ELSE 1000
END AS NewFileGrowthMB,

— Generated SQL
'ALTER DATABASE [' + d.name + '] MODIFY FILE ' +
'( NAME = N''' + mf.name + ''', ' +
'FILEGROWTH = ' +
CAST(
CASE
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 4 — user databases only
AND d.state_desc = ‘ONLINE’
AND d.is_read_only = 0
AND d.is_distributor = 0
ORDER BY
d.name,
mf.type_desc,
mf.name;

— Query to check for any database triggers in a Database
SELECT
t.name,
t.is_disabled,
te.type_desc AS trigger_event
FROM sys.triggers t
JOIN sys.trigger_events te ON t.object_id = te.object_id
WHERE t.parent_class_desc = ‘DATABASE’;

–If found they can be disabled/enabled with:
/*
USE [Admin]
go
DISABLE TRIGGER tr_MStran_alterschemaonly ON DATABASE;
GO
USE [Admin]
go
ENABLE TRIGGER tr_MStran_alterschemaonly ON DATABASE;
GO
*/

/*
Checking contents and size of filesystems
*/

SELECT file_or_directory_name
, level, is_directory, creation_time, (size_in_bytes /1024 ) as [Size_in_KB], (size_in_bytes /1024/1024/1024 ) as [Size_in_GB]
FROM sys.dm_os_enumerate_filesystem(N’R:\SQLServerBackups\LDESQLDBUAT002\’, N’*.*’)
order by level, creation_time desc

/* Other methods to check contents of filesystems

SELECT *
FROM OPENROWSET(BULK ‘R:\SQLServerBackups\*’, FORMAT = ‘diff’) AS FileList;
go

EXEC master..xp_dirtree ‘R:\SQLServerBackups\eu-dave-sqldb-prd’, 1, 1
go

*/

/* To find free space on the Drives:
SELECT DISTINCT
dovs.volume_mount_point AS Drive,
CAST(dovs.total_bytes / 1048576.0 / 1024.0 AS DECIMAL(10, 2)) AS TotalSize_GB,
CAST(dovs.available_bytes / 1048576.0 / 1024.0 AS DECIMAL(10, 2)) AS FreeSpace_GB,
CAST(dovs.available_bytes * 100.0 / dovs.total_bytes AS DECIMAL(10, 2)) AS PercentFree
FROM
sys.master_files mf
CROSS APPLY
sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs;
*/

/* For free space on all drives
SELECT
fixed_drive_path AS [Drive],
drive_type_desc AS [Type],
CAST(free_space_in_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS [Free_GB]
FROM sys.dm_os_enumerate_fixed_drives
go
*/

/*
Check fragmentation of a database
*/

SELECT S.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 30
ORDER BY DDIPS.avg_fragmentation_in_percent desc

The Hallengren Maintenance scripts are an excellent collection of maintenance scripts, here are some options I often set for the IndexOptimize and Full Backup jobs

Backup

EXECUTE [DatabaseBackup]
@Databases = ‘USER_DATABASES’,
@Directory = N’D:\Backups’,
@BackupType = ‘FULL’,
@Verify = ‘Y’,
@CleanupTime = 28, –Hours beyond which it will remove the old backup file
@CleanupMode = ‘BEFORE_BACKUP’, — Delete the Backup file before or after the new backup
@Checksum = ‘Y’,
@LogToTable = ‘Y’,
@MinBackupSizeForMultipleFiles = 5120, –Size before it splits the backup into stripes
@NumberOfFiles = 4, –Number of stripes
@BufferCount = 12,
@MaxTransferSize = 4194304

IndexOptimize

EXECUTE [IndexOptimize]
@Databases = ‘USER_DATABASES’,
@TimeLimit = 14400, — 4 hours total run time
@LockTimeout = 300, — 5 minutes (amount of time it waits for a lock on any table)
@LockMessageSeverity = 10, –(The message severity raised if lock not obtained)
@LogToTable = ‘Y’