Questions[filestream](server)

For a project, I'm using a SQL Server 2008 R2. One table has a filestream column.

I've made some load tests, and now the database has ~20GB used.

I've empty tables, except several(configuration tables). But my database was still using a lot of space. So I used the Task -> Shrink -> Database / Files But my database is still using something like 16GB.

I found that it's the filestream file is still using a lot of space.

The problem is that I need to backup this database to export it on the final production server, and event if I indicate to compress the backup I got a file more than 3.5Go. Not convenient to store and upload.

And I'm planning much bigger test, so I want to know how to shrink that empty space.

When I'm trying:

I get this exception:

The properties SIZE, MAXSIZE, or FILEGROWTH cannot be specified for the FILESTREAM data
file 'FileStreamFile'. (Microsoft SQL Server, Error: 5509)

So what should I do?

I found several topics with this error but they was about removing the filestream column.

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)

There is an application used in a production site that uses SQL Server 2008 as its DBMS.

The database schema uses Filestream Varbinary to save binary data on the filesystem instead of directly into the DB tables.

The point is that now and then it would be useful to copy the production database on development machines, mostly for doing troubleshooting. The database is too big for comfortably moving it around, but it would be ok if it could be moved leaving out the filestream varbinary fields.

In other words, I am trying to make an "imperfect" copy of a database: i.e., on the destination database, it is ok to have NULL values instead of the varbinary. Is this possible? I tried looking for the feature on the SQL Server Management studio and did a backup that excludes the filegroup containing the filestream varbinary, but I cannot restore: MSSMS complains that the restore cannot be done because the backup is incomplete (of course).

Is it possible to achieve what I am trying to do in some way?

I'm using a FILESTREAM enabled database that is hosted on SQL Server 2008 Standard Ed on a Windows 2003 server and am seeing some long waits of types PREEMPTIVE_OS_%. Specifically, PREEMPTIVE_OS_GETFILESIZE when executing GET_FILESTREAM_TRANSACTION_CONTEXT(). I'm wondering if anyone can shed some light on what the PREEMPTIVE_OS_% wait types for FILESTREAM operations are.

When trying to enable filestream to a SQL Server 2008 I've got this message:

There was an unknown error applying the FILESTREAM settings. Check the parameters are valid.

And now in the FILESTREAM tab of the properties of said server I read:

A previous FILESTREAM configuration attempt was incomplete. FILESTREAM may be in an inconsistent state until re-configured

Do you know what might have gone wrong? What parameters is it taking about? Do I need to reset anything to be able to try again?

I've tried with filestream_enable.vbs, but that failed too:

c:\Users\pupeno\Downloads\EnableFilestream>cscript filestream_enable.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

Machine: .
Instance: MSSQLSERVER
Level: 3
ShareName:

Current Filestream configuration:

InstanceName = MSSQLSERVER
AccessLevel  = 0
ShareName    = MSSQLSERVER
RsFxVersion  = 0103

Calling method EnableFilestream with new level

Method failed: hr = -2147024894


New Filestream configuration:

InstanceName = MSSQLSERVER
AccessLevel  = 0
ShareName    = MSSQLSERVER
RsFxVersion  = 0103