Tag: SQL Server Configuration

  • How to Right-Size SQL Server Database Files

    Auto-growth is not the problem. Unplanned, reactive growth is. When database files are undersized, SQL Server is forced to extend them repeatedly under load. Each growth event introduces a pause. If those pauses occur during peak workload, they can cause noticeable latency, increased I/O pressure, and in some cases application timeouts. Right-sizing database files is…

    Written by

    Peter Whyte

  • SQL Server: ALTER DATABASE SET ENABLE_BROKER Taking a Long Time (Fix)

    After a recent Availability Group failover, Database Mail stopped sending emails. On investigation, the underlying cause was Service Broker not being enabled on the system database required by Database Mail. To confirm the status, I ran: The is_broker_enabled column was returning 0, indicating that Service Broker was disabled for that database. I then attempted to…

    Written by

    Peter Whyte

  • SQL Server: Ad hoc Update to System Catalogs is Not Supported (Fix)

    While enabling show advanced options using sp_configure, I encountered an unexpected failure when executing RECONFIGURE: The change would not apply, and any further configuration attempts failed in the same way. This issue typically appears on systems where older configuration settings have been modified and never reset. In most cases, the root cause is the legacy…

    Written by

    Peter Whyte

  • Enabling TCP Connections in SQL Server

    TCP/IP must be enabled in SQL Server for remote connections to work. If it’s disabled, applications can’t connect; even if authentication, ports, and firewall rules are all correct. This post shows two reliable ways to enable TCP connections in SQL Server: Both approaches require a service restart before changes take effect. When You Need to…

    Written by

    Peter Whyte

  • DBA Scripts: Get Services Information

    Part of the DBA-Tools Project. A SQL Server instance is really a handful of Windows services working together, and it is surprisingly common for one of them to be quietly misconfigured. The SQL Server Agent service set to Manual startup instead of Automatic. A service still running under a personal domain account that leaves on…

    Written by

    Peter Whyte

  • DBA Scripts: Get Instance Configuration Snapshot

    Part of the DBA-Tools Project. Every sp_configure setting on an instance tells a small story: a value someone changed deliberately, a value nobody’s touched since install, or a value that’s been changed but is still waiting for a restart to actually take effect. Without a full snapshot, most of that story is invisible, and configuration…

    Written by

    Peter Whyte

  • DBA Scripts: Get MAXDOP Configuration

    Part of the DBA-Tools Project. Max Degree of Parallelism is one of the first things worth checking on any server, and one of the most commonly wrong. The default of 0 lets a single query use every scheduler on the box, which sounds efficient until 20 concurrent OLTP queries all decide to go parallel at…

    Written by

    Peter Whyte

Key Links

DBA Scripts: The Complete Guide
Query and Performance Tuning
High Availability
Security
Storage and Capacity
Installation and Patching
Backups and Recovery

Blog Categories

Latest Posts

Blog Tags

Access (4) Always On Availability Groups (4) Auditing (7) Backups (6) Blocking (4) Blocking Locking (6) Collectors (7) Database Backups & Recovery (8) Database Backups and Recovery (5) Database Maintenance (7) DBA Tools (24) Disk Space (5) Extended Events (8) Features (6) High Availability (6) Indexes (7) Installation (7) Inventory (8) Jobs (3) Maintenance (7) Migration (15) Monitoring (27) Performance (21) Performance Troubleshooting (9) PowerShell (4) Query Behaviour (5) Security (10) SQL Agent (5) SQL Scripts (17) SQL Server Administration (10) SQL Server Configuration (7) SQL Server Errors (5) SQL Server High Availability (4) SQL Server Internals (3) SQL Server Management Studio (SSMS) (6) SQL Server Monitoring (7) SQL Server Networking (9) SQL Server Security (7) Storage and Capacity (7) T-SQL (10) Temp DB (3) Tracing (7) Transaction Logs (4) Troubleshooting (24) Windows Server (4)