Unable to delete RBS Blob data from File system even deleting from SharePoint 2010

Consider fallowing scenario that you have SQL Server 2008 R2  RBS enabled, and SharePoint Server 2010 RBS installed servers . You have some files that stored in SharePoint document library whichs streams are stored in RBS and even you deleted this files form SharePoint Document Library you noticed that the Blob data in file system still remaining.

Usually this is not a problem it is by design issue , because purpose of data recovery ,performance consideration, data integrity and safety the deleted files in real are not deleted immediately. So many systems are designed like this as SharePoint and also RBS included. In that kind of systems as a manner of being on the safe side they are just mark the files are deleted and than runs some background process later for deleting files according when some thresholds or limits are exceeded. If you what to find out this issue is a real problem you have to disable or make shut down this functionalities and after doing this still the blob files are remain on file system then you can say that you have a real problem.

On SharePoint side First thing you should check that the feature of Recycle Bin.

Recycle Bins are used to help users protect and recover data.Microsoft SharePoint Server 2010 supports two stages of Recycle Bins: the first-stage Recycle Bin and second-stage Recycle Bin.When a user deletes an item, the item is automatically sent to the first-stage Recycle Bin. By default, when an item is deleted from the first-stage Recycle Bin, the item is sent to the second-stage Recycle Bin. A site collection administrator can restore items from the second-stage Recycle Bin.You turn on and configure Recycle Bins at the Web application level. By default, Recycle Bins are turned on in all the site collections in a Web application. This article describes how to configure Recycle Bin settings for a Web application.”
http://technet.microsoft.com/en-us/library/cc263125(v=office.14).aspx

For more information and usage recommendations about SharePoint Server 2010 Recycle Bins, see Plan to protect content by using recycle bins and versioning (SharePoint Server 2010).

In that Point you have two option to bypass this feature that 1) you can totally disable Recycle Bin from Central Administrations site by CA-> Manage Web Application -> Select web Application which you decided to disable Recycle bin feature -> on Ribbon Menu Select General Settings and set “Recycle Bin” property as “Off”
2) or when you delete a file you can clear Site (First-stage) and Site Collection (Second-Stage) Recycle bins.

On SQL side in Content Database if you want to be sure and confirm deletion of the file you can use fallowing SQL .
1) Open SQL Server Management Studio
2) Select related Content Database and click “New Query”
3) Select * from AllDocs where ListID='<GUID>’
*** You can find List Guid on Browser Address bar when you open Library Settings page of a document libarary.
and check results for the file still is exists in that list .If you clear correct there should not be the related file is present on the results.

Even that you confirm that the file has been deleted from Content Database will still the Blob Data remains in File System where the blobs are stored. Becuase there is another mechanizm in SQL RBS side named “RBS Garbage Collector”

“SharePoint Server 2010 automatically marks unreferenced or deleted BLOB data for removal. SharePoint Server 2010 counts references to BLOBs by looking at the list of BLOB IDs stored by SharePoint Server 2010 in its content databases at the time of removal. Any BLOB references that are present in the RBS store tables but absent in the content database are assumed to be deleted by SharePoint Server 2010 and will be marked for removal. BLOBs that are not present in the content database and were created before the orphan cleanup time window, described later in this article, are also assumed to be deleted by SharePoint Server 2010 and will be marked for removal.

Because SharePoint Server 2010 tabulates BLOB references from the RBS columns of the content database, every RBS column must have a valid index before it can be registered in RBS.

The SQL Server RBS Maintainer tool removes the items marked by SharePoint Server 2010 for removal. You should schedule the clean-up tasks to be run during off-peak hours to reduce the effect on regular database operations.

RBS garbage collection is performed in the following three steps:

  • Reference scan.(RC)  The first step compares the contents of the RBS tables in the SharePoint Server 2010 content database with RBS’s own internal tables and determines which BLOBs are no longer referenced. Any unreferenced BLOBs are marked for deletion.
  • Delete propagation. (DP) The next step determines which BLOBs have been marked for deletion for a period of time longer than the garbage_collection_time_window value and deletes them from the BLOB store.
  • Orphan cleanup. (OC) The final step determines whether any BLOBs are present in the BLOB store but absent in the RBS tables. These orphaned BLOBs are then deleted”

http://technet.microsoft.com/en-us/library/ff943565(v=office.14).aspx

We have talked about ThreshHolds . In RBS configuration we have 3 important threshold for clearing BLOB data.

delete_scan_period :Specifies the minimum amount of time that must pass between two reference scan garbage collection runs. The default value is 30 days
orphan_scan_period: Specifies the minimum amount of time that must pass between two orphan cleanup garbage collection runs. The default value is 30 days
garbage_collection_time_window : Specifies the minimum time that must pass between identifying a blob as having no references in the database and deleting the blob from the store. This guarantees the availability of BLOBs for the specified time in case a backup is restored. The default value is 30 days.

