It happens more often than you’d think. A server is joined to a domain, or taken off one, or the DBA who set it up leaves, and suddenly nobody can get into SQL Server as sysadmin. You don’t need a password to fix it. Anyone in the server’s local Administrators group can get back in…
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…
This post used to hold a 2016 copy of Ola Hallengren’s DatabaseIntegrityCheck procedure. Ola keeps his Maintenance Solution up to date (version 2026-09-12 at the time of writing), so always install the current version from his site rather than an old copy: SQL Server Integrity Check – documentation and examples Downloads – MaintenanceSolution.sql installs DatabaseIntegrityCheck…
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 mode, not SQLCMD mode, because…
Backup procedures, tables and jobs (full, differential, log and clean-up). Change \\backupserver\ to your backup share. Updated October 2026, tested on SQL Server 2022. /* Back up the master, msdb, and model databases to ensure that you can roll back to a previous configuration if problems occur. master: This database hosts system-level information. Back up…
This post used to hold a 2016 copy of Ola Hallengren’s IndexOptimize procedure. Ola keeps his Maintenance Solution up to date (version 2026-09-12 at the time of writing), so always install the current version from his site rather than an old copy: SQL Server Index and Statistics Maintenance – documentation and examples Downloads – MaintenanceSolution.sql…
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 [email protected] at the top to your own address before running it. Needs Database Mail and the Agent mail profile (see Setup_SQL_Server_Agent_Alerting_System). Tested on SQL Server 2017 and…
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….
declare @cmd nvarchar(200) set @cmd = ‘move \\server\UserDB\*.bak \\server\UserDB\Archive’ exec xp_cmdshell @cmd
Errors 823,824, and 825 To do this, run the following command: USE msdb GO EXEC msdb.dbo.sp_add_alert @name = N’823 – Read/Write Failure’, @message_id = 823, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 1 GO EXEC msdb.dbo.sp_add_alert @name = N’824 – Page Error’, @message_id = 824, @severity = 0, @enabled = 1,…
backup.sql BACKUP DATABASE [AdaptiveCR] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdaptiveCR.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdaptiveCR-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO MF20bak.cmd sqlcmd -S .\SQLEXPRESS -i C:\backup.sql Scheduled Tasks to call MF20bak.cmd
EXEC sys.sp_configure N’xp_cmdshell’, 1;
Precious Lite 2025 | All Rights Reserved. Precious Lite theme by Flythemes