I was recently investigating an issue that stemmed from a database being in the recovery_pending state. A quick investigation through the SQL logs showed that the server had restarted earlier that morning, and that this instance is running on a cluster. Assuming that this is probably just a timing issue, I decided to try to bring the database online.
|
1 |
ALTER DATABASE <database_name> SET ONLINE; |
Now, you didn’t really think that this blog post would end so soon, did you? Well, running that little bit of code gave me this wonderful result:
|
1 2 3 4 5 6 7 8 |
<span style="color: #ff0000;">Msg 5591, Level 16, State 5, Line 1 FILESTREAM feature is disabled. Msg 5105, Level 16, State 14, Line 1 A file activation error occurred. The physical file name 'F:\MSSQL11.MSSQLSERVER\MSSQL\Filestream\<database_name>' may be incorrect. Diagnose and correct additional errors, and retry the operation. Msg 5181, Level 16, State 5, Line 1 Could not restart database "<database_name>". Reverting to the previous status. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.</span> |
Sigh. Okay, that didn’t work out too well. Looking over the system a little bit more, I could see that the database had been online prior to the shutdown – backups had just happened 2 hours earlier.
I proceeded to check out all of the normal filestream things – things like making sure that the permissions on the specified folder had been granted to the SQL Server service account. But still, no luck – everything that I try continues to return the same error.
A friend reminded me that filestream needs to be enabled on the service. I’m thinking that surely, this isn’t it… the database had been up. Regardless, I launched SQL Server Configuration Manager, went to the properties of the SQL Server service, and clicked on the FILESTREAM tab. And what did I find? My assumption was wrong, for on this node, the “Enable FILESTREAM for Transact-SQL Access” setting was disabled. I quickly checked the other node (it was enabled there), and assumed that this must be the problem. I enabled this setting on the current node, and ran the alter statement to bring the database online again.
And the same error occurs. (DEEP Sigh!)
A little bit of internet searching brings me to a BOL article on how to Enable and Configure FILESTREAM (why do they always capitalize that anyway? It’s not like it’s an acronym, is it?) In this article, it mentions that we need to set the filestream access level. This is performed with the sp_configure stored procedure, so it means that this setting is stored in the master database. Since it was working on the other node, I assumed that the setting is set properly. But a quick check against sys.configurations shows that it isn’t set, so I run this bit of code:
|
1 2 |
EXECUTE sp_configure filestream_access_level, 2; RECONFIGURE; |
and then I run the ALTER DATABASE statement to put the database online. And… success. (Whew)
So did I really write this blog post just to impress you with my troubleshooting skills? Nope, not at all. The purpose of this post is to remind you not to make assumptions. Don’t assume that just because something was working, that it must be configured properly. And don’t assume that because everything with SQL is working properly on one node of a cluster, that it will work properly on the other node(s). I have seen too many times where nodes are not configured identically (in one case, this extended to the drive mappings, so when the cluster did fail over, it started causing all kinds of errors).
So, don’t assume. Step back, and investigate to ensure that the prerequisites for utilizing a feature are met, and that the settings are properly configured. Just look at all of the faulty assumptions that I made trying to get this situation resolved.
And when you’re working on a cluster, ensure that the nodes are configured identically.