So according to default values , your BLOB files should be cleared after 30 days , if they are not referenced to any Content Database record.
You can get more information about all configuration thresholds about RBS with following article:
http://msdn.microsoft.com/en-us/library/gg316763(v=sql.105).aspx

For testing immediate delete we can change these threashold .From SQL Server Management Studio:
1)Open SQL Server Management Studio
2)Select RBS enabled Content Database and click “New Query”
3) Execute following queries.
exec mssqlrbs.rbs_sp_set_config_value ‘garbage_collection_time_window’, ‘time 00:00:00’;
exec mssqlrbs.rbs_sp_set_config_value ‘delete_scan_period ‘, ‘time 00:00:00’;
exec mssqlrbs.rbs_sp_set_config_value ‘orphan_scan_period’, ‘time 00:00:00’;

Our job is not done yet:
The actual work of GC is done by the RBS Maintainer application. The maintainer is a console application that takes command line parameters such as the connection string to the database and the phases of GC to execute. This can be run from any machine that has access to the DB and the blob store(s). It can also be run from multiple machines simultaneously. You can schedule it using your favorite scheduler e.g. Windows Task Scheduler.

 Maintainer also takes an optional parameter to limit the amount of time it is run
http://blogs.msdn.com/b/sqlrbs/archive/2008/08/08/rbs-garbage-collection-settings-and-rationale.aspx

RBS requires you to define a connection string to each database that uses RBS before you run the RBS Maintainer. This string is stored in a configuration file in the <RBS installation path>\Microsoft SQL Remote Blob Storage 10.50\Maintainer folder that is ordinarily created during installation. The RBS Maintainer can be run manually by executing the Microsoft.Data.SqlRemoteBlobs.Maintainer.exe program together with the parameters that are listed in the following table.
When you run Maintainer from Command Prompt you can trace the operation logs in cmd window:

1) On Sql server open CMD prompt as Administrator and navigate to the path “C:\Program Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer”

2)Execute the command
Maintainer.exe -connectionstringname  RBSMaintainerConnection -operation GarbageCollection ConsistencyCheck ConsistencyCheckForStores -GarbageCollectionPhases rdo
-ConsistencyCheckMode r -TimeLimit 120

You can get more information about Maintainer.exe parameters
http://blogs.msdn.com/b/sqlrbs/archive/2010/03/19/running-rbs-maintainer.aspx
for  schedule an RBS Maintainer task please read following arcile:
http://technet.microsoft.com/en-us/library/ff943565(v=office.14).aspx

After you run RBS Maintainer , RS and DP phase completed the blob records will be cleared ! no not yet 🙂 . This operation is takes much 2 or 3 mintues and depends on how much data you have.

RBSGC
Image Source:http://blogs.technet.com/b/pramodbalusu/archive/2011/07/09/rbs-and-sharepoint-2010.aspx

 

FILESTREAM GC runs as part of the database checkpoint process. This is what causes some confusion – an old FILESTREAM file will not be removed until after it is no longer needed AND a checkpoint runs. 
http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-garbage-collection.aspx

In Simple recovery mode, you may run following command
CHECKPOINT;
In  Full recovery mode, two transaction log with CHECKPOINT are needed
or

“Forces the FILESTREAM garbage collector to run, deleting any unneeded FILESTREAM files. A FILESTREAM container cannot be removed until all the deleted files within it have been cleaned up by the garbage collector. The FILESTREAM garbage collector runs automatically. However, if you need to remove a container before the garbage collector has run, you can use sp_filestream_force_garbage_collection to run the garbage collector manually
http://msdn.microsoft.com/en-us/library/gg492195.aspx

USE <Content Database>;
GO
EXEC sp_filestream_force_garbage_collection @dbname =  N'<Content Database>’;

And finally if still your BLOB data is not cleared than you may create a Case for Microsoft Support 🙂

Advertisements

Sharepoint 2010 Misconception with RBS

After Sharepoint 2010 sp1 is released , some new features and improvements has been spreading in several blogs.And one of the most interested the new content database limits. In SP1 , by defining specific requirements for large data storage in SharePoint, Microsoft is able to increase the supported limits for data storage in SharePoint.
Sotware boundaries and limits and the primary changes are:

  1. For a SharePoint content database up to 200 GB there are no special requirements and this limit is included for consistency.
  2. For a SharePoint content database up to 4 TB you need to additionally plan for the following two requirements:
    • Requires disk sub-system performance of 0.25 IOPS per GB, 2 IOPS per GB is recommended for optimal performance.
    • Requires the customer to have plans for high availability, disaster recovery, future capacity, and performance testing.
    • And you need to review additional considerations in the TechNet Boundaries and Limits article.
  3. For a SharePoint content database over 4TB specifically for a Document Archive scenario you are required to additionally plan for the following:
    • SharePoint sites must be based on Document Center or Records Center site templates and must be an archive scenario where less than 5% of content is actively read from each month and less than 1% of content is actively written to.
    • Do not use alerts, workflows, link fix-ups, or item level security on any SharePoint objects in the content database. Note: document archive content databases can be the recipient of documents as a result of Content Routing workflow.
  4. Other specific limits changes being made at the same time:
    • A new limit of 60million items in any one SharePoint content database
    • The specific 5 TB limit per SQL Server instance has been removed.  Instead you should work with a SQL Server professional to plan for database storage.

 Also these new extended limitation is not our artile issue if you need more info see:
