Default file path
Issue: SQL Server has three instance level settings that control where new database files and backups are placed by default: the default data path, the default log path, and the default backup path. When SQL Server is installed with default options, these paths point to subfolders under the SQL Server program directory on the system drive (typically C:\Program Files\Microsoft SQL Server...).
This finding identifies instances where these defaults are still pointing to the C drive even though the server has additional drives intended for database storage. The condition is commonly seen on instances installed quickly without customizing the storage configuration during setup.
Problem: Placing data files, log files, or backups on the C drive consumes space on the operating system volume, which is sized for Windows and SQL Server binaries rather than user data. If the C drive fills up, Windows itself can become unstable, paging fails, and the SQL Server service may stop or refuse to start, causing an outage that affects every database on the instance.
Beyond stability, mixing data and log files on the same drive eliminates the I/O performance benefits of separating sequential log writes from random data file activity. Backups stored on C also share the same physical disk as the live data, meaning a single drive failure or corruption event can destroy both the database and its backup at the same time.
These issues often hide until a database is created without an explicit path or until a developer or junior administrator uses default settings, at which point files land on C silently and grow unnoticed.
Learn More...Password protected backups
Issue: SQL Server historically supported a PASSWORD and MEDIAPASSWORD option on the BACKUP command, allowing administrators to require a password before a backup file could be restored. Beginning with SQL Server 2012, Microsoft discontinued the ability to create new password protected backups, although existing password protected backup files can still be restored.
This finding flags backup files that rely on password protection rather than proper encryption.
Problem: The password protection on native SQL Server backups is weak by design and was never intended as real security. Microsoft itself documents that the protection is weak and explicitly recommends against relying on it. The password only blocks restore operations through the RESTORE command interface; the backup file itself is not encrypted, meaning anyone with access to the file can extract data using third party tools or by parsing the file format directly. As a result, password protected backups create a false sense of security: administrators may treat them as protected and store them in less secure locations, when in reality the underlying data is fully exposed.
Beyond the security weakness, the feature is deprecated and unavailable for new backups in supported versions, so any reliance on it is also a maintenance and migration liability.
Learn More...Database with multiple log files
Issue: SQL Server database has one primary transaction log file by design, and SQL Server writes to the log sequentially regardless of how many log files exist. Despite this, some databases are configured with multiple transaction log files, often added in response to a one-time emergency such as the original log volume running out of space, with the intent to remove the extra file later once the situation was resolved.
Problem: Multiple transaction log files provide NO performance benefit. Unlike data files, which can be spread across multiple files in a filegroup to parallelize I/O, the transaction log is written sequentially, so adding more log files does not increase throughput. The extra files simply add complexity: each one must be backed up, restored, and managed alongside the primary log, and they consume storage on whatever volumes they reside on.
Forgotten secondary log files often live on volumes that were never intended to be permanent log storage, creating dependencies that surprise teams during disaster recovery or migrations. They can also obscure the real cause of past log growth issues, since the existence of multiple log files suggests a problem was worked around rather than solved.
Learn More...Database file growth
Issue: Every SQL Server database file (data and log) has an autogrowth setting that controls how the file expands when it runs out of space. Autogrowth can be configured as either a percentage of the current file size or a fixed amount in megabytes. The default for new databases was historically been 1 MB for data files and 10 percent for log files, both of which are problematic. This finding flags any database file configured for percentage-based growth or fixed growth smaller than 64 MB.
Problem: Small autogrowth increments cause frequent growth events, and each growth event briefly pauses database activity while the file is extended, which can cause query timeouts on busy systems. Percentage based growth is even worse because as the file gets larger, each growth event also gets larger and takes longer, creating unpredictable pauses. Frequent log file growth also produces an excessive number of virtual log files (VLFs) inside the log, which slows down recovery, log backups, and database startup.
Without Instant File Initialization enabled, every data file growth must be zeroed out first, multiplying the impact. Together, these issues turn a routine background operation into a recurring source of performance problems and unpredictable behavior.
Learn More...Database log file size
Issue: Every SQL Server database has at least one data file (.mdf or .ndf) and one transaction log file (.ldf). The data files hold the actual data and indexes, while the log file records every transaction for recovery and rollback purposes. Under normal operation, the log file should be sized appropriately for the workload but generally remains smaller than the total data file size. This finding flags databases where the transaction log file has grown larger than the combined data files, which is almost always a sign that something is wrong.
Problem: A log file that exceeds the size of the data files indicates that log truncation is not occurring as expected. Common causes include the database being in FULL or BULK_LOGGED recovery model without regular transaction log backups, a long running open transaction blocking truncation, replication or Change Data Capture falling behind, an Always On Availability Group replica that is disconnected or far behind in synchronization, or a one time operation such as a massive data load that forced the log to grow. The immediate consequence is wasted storage, but the deeper problem is that whatever is preventing truncation is also preventing recovery point objectives from being met. If the log file fills the disk, all writes to the database stop, causing an immediate outage.
Learn More...Compatibility level
Issue: Every SQL Server database has a compatibility level setting that controls which version of the query optimizer and which T-SQL behaviors apply to that database. The compatibility level is expressed as a number tied to a SQL Server version (for example, 150 for SQL Server 2019, 160 for SQL Server 2022). When a database is restored or attached to a newer SQL Server instance, its compatibility level does not automatically change, which is why databases often run at a level lower than the host instance supports.
Problem: Running at a lower compatibility level means the database is not benefiting from improvements in the query optimizer, intelligent query processing features, and T-SQL enhancements introduced in newer versions. Features such as adaptive joins, batch mode on rowstore, table variable deferred compilation, scalar UDF inlining, and parameter sensitive plan optimization are tied to specific compatibility levels and will not activate on older settings.
Beyond performance, lower compatibility levels also lock the database out of newer T-SQL syntax and behaviors that developers may want to use. Older compatibility levels (specifically 100 and below) are also deprecated and will eventually be removed, creating a future upgrade obstacle.
Learn More...Max degree of parallelism
Issue: Max degree of parallelism (MAXDOP) is an instance level setting that limits how many CPU cores a single query can use when SQL Server chooses a parallel execution plan. The default value of 0 means SQL Server may use all available logical processors for a parallel query, which on modern servers with many cores is rarely optimal. The opposite extreme, MAXDOP set to 1, disables parallelism entirely and forces every query to run serially regardless of size or complexity.
Problem: When MAXDOP is 0 on a server with many cores, large queries can grab every available processor, causing severe contention with other workloads, excessive CXPACKET waits, and uneven performance across concurrent queries. NUMA based hardware suffers further because SQL Server must coordinate workers across memory nodes, adding latency.
When MAXDOP is set to 1, SQL Server cannot use parallelism for any query, which often crushes performance for analytical queries, large index maintenance operations, and any workload that benefits from spreading work across cores. MAXDOP of 1 is sometimes recommended for specific applications such as older versions of SharePoint or Dynamics, but applying it broadly to a general purpose instance is almost always a mistake.
Learn More...Cost threshold for parallelism
Issue: The cost threshold for parallelism is an instance level setting that controls when SQL Server considers using a parallel execution plan instead of a serial one. When the optimizer estimates a query's cost is above this threshold, SQL Server may distribute the work across multiple CPU cores. The default value is 5, which was set in the SQL Server 7.0 era based on hardware that bears no resemblance to modern servers. On today's systems, this value is widely considered far too low.
Problem: With the threshold set to 5, even trivial queries become eligible for parallelism, which is rarely beneficial. Coordinating parallel execution across cores adds overhead, and for small queries this overhead often exceeds any gain from parallel processing. The result is excessive CPU consumption, increased context switching, and elevated CXPACKET and CXCONSUMER waits as workers synchronize unnecessarily.
On busy OLTP systems, a low cost threshold can cause noticeable performance degradation and crowd out larger queries that genuinely benefit from parallelism. Symptoms commonly include high CPU utilization with no clear cause, query plans that go parallel for no apparent reason, and wait statistics dominated by parallelism related waits.
Learn More...Memory dump
Issue: A stack dump is a diagnostic file that SQL Server generates automatically when it encounters a serious internal error, such as an access violation, an assertion failure, or an unhandled exception within the database engine. The dump file captures the state of the SQL Server process at the moment of failure, including thread information, memory contents, and the call stack, and is written to the SQL Server LOG directory along with corresponding entries in the error log. This finding indicates that one or more stack dumps have been generated on the instance.
Problem: Stack dumps are not normal. They indicate that SQL Server hit a condition the engine was not designed to handle gracefully, which is almost always a sign of something seriously wrong. Possible causes include software bugs in SQL Server itself, hardware problems such as failing memory or storage, corruption in databases or system metadata, faulty drivers, or interference from third party software such as antivirus or filter drivers. Some stack dumps are isolated incidents, but recurring dumps often precede broader stability problems including service crashes, data corruption, or extended outages. Ignoring stack dumps means missing the early warning signs of failures that may escalate.
Learn More...SQL Agent offline
Issue: SQL Server Agent is the scheduling and automation service that runs alongside the SQL Server database engine. It is responsible for executing scheduled jobs (backups, integrity checks, index maintenance, ETL processes), responding to alerts, and sending notifications to operators. This finding indicates that the SQL Server Agent service is currently stopped or disabled on an instance where it should be running.
Problem: When SQL Server Agent is offline, every scheduled job stops running. This commonly means backups are not being taken, integrity checks are not running, indexes are not being maintained, and ETL or business processes that depend on SQL Agent are silently failing. Alerts for critical errors such as severity 19 through 25 or I/O errors 823, 824, and 825 will not fire, removing your early warning system for corruption and hardware problems.
Because Agent failures often go unnoticed until someone needs a backup or notices stale data, the impact may not become apparent until a crisis occurs and recovery options have already been compromised.
Learn More...About sp_checks
This page contains a list of SQL Server configuration checks performed by Straight Path's suite of sp_check tools. For more details about our free tools, select one from the following list: