sp_who3 works like sp_who2 but adds the running SQL text, wait type, reads, writes and elapsed time, using the sys.dm_exec_sessions and sys.dm_exec_requests DMVs. It only lists sessions that are running a request. For much more detail use Adam Machanic’s sp_WhoIsActive. Tested on SQL Server 2017 and 2025 and safe to run more than once. USE…
Which trace flags to use on SQL Server 2016 to 2022. Most of the old “always on” flags (1117, 1118, 2371, 4199, 9481) are now default behaviour or a per-database setting, so this script turns them off and keeps only the ones still worth having. Tested on SQL Server 2017 and 2025. — Trace flags…
Creates two SQL Agent jobs, DatabaseIntegrityCheck – SYSTEM_DATABASES and DatabaseIntegrityCheck – USER_DATABASES, which run Ola Hallengren’s DatabaseIntegrityCheck (DBCC CHECKDB) from the SQLDBA database every Sunday at 6 am and email DBA_Alerts if they fail. Install the Maintenance Solution in SQLDBA first (see the Database_Integrity_Check post). Run it in SSMS normal mode, not SQLCMD mode. Tested…
A database integrity check runs DBCC CHECKDB to find corruption before it finds you: damaged pages are far easier to deal with when you spot them early and still have a clean backup to restore from. I run it weekly with Ola Hallengren’s DatabaseIntegrityCheck, and Microsoft’s DBCC CHECKDB documentation covers what the checks do. Installing…
This index job script creates the SQL Agent job IndexOptimize – USER_DATABASES, which runs Ola Hallengren’s IndexOptimize from the SQLDBA database every Friday at 7 pm and emails DBA_Alerts if it fails. Install the Maintenance Solution in SQLDBA first (see the Index Optimize post) and create the operator (see Create_Operator_and_Standard_Alerts). Run it in SSMS normal…
This script will create backup objects and jobs in a SQLDBA database: a config table, a backup log, an exclusion list, the backup, copy, archive and retention procedures, and SQL Agent jobs for full, differential, log and clean-up. Backups are written locally, then copied to a central share. Change \\backupserver\ to your backup share. Tested…
Index Optimize is Ola Hallengren’s IndexOptimize procedure, which rebuilds or reorganises indexes only when they pass a fragmentation threshold and updates the statistics that have changed. It’s what I use for weekly index and statistics maintenance instead of a maintenance plan. Microsoft’s guide to optimising index maintenance covers the background. Installing Index Optimize This post…
This script will create an operator and standard alerts so SQL Server tells you about serious problems as they happen, rather than when a user does. It creates the DBA_Alerts operator and 13 standard alerts: corruption errors 823, 824 and 825, severity 17 to 25, and database option changes (5084), all emailing the operator. Change…
Tells SQL Server Agent to use Database Mail (profile SQL_Alerting) so it can send alert and job-failure emails. Set up Database Mail first, then run this and restart SQL Server Agent. Windows only; on Linux use mssql-conf. Tested on SQL Server 2017 and 2025 and safe to run more than once. — 06.Setup_SQL_Server_Agent_Alerting_System.sql — Author:…
Sets up tempdb: one data file per logical CPU (up to 8), all the same size and growth, plus the log. Missing files are added next to the first data file; existing files are never shrunk. Trace flags 1117 and 1118 are not needed for this on SQL Server 2016 and later. Tested on SQL…
Post-install configuration for a new instance: max server memory and MAXDOP worked out from the server’s memory and CPUs, cost threshold for parallelism, optimize for ad hoc workloads, backup compression and checksum, the DAC, error log count, model growth and Query Store. Run it as it is first: it only shows what it would change….
Creates a SQL Agent job, (00:00) Maintenance – CycleErrorLogs, that starts a new error log every night so each log stays small and easy to read. If the job already exists it is left alone. Tested on SQL Server 2017 and 2025 and safe to run more than once. — 02.Cycle_Error_Logs.sql — Author: Clive Roberts…
Gives msdb a sensible starting size and growth (50 MB, growing 200 MB) so job and backup history do not grow it in tiny steps. It only ever grows a file, never shrinks one. Tested on SQL Server 2017 and 2025 and safe to run more than once. — 01.Configure_MSDB.sql — Author: Clive Roberts DBA….
Precious Lite 2025 | All Rights Reserved. Precious Lite theme by Flythemes