SQL Server 2008 SP2 installed, filestream failing
I ran the installer for SQL Server 2008 Service Pack 2. Somehow I missed the part that says "this completely screws you over". Regardless, we had a couple databases with filestreams.
I followed some links and got the server itself operational. However, these two databases won't load.
I turned off filestream, uninstalled SP2, installed SP2 again, and am now at the point that I'm trying to turn filestream back on.
To do this I go to SQL Server Configuration, and on the FILESTREAM tab of the properties for the service I turn on Filestream and the share.
The I go to SSMS and run
exe sp_configure 'filestream access level', 2
GO
RECONFIGURE
GO
The error I get back is:
Msg 5597, Level 16, State 1, Line 1
FILESTREAM feature could not be initialized. The Windows Administrator must enable FILESTREAM on the instance using Configuration Manager before enabling through sp_configure.
Now, as stated above, I have enabled it in the config manager. Does anyone have an idea?
I tried setting the server to run under LocalSystem (it was set to a defined use). The sql log has several Error 5591 state 5 and 5105 state 14 errors along with several "a file activation error occurred..."" messages.
At this point I'm out of ideas.
UPDATE:
One more error that keeps popping up is:
Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install.
<{81E6B8A0-60E9-4A9F-82E3-FD5326CC3663}>RsFxFt.Dll::RsFxMgmtInitialize failed: Error 0x80070002 (-2147024894)
<{C580416B-A13E-4ECD-B61B-AAFAE39E5E35}>Failed to initialize the CFsaShareFilter interface
<{1038F43D-3391-45F7-B1B3-BADF26459429}>Failed to initialize CFsaShareFilter: Error 0x80070002 (-2147024894)
Well, I decided to go all in.
First, I detached all of the databases using filestreams. Then I made sure all configuration for filestreams was set to being off.
Then I installed Cumulative Update #5 (released a couple weeks ago).
After that finished, I enabled Filestreams in the configuration manager and ran the sp_configure statement. It worked!
Finally, I reattached my databases and tested. Everything is working great.
So, for anyone that runs into this problem in the future the steps for SP2 probably out to be:
- backup and detach all of you filestream databases.
- turn off filestream access completely for sql server.
- apply SP2.
- Apply the latest CU (I did #5).
- turn filestreams back on.
- reattach your databases.
- (optional) upvote this connect issue: http://connect.microsoft.com/SQLServer/feedback/details/680602/list-of-bugs-introduced