There are multiple log files for tempdb
Issue: A SQL Server database has one transaction log file by design, and SQL Server writes to the log sequentially regardless of how many log files exist. Tempdb is no exception to this rule and operates with a single log file under standard configuration.
This finding identifies instances where tempdb has been configured with more than one transaction log file. The configuration is often the result of a one-time emergency response, where a second log file was added to handle a tempdb log space issue and was never removed afterward, although sometimes it is due to a misunderstanding of how the transaction log functions.
Problem: Multiple transaction log files provide no performance benefit. Unlike data files, which can be spread across multiple files in a filegroup to reduce allocation contention, the transaction log is written sequentially, so adding more log files does not increase throughput or reduce latency. The extra files simply add complexity with no offsetting benefit.
For tempdb specifically, multiple log files can complicate operations and management. Tempdb files are recreated at every service restart, and any non-standard configuration must be maintained correctly across restarts. Forgotten secondary log files can also live on volumes that were never intended for permanent tempdb storage, creating storage dependencies that are not obvious to the team and that complicate disaster recovery and migrations.
The presence of multiple tempdb log files often indicates that a past tempdb log issue was worked around rather than fully resolved. The original problem (a long-running transaction, a runaway version store, or a workload that produced unexpected tempdb log growth) may still be present, only masked by the additional log space. Reviewing the multiple-log-file condition is an opportunity to identify and address the root cause.
Learn More...Memory-optimized tempdb
Issue: Memory-Optimized Tempdb Metadata is a SQL Server feature introduced in SQL Server 2019 as part of the In-Memory Database feature umbrella. When enabled, it moves the tempdb system metadata tables (used to track temporary tables, table variables, and similar objects) into memory-optimized, latch-free storage, eliminating the PAGELATCH contention that has historically been a tempdb bottleneck in high-concurrency workloads.
For clarity, it does not memory-optimize user-created temporary tables or table variables, only the underlying system metadata that SQL Server maintains for those objects.
This finding identifies instances where the feature is currently enabled. It is not necessarily a problem, but it is worth noting in case the team is unaware that the feature is active or unaware of the considerations that come with it, particularly around SQL Server version differences.
Problem: The feature provides significant benefit on workloads with heavy tempdb metadata activity, particularly those that create and drop large numbers of temporary tables. By moving metadata into latch-free in-memory structures, it eliminates a class of PAGELATCH waits tied to tempdb system-table metadata pages, though allocation-map related PAGELATCH waits (PFS/GAM/SGAM) may still remain.
The most important consideration is memory consumption. The feature uses the In-Memory OLTP (Hekaton) infrastructure and consumes memory from the server's memory pool, which can be substantial under heavy tempdb activity. Without proper memory planning, the in-memory tempdb metadata can grow to consume large portions of available memory, and certain workload patterns (long-running explicit transactions with DDL on temporary tables) can cause memory growth that does not release, eventually leading to out-of-memory errors and potential service crashes.
There are also functional limitations that vary by SQL Server version. In SQL Server 2019 specifically, columnstore indexes are not supported on temporary tables when the feature is enabled, and sp_estimate_data_compression_savings cannot estimate columnstore compression in tempdb. Transactions that access memory-optimized tables in user databases cannot also access tempdb metadata catalog views in the same transaction. SQL Server 2022 builds on the 2019 implementation with additional improvements including shared latches for GAM/SGAM pages and improved allocation logic.
Learn More...The tempdb log file is larger than the data files
Issue: Tempdb has data files (typically configured as multiple files for contention reasons) and a single transaction log file. Under normal operation, the data files hold the bulk of tempdb's storage usage, and the log file stays comparatively small because tempdb activity is short-lived and the log is truncated automatically as soon as transactions complete.
This finding identifies instances where the tempdb log file has grown to a size larger than the combined size of the tempdb data files. The size relationship between data and log files is normally a good indicator of overall tempdb health, with an oversized log signaling transactional activity is occurring or has occurred at an unexpected pace.
Problem: A log file larger than the data files can indicate that an open transaction has been active long enough or has performed enough work in tempdb to accumulate substantial log records that cannot be truncated until the transaction completes. The most common cause is a very large or long-running transaction that uses tempdb extensively, often through large temporary tables, table variables, or sort and hash operations.
Sustained log growth in tempdb beyond the size of the data files can indicate an application or query pattern that needs review. Transactions should generally complete quickly, and one that produces enough log activity in tempdb to outgrow the data files often points to a query or batch that is doing more work in tempdb than necessary, or that is open longer than it should be.
This condition is also worth noting because tempdb log growth often competes for storage with the data files. If both are on the same volume (which is typical), an oversized log reduces space available for data file growth and can contribute to tempdb running out of space during heavier activity. The condition is rarely critical on its own but is a useful early indicator of issues in transaction or query design.
Learn More...SQL Server has been online for over 180 days
Issue: SQL Server recreates the tempdb database every time the SQL Server service starts, which means the creation date of tempdb in sys.databases reflects the time of the most recent service restart. Comparing this date against the current date provides a reliable measure of how long the instance has been running continuously since its last restart.
This finding identifies instances where the tempdb creation date indicates the SQL Server service has been online for more than 180 days without a restart. The threshold is a useful indicator that the instance has likely been missing scheduled maintenance, including the application of cumulative updates and security patches.
Problem: 180 days is a long interval to go without applying any updates. SQL Server cumulative updates are typically released every two to three months, and security updates are released as needed when vulnerabilities are identified. An instance running for 180 days has almost certainly missed multiple updates, including any security fixes that have been issued during that window.
Missing security updates leaves the instance exposed to publicly-known vulnerabilities. Once Microsoft releases a security update, the underlying vulnerability is documented in the security bulletin, and exploit code often follows quickly. Unpatched instances become attractive targets for both opportunistic and targeted attacks, particularly for any vulnerabilities that affect features exposed on the network.
Beyond security, missing cumulative updates means the instance is not benefiting from the bug fixes, performance improvements, and feature enhancements that have been released. Some performance issues, query optimizer bugs, and corruption-related problems are addressed only in cumulative updates, and instances that are far behind on patching can experience problems that have already been fixed in the engine.
Learn More...Tempdb files with high usage
Issue: Tempdb data files have a fixed size at any given moment, with autogrowth available to extend that size when more space is needed. SQL Server tracks how much of each tempdb file is currently allocated, and the ratio of allocated space to file size produces a usage percentage that reflects how full each file is.
This finding identifies instances where one or more tempdb data files are currently using more than 50% of their allocated space, which is the default threshold used by sp_CheckTempdb. The condition is not necessarily a problem, particularly if the workload regularly uses tempdb at high levels. However, sustained high usage warrants review to confirm it is expected and to identify any opportunities to reduce tempdb pressure.
Problem: High tempdb usage can be a normal operating state for some workloads, particularly those that run large analytical queries, perform heavy sorting and hashing, or rely on snapshot isolation with active version store activity. In these environments, sustained usage above 50% may simply reflect the typical tempdb working set, and the threshold is a starting point for review rather than an automatic problem.
For other workloads, sustained high usage indicates queries that could benefit from refactoring. Common causes include queries that spill due to undersized memory grants, large temporary tables built without appropriate filtering, repeated creation of temporary objects in tight loops, and use of temporary tables where set-based operations would work better. Reducing tempdb pressure in these cases improves both tempdb sizing requirements and overall query performance.
High usage also increases the risk of tempdb running out of space. If tempdb is already 50% full under normal load, an unexpected workload spike (a single large query, a long-running transaction holding the version store, or an ad hoc operation that uses heavy temporary objects) can push tempdb to capacity. Even with autogrowth enabled, the growth event itself adds latency and can cause the drive to fill if the workload continues.
Learn More...Tempdb files on the C: drive
Issue: Tempdb is the SQL Server system database used for sorts, hash joins, temporary tables, table variables, version store activity, and many internal operations. Because tempdb is shared by every database and every session on the instance, its placement and storage performance affect the entire workload.
By default, when SQL Server is installed without customizing the storage configuration, tempdb files are placed under the SQL Server installation directory on the C drive. If the team does not move tempdb during initial setup or shortly after, the files remain on C indefinitely.
This finding identifies instances where one or more tempdb files (data or log) are located on the C drive even though additional drives are available on the server. The condition is likely the result of accepting the installation defaults without customization.
Problem: Placing tempdb on the C drive consumes space on the operating system volume, which is sized for Windows and SQL Server binaries rather than for high-volume database activity. Tempdb growth, particularly during heavy workloads or unexpected spikes, can fill the C drive and cause Windows itself to become unstable, paging to fail, and all other kinds of bad.
Tempdb on C also competes with the operating system for I/O on the same physical drive. Modern best practice is to place tempdb on dedicated, fast storage where the I/O patterns of tempdb (heavy random reads and writes, sustained throughput) do not interfere with operating system activities such as logging, paging, and binary access. The C drive is rarely sized or configured for this kind of sustained database I/O.
Performance is also affected by sharing storage with other workloads on C. The operating system, antivirus tools, monitoring agents, and any other processes running on the host all use the C drive, producing contention that elevates tempdb latency and reduces overall instance throughput. A dedicated tempdb drive avoids this contention entirely.
This condition often exists unnoticed because it does not produce immediate, obvious symptoms during light workloads. The instance functions normally, queries return results, and the issue surfaces only during heavier activity or when tempdb growth begins to consume meaningful space on C. By that point, moving tempdb requires planning and a service restart.
Learn More...Split backup chain
Issue: A backup chain is the sequence of full, differential, and transaction log backups that SQL Server uses to restore a database to a specific point in time. The chain depends on each backup having a known location and a clean relationship to the backups before and after it, so the restore process can locate the right files in the right order.
A split backup chain occurs when backup files for the same database are being written to multiple locations, with no single destination containing the complete sequence. This can result from multiple backup processes running in parallel (such as a maintenance plan and a third-party tool), ad hoc backups taken without the COPY_ONLY option, or scheduled backups that have been changed to a new location without retiring the old configuration.
This finding identifies databases where recent backup history shows backup files being written to multiple distinct locations.
Problem: A split backup chain complicates the restore process significantly. To restore to a specific point in time, the team must identify which backups exist, where each one is located, and how they relate to each other in the chain. Under time pressure during an outage, this discovery work adds delay and increases the chance of mistakes that further extend the recovery time.
The condition also creates risk that the chain itself is broken. An ad hoc full backup taken without COPY_ONLY resets the differential base and changes the relationship between subsequent differential and log backups. If the team is not aware that the backup occurred or where the file was written, subsequent restores may fail in ways that are hard to diagnose, or may succeed but with a different recovery point than expected.
Split chains often indicate that multiple teams or tools are managing backups for the same database without coordination. Application teams sometimes run their own backup scripts, third-party tools may be configured at the storage layer without DBA involvement, and ad hoc operations may be performed during deployments or troubleshooting without consideration of the impact on the chain.
The condition is also worth attention because it complicates compliance and disaster recovery review. Verification that recovery objectives can be met depends on a clear, well-understood backup chain, and a split chain makes it harder to confirm that the necessary backup files exist in the expected places at the expected intervals.
Learn More...Recovery Point Objective issues
Issue: A Recovery Point Objective (RPO) defines the maximum acceptable amount of data loss measured in time. It is implemented through a backup strategy that takes full, differential, and transaction log backups at appropriate intervals to ensure recovery to a recent point is possible. The recovery model of each database determines which backup types are supported: SIMPLE allows only full and differential backups, while FULL and BULK_LOGGED allow transaction log backups in addition.
This finding identifies databases that fall outside common RPO thresholds: databases with no full backups in the last week, or databases in FULL or BULK_LOGGED recovery model with no transaction log backups in the last hour. These thresholds are common starting points for typical production environments and indicate gaps in backup coverage that warrant immediate attention.
Problem: A database with no recent full or differential backup has no current recovery baseline. If the database is lost or corrupted, the only available recovery point is the last full backup, which can be days or weeks old, resulting in significant data loss. The longer the gap, the greater the loss, and the harder the recovery becomes.
For databases in FULL or BULK_LOGGED recovery model, the absence of recent transaction log backups breaks the recovery chain. Point-in-time recovery is not possible past the last log backup, and the transaction log file continues to grow because it cannot be cleared until log backups occur. This produces both a recovery gap and an operational problem (log file growth) that can eventually cause the log volume to fill and stop the database.
The condition often arises from configuration drift, broken backup jobs, or databases that were created and never added to the standard backup process. New databases are sometimes overlooked when backup jobs are configured by name rather than by detecting all user databases dynamically. Other times, backup jobs fail silently for extended periods, with the failures unnoticed because no one is monitoring backup health specifically.
Backup gaps also have compliance implications. Most regulatory frameworks require demonstrable recovery capabilities, and an audit that finds databases without recent backups produces findings regardless of whether an actual outage has occurred. Even without compliance pressure, the inability to meet basic recovery objectives is one of the more serious operational deficiencies a database environment can have.
Learn More...Databases are missing backups
Issue: There are one or more databases that have not had a full backup completed, or there are one or more databases using the Full or Bulk Logged recovery model that have not had a log file backup completed.
Problem: By default, SQL Server does not perform any backups of your databases. If a database is missing a full backup then it is likely unrecoverable.
Also, if your database is using the Full or Bulk Logged recovery model, you will need to perform log file backups at regular intervals. Failing to do so will result in the log file growing until there is no more available drive space.
Learn More...Failed backups
Issue: SQL Server records every backup attempt in the SQL Server error log, including both successful backups and any that fail with an error. Failed backup entries include the database name, the type of backup attempted, the destination, and the specific error that caused the failure, providing a useful diagnostic record for backup-related issues.
This finding identifies instances where the error log contains entries for one or more recent failed backups. The detection is based on scanning the error log for backup-related error messages, typically those generated by failed BACKUP DATABASE or BACKUP LOG commands.
A failed backup can result from many causes, including disk space exhaustion on the backup target, network connectivity problems to remote backup locations, permissions issues on the destination, corruption in the source database, conflicts with other backup processes, or specific configuration problems such as expired backup encryption certificates.
Problem: A failed backup is a gap in the recovery chain. Whether the failed backup is a full, differential, or transaction log backup, its absence affects what can be recovered and how far back recovery is possible. Repeated log backup failures are particularly serious in databases using the FULL recovery model, since they can cause the transaction log to grow continuously and prevent point-in-time recovery for the affected window.
Backup failures often go unnoticed when monitoring is not specifically configured to alert on them. The SQL Server Agent job that runs the backup may continue to run on schedule, and the next attempt may succeed, leaving the failure visible only in the error log and the job history. Reviews that focus on whether backups are running rather than whether they are succeeding can miss the gap entirely.
The condition is also a useful signal for broader infrastructure issues. Backup destinations are often shared across many databases or instances, and a failure on one backup can indicate a problem (full disk, failing storage, network outage) that affects others as well. Investigating a failed backup early can prevent a larger disruption.
Failed backup entries in the error log also produce noise during other troubleshooting. When a backup fails repeatedly, the error log fills with backup error messages that can obscure other issues, making it harder to find unrelated entries when investigating different problems.
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: