— How to get instance name?
— How to get SQL Server version?
— How to show the full space percentage of the log files?
— How to show free space on disks?
— How to get last restarted date of SQL Server?
select sqlserver_start_time from sys.dm_os_sys_info
— How to check databases size in SQL Server?
exec sp_helpdb 'AdventureWorks2014' ---> it returns details for spesific database
exec sp_databases --- alternative
— How to get blocking queries?
select * from sys.sysprocesses where blocked<>0
— List databases with state
select name,state_desc from sys.databases
— How to get all my databases last full, diff, tran backup info
d.name as db_name,
when 'D' then 'Full Backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
end as 'backup_type',
cast(bs.compressed_backup_size/1048576 as decimal(15,2)) as 'backup_size_in_MB',
row_number() over(partition by d.database_id,backup_types.Backup_Type order by bs.backup_start_date desc) as rwn
from sys.databases as d
select 'D' as Backup_Type
) as backup_types
left join msdb.dbo.backupset as bs on (d.name=bs.database_name and backup_types.Backup_Type=bs.type)
left join msdb.dbo.backupmediafamily as bmf on bs.media_set_id=bmf.media_set_id
where isnull(bs.is_copy_only,0)=0 --- if you want to filter copy_only backups
) as tbl
and tbl.database_id>4 --- eliminate system databases
order by tbl.database_id asc
— How to get all indexes on a spesific table in SQL Server?
-- exec sp_helpindex 'Table_Name'
exec sp_helpindex 'HumanResources.Department'
— How to list all indexes with last statistics updated date
SELECT OBJECT_NAME(object_id) as table_name,
name AS index_name,
STATS_DATE(object_id, index_id) AS StatsUpdated
order by StatsUpdated desc