Creating data files for the Workflow Manager workspace in SQL Server

Complexity: Beginner Data Requirement: ArcGIS Tutorial Data for Desktop

Standard GIS storage recommendations favor keeping index and log files separate from vector and tabular business tables. For performance reasons, it is better to position the business, feature, and spatial index tables separately and position FileGroup data files based on their usage pattern. For a multiversioned, highly active editing geodatabase, database files of the VERSIONS FileGroup may be separated and dispersed across available disks to avoid input/output contention.

The following table has the recommended FileGroups to be created for storing the Workflow Manager features and table.

FILEGROUP	ArcSDE Parameter
WMX_BDATA	Business table
WMX_BINDEX	Business table index
WMX_FDATA	Feature table
WMX_FINDEX	Feature table index
WMX_SDATA	Spatial index table
WMX_SINDEX	Spatial index table index
WMX_ADATA	Adds table (versioned)
WMX_AINDEX	Adds table index
WMX_DDATA	Deletes table (versioned)
WMX_DINDEX	Deletes table index

SQL Server parameters

The following script can be used to add FileGroups through SQL Server Management Studio.

USE MASTER
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_BDATA] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Bdata01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Bdata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_BDATA]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_BINDEX]
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Bindex01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Bindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_BINDEX]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_FDATA] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Fdata01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Fdata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_FDATA]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_FINDEX] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Findex01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Findex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_FINDEX]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_SDATA] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Sdata01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Sdata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_SDATA]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_SINDEX] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Sindex01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Sindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_SINDEX]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_ADATA] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Adata01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Adata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_ADATA]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_AINDEX] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Aindex01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Aindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_AINDEX]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_DDATA] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Ddata01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Ddata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_DDATA]
GO
ALTER DATABASE [WMXDB] ADD FILEGROUP [WMX_DINDEX] 
GO
ALTER DATABASE [WMXDB] ADD FILE(NAME = N'wmx_Dindex01', FILENAME = N'C:\mssql\data\wmxdb\wmx_Dindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [WMX_DINDEX]
GO

By setting the data files' initial size to 1 MB, there is no delay in the creation of the FileGroups. You can then resize the data files to avoid fragmentation.

ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Bdata01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Bindex01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Fdata01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Findex01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Sdata01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Sindex01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Adata01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Aindex01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Ddata01’, SIZE = 400MB )
ALTER DATABASE [WMXDB] MODIFY FILE ( NAME = N’wmx_Dindex01’, SIZE = 400MB )

Use the following to verify FileGroups and data files:

EXEC sp_helpdb wmxdb
GO

Copying and pasting the example may cause syntax errors.