Creating data files for the 生产化制图 (Production Mapping) workspace in SQL Server (Production Mapping)

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 patterns. 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 生产化制图 (Production Mapping) features and table.

FILEGROUP

DATAFILE_NAME

ArcSDE_PARAMETER

PM_BDATA

PM_Bdata

Business table

PM_BINDEX

PM_Bindex

Business table index

PM_FDATA

PM_Fdata

ST_Geometry LOB storage

PM_FINDEX

PM_Findex

ST_Geometry LOB index

PM_SDATA

PM_Sdata

Spatial index tables

PM_SINDEX

PM_Sindex

Spatial index features

PM_ADATA

PM_Adata

Adds table (versioned)

PM_AINDEX

PM_Aindex

Adds table index

PM_DDATA

PM_Ddata

Delete table (versioned)

PM_DINDEX

PM_Dindex

Delete table index

PM_RASTER

PM_Raster

Raster datasets

PM_RINDEX

PM_Rindex

Raster indexes

PM_RBLK

PM_Rblk

Raster BLK table

PM_RBLKIDX

PM_Rblkidx

Raster BLK table indexes

PM_XMLDOC

PM_Xmldoc

XML documents

PM_XMLIDX

PM_Xmlidx

XML documents indexes

PM_TOPO_BDATA

PM_Topo_Bdata

Topology business table

PM_TOPO_BINDEX

PM_Topo_Bindex

Topology business table index

PM_TOPO_FDATA

PM_Topo_Fdata

Topology ST_Geometry Lob storage

PM_TOPO_FINDEX

PM_Topo_Findex

Topology ST_Geometry Lob index

PM_TOPO_SDATA

PM_Topo_SData

Topology spatial index tables

PM_TOPO_SINDEX

PM_Topo_Sindex

Topology spatial index features

PM_TERRAIN_BDATA

PM_Terrain_Bdata

Terrain business table

PM_TERRAIN_BINDEX

PM_Terrain_Bindex

Terrain business table index

PM_TERRAIN_FDATA

PM_Terrain_Fdata

Terrain ST_Geometry LOB storage

PM_TERRAIN_FINDEX

PM_Terrain_Findex

Terrain ST_Geometry LOB index

PM_TERRAIN_SDATA

PM_Terrain_Sdata

Terrain spatial index tables

PM_TERRAIN_SINDEX

PM_Terrain_Sindex

Terrain spatial index features

PM_NET_BDATA

PM_Net_Bdata

Network business table

PM_NET_BINDEX

PM_Net_Bindex

Network business table index

PM_NET_FDATA

PM_Net_Fdata

Network ST_Geometry LOB storage

PM_NET_FINDEX

PM_Net_Findex

Network ST_Geometry LOB index

PM_NET_SDATA

PM_Net_Sdata

Network spatial index tables

PM_NET_SINDEX

PM_Net_Sindex

Network spatial index features

FileGroups created in the 生产化制图 (Production Mapping) workspace

The following script can be run in SQL Management Studio to add FileGroups.