http://technet.microsoft.com/en-us/library/cc262787.aspx

And also RBS has some improvements:
The Microsoft SQL Server FILESTREAM RBS provider is now supported allowing for iSCSI connections to lower cost NAS storage. The SQL Server RBS provider is one option for RBS use with SharePoint and there are a number of ISV’s who also have RBS providers.

What about RBS misconception: Misconception is that RBS can provide extra database size limit ? Answer is “No”:

First of all Microsoft Says :
“We are clarifying that Remote Blob Storage (RBS) does not offer a way to increase the SharePoint content database size limits. The content database supported size limits apply to the sum of data stored in SQL Server plus data stored outside of SQL Server using an RBS provider”

Even if you use RBS or not , Sharepoint boundries is strict and you should not able to change it. And this is not only just Content DB size limit. it is also valid for all Sharepoint Limitation,  including Maximum document size limit of 2 GB for one file.

See you in next article…

 

Can not Enable RBS because of SQL server collation for Sharepoint 2010

Note: This is article contains possibility of one reason for this error.You can able to get this error other unrelated situations.)

When you try to enable after complete installation of RBS in sharepoint powershell console you getting belowed error:

Exception calling “Enable” with “0” argument(s): “Must declare the scalar variable “@RBsId”.”
At line:1 char:13

First you think like me should i missing a parameter ? Answer NO! You are not! this fuction is parameterless.But whats happing ? I reflect the dlls and find out SPContentDatabase.RemoteBlobSettings.Enable() function call stored procedure of “dbo.proc_EnableRbs” in selected content database.And this stored procedure using @RBsId as internal parameter.

Can not enable RBS Error

Can not enable RBS Error

After working one day and countless install/uninstall attempt finally we found the problem is SQL Server Collation set “TURKISH_CI_AS” .And sharepoint 2010 has limitation with SQL collation .

You get more information form here:
http://support.microsoft.com/kb/2008668

Changing the server collation to Latin1_General_CI_AS_KS_WS fixes the issue .

In this moment you have two option as hardway you can try to rebuild master tables with correct collation and alter all content databases or as easy way you can reinstall SQL Server  with corrent collation.

Error acces to registry key when installing RBS.msi

This article about encounter registry access denied error when installing RBS.msi as your account already added Local Administrator group in Windows Server 2008

Here is the complete error:
“Installing the FILESTREAM blob store failed with error message:
Access to the registry key
‘HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Remote Blob Storage’ is denied.
Setup will continue the installation”

Cause : UAC

Solution: Disable UAC

.

SharePoint 2010 Server with FILESTREAM RBS Provider

What is RBS ?

Remote Blob Storage is a library API set that is incorporated as an add-on feature pack for Microsoft SQL Server. It can be run on the local server running Microsoft SQL Server 2008 R2, SQL Server 2008,SQL server 2008 express or SQL Server 2008 R2 Express. To run RBS on a remote server, you must be running SQL Server 2008 R2 Enterprise edition. RBS is not supported for Microsoft SQL Server 2005.

What is the benefits of RBS ?

RBS can provide the following benefits:

  • BLOB data can be stored on less expensive storage devices that are configured to handle simple storage.
  • The administration of the BLOB storage is controlled by a system that is designed specifically to work with BLOB data.
  • Database server resources are freed for database operations.

When we consider to use RBS ?

  • The BLOB data files are larger than 256 kilobytes (KB).
  • The BLOB data files are at least 80 KB and the database server is a performance bottleneck. In this case, RBS reduces the both the I/O and processing load on the database server.

What is the difference of RBS with FILESTREAM and RBS without FILESTREAM feature ?

This implementation of the FILESTREAM provider is known as the local FILESTREAM provider. You can conserve resources by using the local RBS FILESTREAM provider to place the extracted BLOB data on a different (cheaper) local disk such as RAID 5 instead of RAID 10. You cannot use RBS with the local FILESTREAM provider on remote storage devices, such as network attached storage (NAS). The FILESTREAM provider is supported when it is used on local hard disk drives only.

