While working on my Swart’s Ten Percent Rule post last week, I needed to test the Windows version of the script on my SQL Server 2016 instance. Just before removing all the databases, I noticed something interesting when querying the
sys.databases Dynamic Management View (DMV).
Because the process I had come up with involved setting databases offline to allow me to work around space constraints, I noticed that all the databases I created for the test showed a
NULL value in the
As this was expected behaviour (the collation cannot be read if the database is offline), I dismissed the vast amount of
NULLs in this column. However, once I cleaned up the 32,700-odd extra databases, I discovered there were two databases I was using for another project where the
collation_name was also
When I right-clicked on those databases in SQL Server Management Studio’s Object Explorer to look at the properties, both showed the default collation of
I remembered that Andy Warren had asked a Question of the Day a long time ago on SQLServerCentral.com asking about the cases when the output of the following query would return
SELECT DATABASEPROPERTYEX('SomeDB', 'Collation');
If we follow that link, we see that the correct answer had three possible options:
- the database name is spelled incorrectly
- the database is online but recovery is not complete
- the database is offline
None of these answered this problem to my satisfaction, but there was a link in the answer to a MSDN article. With trepidation I clicked the link expecting it to fail, but the folks behind Microsoft Docs have done an amazing job keeping old links alive.
The official documentation for
DATABASEPROPERTYEX tells us that the Collation property returns the database collation as expected, but if the database “is not started,” it will return a
This was my clue. It wasn’t that the two databases were offline or in recovery, but that Auto Close was enabled. As soon as I figured it out I facepalmed, because these databases were restored from SQL Server Express Edition which enables Auto Close by default.
Guess what? The
sys.databases documentation confirms that, if I’d just started there in the first place.
Look, not every one of these blog posts is gold, but I thought it was a creative way to find the answer. It’s still not as bad as the time I literally forgot the
Share your recent facepalm moments in the comments below.