USE MASTER
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_BDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Bdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Bdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_BDATA]
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Bdata_02', FILENAME = N'D:\mssqldata\pmdb\pm_Bdata_02.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_BDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_BINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Bindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Bindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_BINDEX]
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Bindex_02', FILENAME = N'D:\mssqldata\pmdb\pm_Bindex_02.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_BINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_FDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Fdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Fdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_FDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_FINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Findex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Findex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_FINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_SDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Sdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Sdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_SDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_SINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Sindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Sindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_SINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_XML_DOC] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Xml_doc_01', FILENAME = N'D:\mssqldata\pmdb\pm_Xml_doc_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_XML_DOC]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_XML_INDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Xml_index_01', FILENAME = N'D:\mssqldata\pmdb\pm_Xml_index_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_XML_INDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_ADATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Adata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Adata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_ADATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_AINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Aindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Aindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_AINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_DDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Ddata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Ddata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_DDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_DINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Dindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Dindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_DINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_RASTER] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Raster_01', FILENAME = N'D:\mssqldata\pmdb\pm_Raster_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_RASTER]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_RASTER_INDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Raster_Index_01', FILENAME = N'D:\mssqldata\pmdb\pm_Raster_Index_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_RASTER_INDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TOPO_BDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Topo_Bdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Topo_Bdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TOPO_BDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TOPO_BINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Topo_Bindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Topo_Bindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TOPO_BINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TOPO_FDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Topo_Fdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Topo_Fdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TOPO_FDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TOPO_FINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Topo_Findex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Topo_Findex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TOPO_FINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TOPO_SDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Topo_Sdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Topo_Sdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TOPO_SDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TOPO_SINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Topo_Sindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Topo_Sindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TOPO_SINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TERRAIN_BDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Terrain_Bdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Terrain_Bdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TERRAIN_BDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TERRAIN_BINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Terrain_Bindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Terrain_Bindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TERRAIN_BINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TERRAIN_FDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Terrain_Fdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Terrain_Fdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TERRAIN_FDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TERRAIN_FINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Terrain_Findex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Terrain_Findex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TERRAIN_FINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TERRAIN_SDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Terrain_Sdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Terrain_Sdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TERRAIN_SDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_TERRAIN_SINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Terrain_Sindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Terrain_Sindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_TERRAIN_SINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_NET_BDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Net_Bdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Net_Bdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_NET_BDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_NET_BINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Net_Bindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Net_Bindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_NET_BINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_NET_FDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Net_Fdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Net_Fdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_NET_FDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_NET_FINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Net_Findex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Net_Findex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_NET_FINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_NET_SDATA] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Net_Sdata_01', FILENAME = N'D:\mssqldata\pmdb\pm_Net_Sdata_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_NET_SDATA]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_NET_SINDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_Net_Sindex_01', FILENAME = N'D:\mssqldata\pmdb\pm_Net_Sindex_01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [PM_NET_SINDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_SDE_DICT] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_sde_dict_01', FILENAME = N'D:\mssqldata\pmdb\pm_sde_dict_01.NDF' , SIZE = 1, MAXSIZE = 300, FILEGROWTH = 1) TO FILEGROUP [PM_SDE_DICT]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_SDE_DICT_INDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_sde_dict_index_01', FILENAME = N'D:\mssqldata\pmdb\pm_sde_dict_index_01.NDF' , SIZE = 1, MAXSIZE = 100, FILEGROWTH = 1) TO FILEGROUP [PM_SDE_DICT_INDEX]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_SDE_LOG] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_sde_log_01', FILENAME = N'D:\mssqldata\pmdb\pm_sde_log_01.NDF' , SIZE = 1, MAXSIZE = 300, FILEGROWTH = 1) TO FILEGROUP [PM_SDE_LOG]
GO
ALTER DATABASE [pmdb] ADD FILEGROUP [PM_SDE_LOG_INDEX] 
GO
ALTER DATABASE [pmdb] ADD FILE(NAME = N'pm_sde_log_index_01', FILENAME = N'D:\mssqldata\pmdb\pm_sde_log_index_01.NDF' , SIZE = 1, MAXSIZE = 100, FILEGROWTH = 1) TO FILEGROUP [PM_SDE_LOG_INDEX]
GO

通过将数据文件的初始大小设置为 1 MB,以避免在创建文件组时发生延迟。然后,可以重新调整数据文件的大小以避免产生碎片。

ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Bdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Bindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Fdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Findex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Sdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Sindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Adata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Aindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Ddata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Dindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Raster_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Rindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Rblk_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Rblkidx_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Xmldoc_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Xmlidx_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Topo_Bdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Topo_Bindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Topo_Fdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Topo_Findex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Topo_Sdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Topo_Sindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Terrain_Bdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Terrain_Bindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Terrain_Fdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Terrain_Findex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Terrain_Sdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Terrain_Sindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Net_Bdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Net_Bindex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Net_Fdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Net_Findex_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Net_Sdata_01, SIZE = 400MB )
ALTER DATABASE [PMDB] MODIFY FILE ( NAME = Npm_Net_Sindex_01, SIZE = 400MB )

Use the following to verify FileGroups and data files:

EXEC sp_helpdb pmdb
GO
4/27/2014