A remote RBS FILESTREAM provider that is available in SQL Server 2008 R2 Express can store BLOB data on remote commodity storage such as direct-attached storage (DAS) or NAS. However, SharePoint Server 2010 does not currently support the remote RBS FILESTREAM provider.

BLOBs can be kept on commodity storage such as direct-attached storage (DAS) or network attached storage (NAS), as supported by the provider. The FILESTREAM provider is supported by SharePoint Server 2010 when it is used on local hard disk drives only. You cannot use RBS with FILESTREAM on remote storage devices, such as NAS.

The following table summarizes FILESTREAM benefits and limitations.

Operational requirement RBS with FILESTREAM RBS without FILESTREAM
SQL Server integrated backup and recovery of the BLOB Store Yes Yes
Scripted migration to BLOBs Yes Yes
Supports mirroring No No
Log shipping Yes Yes, with provider implementation
Database snapshots No1 No1
Geo replication Yes No
Encryption NTFS only No
Network Attached Storage (NAS) Not supported by SharePoint 2010 Products Yes, with provider implementation

1If the RBS provider that you are using does not support snapshots, you cannot use snapshots for content deployment or backup. For example, the SQL FILESTREAM provider does not support snapshots.

If FILESTREAM is not a practical provider for your environment, you can purchase a supported third-party provider. In this case, you should evaluate the following criteria when shopping for a provider:

  • Backup and restore capability
  • Tested disaster recovery
  • Deployment and data migration
  • Performance impact
  • Long-term administrative costs

What is the Prerequistes ?

If you plan to store BLOB data in an RBS store that differs from your SharePoint Server 2010 content databases, you must run SQL Server 2008 with SP1 and Cumulative Update 2. This is true for all RBS providers.

Important Notes

  • RBS does not enable any kind of direct access to any files that are stored in Microsoft SharePoint 2010 Products. All access must occur by using SharePoint 2010 Products only.
  • If you are storing many small (less than 256 KB) files that are frequently accessed by many users, you might experience increased latency on sites that have many small files that are stored in RBS. Increased latency is one cost factor that you should consider when you evaluate RBS for your storage solution. However, it is unlikely to be the strongest consideration. The amount of increased latency is also related to the RBS provider that you use.
  • RBS can be run on the local server running Microsoft SQL Server 2008 R2, SQL Server 2008 or SQL Server 2008 R2 Express. To run RBS on a remote server, you must be running SQL Server 2008 R2 Enterprise edition. SharePoint Server 2010 requires you to use the version of RBS that is included with the SQL Server Remote BLOB Store installation package from the Feature Pack for Microsoft SQL Server 2008 R2. Earlier versions of RBS will not work with SharePoint Server 2010. In addition, RBS is not supported in SQL Server 2005.
  • For best performance, simplified troubleshooting, and as a general best practice, we recommend that you create the BLOB store on a volume that does not contain the operating system, paging files, database data, log files, or the tempdb file
  • Microsoft SQL Server 2008 R2 Express supports databases up to 10 GB. If the installation includes content databases that are larger than 4 GB but smaller than 10 GB, you can upgrade to SQL Server 2008 R2 Express for your content database storage solution instead of implementing RBS. For more information, see Microsoft SQL Server 2008 R2 Express Edition
  • WARNING:We do not recommend that you install RBS by running the RBS_X64.msi file and launching the Install SQL Remote BLOB Storage wizard. The wizard configures the RBS Maintainer to run a scheduled task every 30 days. This setting might not be optimal for your environment. For more information about the RBS Maintainer, see the SQL Server Help documentation that is included with the SQL Server Remote BLOB Store installation package from the Feature Pack for Microsoft SQL Server 2008 R2. (ref : Install and configure Remote BLOB Storage (RBS) with the FILESTREAM provider (SharePoint Server 2010))UPDATE 18.04.2011: With new version RBS.msi you can use GUI interface to install rbs to your envoriment.
    You have to select “Show the optional advanced configuration options” when wizard in progress. And uncheck the maintaner  schedule configuration for disable maintainer.You can download from Microsoft SQL Server 2008 R2 Feature Pack Page
    http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52
    This is the only provider that Sharepoint Server 2010  with version 10.50.xxxx (R2) .But you can load this provider to SQL Server 2008 express loaded machine.


Tips and Tricks

  • You can allow only big files to be put into FILESTREAM. Since FILESTREAM performance is not as good as the databases when it deals with small files (for example, <1M), you can change this threshold.  The following Windows PowerShell command change the setting to 1M (1048576 bytes), file below 1M will be stored in DB.

$cbd = Get-SPContentDatabase “WSS_Content”
$cbd.RemoteBlobStorageSettings.MinimumBlobStorageSize=1048576
$cdb.Update()

It would be good to test the performance based on your own storage and hardware.

This Article base on below MSDN articles :

And Blog Articles

Other Resources

Happy Codding…
.