SQL Server recipes
- Take a transaction log backupBackup & Log
- Take FULL and DIFFERENTIAL backupsBackup & Log
- Why the log file grew / why it is not truncatingBackup & Log
- Shrink the log fileBackup & Log
- Show the VLF (Virtual Log File) countBackup & Log
- Find the oldest open transaction (ACTIVE_TRANSACTION)Backup & Log
- Add a data file to a databaseStorage
- Add a filegroupStorage
- Change file size / autogrowthStorage
- Move a data/log file to another locationStorage
- Add a data file to tempdbStorage
- Index rebuild / reorganizeMaintenance
- Update statisticsMaintenance
- Integrity check (DBCC CHECKDB)Maintenance
- Show index fragmentationMaintenance
- Kill a session (SPID)Session
- Find the blocking (lock) chainSession
- Show active queries and their costSession
- Configure with sp_configure (max server memory)Configuration
- General sp_configure setting and RECONFIGUREConfiguration
- Availability Group failoverConfiguration
- Change the database recovery modelConfiguration
- Monitor availability group health and lag (log send / redo queue, RPO)Always On & HA
- Forced failover (FORCE_FAILOVER_ALLOW_DATA_LOSS) and post-failover recoveryAlways On & HA
- Add a database to an AG (automatic seeding) and monitor seeding progressAlways On & HA
- Check the listener, its IPs and read-only routing configurationAlways On & HA
- Monitor log shipping status and latencyAlways On & HA
- Take an ad-hoc backup without breaking the chain (COPY_ONLY, striping, throughput)Backup & Log
- Back up to URL (Azure Blob SAS / S3-compatible storage)Backup & Log
- Backup history from msdb and missing backup checkBackup & Log
- See backup / restore / CHECKDB progress and estimated completionBackup & Log
- Take a tail-log backup before a restore (NORECOVERY / NO_TRUNCATE)Restore & Recovery
- Restore to a point in time (point-in-time restore, STOPAT)Restore & Recovery
- Inspect and verify a backup file (VERIFYONLY, HEADERONLY, FILELISTONLY)Restore & Recovery
- Find corrupt pages (suspect_pages) and restore single pagesRestore & Recovery
- Find the most expensive queries in the plan cache (CPU, reads, duration)Performance
- Wait statistics: what the server is waiting onPerformance
- Read past deadlocks from the system_health session (deadlock graph)Performance
- Create an Extended Events session: capture slow queries to file and read themPerformance
- Diagnose tempdb contention and space usagePerformance
- Enable Query Store, apply recommended settings and check its stateQuery Store
- Find plan regressions with Query Store (same query, different plans)Query Store
- Force a good plan or attach a hint with Query StoreQuery Store
- Read missing index suggestions from DMVs (with caveats)Maintenance
- Find unused / write-only indexesMaintenance
- Statistics health: when updated, how many rows changedMaintenance
- DBCC CHECKDB strategy: last good check, splitting the work, interpreting resultsMaintenance
- Limit reporting load with Resource Governor (pool, workload group, classifier)Configuration
- Set up TDE: master key, certificate, certificate backup and encryption progressSecurity
- Set up SQL Server Audit: logins, permission changes and table accessSecurity
- Find orphaned users and remap them to logins (after restore / AG)Security
- Login and permission audit: sysadmin members, risky permissions, weak passwordsSecurity
- Create a ledger table, read its change history and verify integritySecurity
- SQL Agent job failure history and last run statusSQL Agent