Quick Summary
A database in an Availability Group was being silently skipped by Ola Hallengren’s DatabaseBackup stored procedure with no error and no entry in the output file. We caught this in our daily health check review we do making sure clients are recoverable, safe, and running in a best configuration. After some digging, the culprit turned out to be a single trailing space in the database name – a character so easy to miss that it had gone completely unnoticed. We noticed it and did some more research and found the opportunity.
Context
The database in question was part of a SQL Server 2019 AG. It was online, in MULTI_USER mode, and the AG backup preference was correctly set to PRIMARY – all the things your typical DBA (including me!) would check when troubleshooting a missing backup in an AG. The backup job was running on the primary replica and using Ola Hallengren’s DatabaseBackup stored procedure, which we at Straight Path still consider the gold standard for native SQL Server backup automation. Everything looked healthy from the outside.
The Problem
We scheduled a backup job for the database in question, but the database was simply not being backed up by the job. There were no error messages, no warnings, and no entry in the output file explaining why it had been skipped. From the job’s perspective, everything had run just fine, but we were seeing the status of the database as violating our RPO target and we jumped in.
The Investigation
The first instinct with a skipped AG database is to look at the usual suspects: the synchronization health of the replica, the AG backup preference setting, and whether the @AvailabilityGroupReplicas parameter had been set in a way that excluded the primary. Those all checked out fine.
The lightbulb moment came from looking at the CommandLog table in our standard “DB_Administration” database. This is the logging table that DatabaseBackup writes to when you run it with @LogToTable = ‘Y’.
SELECT DatabaseName, CommandType, Command, StartTime, EndTime, ErrorNumber, ErrorMessage
FROM dbo.CommandLog
WHERE DatabaseName LIKE '%DatabaseName%'
ORDER BY StartTime DESC; The LIKE with a wildcard was key here. We looked at all recent command executions and noticed that other jobs configured to use the USER_DATABASES value for the @Databases parameter in Ola Hallengren’s maintenance solution were picking up the database – but with a trailing space included! That single extra character meant that when DatabaseBackup iterated through the databases and tried to match this database against its internal logic, the name with the trailing space did not match the actual database name as SQL Server resolved it, and the procedure skipped it without complaint, assuming that the database didn’t exist.
The Fix
The immediate fix here – for the backup issue – is to simply provide the database name, in brackets, with the trailing space included. The real fix is to work with the customer to remove the trailing space from the database name. This means taking stock of everything that references the database name, because the rename only fixes the system catalog – it does nothing for anything else that refers to the name by string. Because the database is in an Availability Group, this also means removing it from the AG, renaming it on every replica, and adding it back. Therefore, the fix is simple, but not necessarily easy.
Lesson/Takeaway
Trailing spaces in database names are invisible in most tools. SSMS, monitoring dashboards, and most query results will display the name in a way that looks completely normal. You will not notice it unless you specifically look for it, which means it can persist indefinitely in an environment that was otherwise set up correctly.
It is worth running a periodic audit of your database names for exactly this kind of whitespace issue, particularly in environments where databases are created and removed often and/or programmatically, where a trailing space in a variable or parameter can quietly make its way into the object name.
If you are using Ola Hallengren’s maintenance solution, I also highly recommend you ensure your scheduled maintenance jobs are using the @LogToTable = ‘Y’ parameter. It’s come in handy more times than I can count.
The Straight Path Team and Skills
This investigation combined our knowledge of Ola Hallengren’s maintenance solution, including the always-useful CommandLog table, SQL Server AGs, and the lateral thinking that comes with supporting 120+ SQL Server environments and the large variety of setups (and problems) we are exposed to because of it. Our free community tool, sp_checkBackup, can help you diagnose your own backup challenges.
This post is part of our Case of the Week series—real SQL Server issues and lessons from the field.
If you are inheriting SQL Server environments and want a team that knows how to dig into the details, reach out to us, and let’s talk about managing your SQL Server environment together. Our team is 20 people deep, and we have MVPs, speakers, bloggers, and authors. We know a lot because we’ve been exposed to a lot.