Exception:
Database 'Tfs_DefaultCollection' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
TFS Collection Databases :-
Database 'Tfs_DefaultCollection' cannot be started in this edition of SQL Server because part or all of object <tables> is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Explanation:
TFS will enable and use the following Enterprise Edition features.
Online index operations
Page compression
Table and index partitioning
Larger read - ahead buffering & Cube perspectives
Data compression and vardecimal storage format will cause problems when moving to different versions of SQL editions. Data compression and vardecimal storage format are enabled on the Tfs_Configuration and Tfs_<Collection> databases by default. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. Hence the databases backed up from Enterprise Editions cannot be restored on other Editions.
Solution 1:
The solution is to remove the objects which have compression on the TFS databases.
- Detach the Collection from TFS through TFS Administration console.
- Executed the following query on Tfs_<Collection> databases deployed on Enterprise Edition to identify the tables with Data compression and vardecimal storage format enabled.
SELECT SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] ,[rows] ,[data_compression_desc] ,[index_id] as [IndexID_on_Table]
FROM sys.partitions INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' ORDER BY SchemaName, ObjectName;
SELECT OBJECTPROPERTY(OBJECT_ID('<table name(s) from above command output>'),'TableHasVarDecimalStorageFormat'
Following are the Tfs_<Collection> database tables enabled with Data compression and vardecimal storage format.
CodeSense.tbl_AggregateMap
CodeSense.tbl_AggregatorInputQueue
dbo.LinkTreesLatest
dbo.tbl_AuthorizationObject
dbo.tbl_Branch
dbo.tbl_BranchMapping
dbo.tbl_LocalVersion
dbo.tbl_nodes
dbo.tbl_PendingChange
dbo.tbl_PendingChangeRecursive
dbo.tbl_PendingMerge
dbo.tbl_PendingRollback
dbo.tbl_PropertyValue
dbo.tbl_RegistryItems
dbo.tbl_SecurityAccessControlEntry
dbo.tbl_Version
dbo.tbl_WorkingFolder
dbo.tbl_WorkingFolderHistory
dbo.tbl_WorkspaceMapping
- Execute the following SQL command to disable the compression on the Tfs_<Collection> database table listed above one by one
ALTER INDEX ALL ON '<table name(s) from above command output>' REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON CodeSense.tbl_AggregateMap REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON CodeSense.tbl_AggregatorInputQueue REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.LinkTreesLatest REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_AuthorizationObject REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_Branch REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_BranchMapping REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_LocalVersion REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_nodes REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_PendingChange REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_PendingChangeRecursive REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_PendingMerge REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_PendingRollback REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_PropertyValue REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_RegistryItems REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_SecurityAccessControlEntry REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_Version REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_WorkingFolder REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_WorkingFolderHistory REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON dbo.tbl_WorkspaceMapping REBUILD WITH (DATA_COMPRESSION = None);
- Backup the Tfs_<Collection> database on SQL Server Enterprise Edition and restored it to SQL Server other Editions (both Express and Standard Editions)
- Attach the Tfs_<Collection> to the TFS instance deployed on SQL Server other Editions (Express and Standard Editions)
Solution 2:
- Detach the Collection from TFS through TFS Administration console.
- Executed the following stored procedure on Tfs_<Collection> databases deployed on Enterprise Edition to disable the compression on the relevant tables.
EXEC [dbo].[prc_EnablePrefixCompression] @online = 1, @disable = 1
- Backup the Tfs_<Collection> database on SQL Server Enterprise Edition and restored it to SQL Server other Editions (both Express and Standard Editions)
- Attach the Tfs_<Collection> to the TFS instance deployed on SQL Server other Editions (Express and Standard Editions)
Subscribe