Script de SQL de grupos de archivos de base de datos de cuadro de mensajes de BizTalk Server
En este tema se proporciona un script SQL que se puede ejecutar en las instancias de SQL Server en un entorno de BizTalk Server para crear varios archivos y grupos de archivos para las bases de datos de Cuadro de mensajes de BizTalk.
Importante
Este script se proporciona "tal cual", está pensado solo para fines educativos o de demostración, y se usará en su propio riesgo. Microsoft no admite el uso de este script y Microsoft no garantiza la idoneidad de este script.
Importante
Se aplican las siguientes consideraciones al usar este script SQL para crear varios archivos y grupos de archivos para las bases de datos de Cuadro de mensajes de BizTalk:
Vuelva a ejecutar el script SQL de grupos de archivos de base de datos messageBox en las condiciones siguientes:
- Si instala un BizTalk Server revisión o Service Pack que ejecuta msgboxlogic.sql, tendrá que volver a ejecutar el script SQL de grupos de archivos de base de datos messageBox. Esto es necesario porque msgboxlogic.sql revierte los grupos de archivos y archivos messageBox a la configuración predeterminada, que es usar el grupo de archivos PRIMARY. Para determinar si una revisión o Service Pack ejecuta msgboxlogic.sql, consulte la sección Información del archivo del artículo de KB de revisión. O bien, compruebe el archivo setup.xml que se incluye con los archivos de Service Pack.
- Si agrega un nuevo host al grupo de BizTalk Server, tendrá que volver a ejecutar el script SQL de grupos de archivos de base de datos MessageBox. Esto es necesario porque el procedimiento almacenado que crea nuevos hosts configura las tablas para que los hosts usen el grupo de archivos PRIMARY de forma predeterminada.
- Aplicación del script SQL de grupos de archivos de base de datos messageBox en un entorno de varios cuadros de mensajes: Aunque no es un requisito, el script SQL de grupos de archivos de base de datos messageBox se puede ejecutar en cada cuadro de mensajes en un entorno de varios cuadros de mensajes.
Script SQL de grupos de archivos de base de datos de Cuadro de mensajes de BizTalk
El siguiente script SQL se puede usar para crear varios archivos y grupos de archivos, como se describe en el tema Optimización de grupos de archivos para Databases2.
/************************************************************
This script will create multiple filegroups / files for the
BizTalk MessageBox database. It will also set the initial
size, location, and the number of files per filegroup before
distributing objects among them.
************************************************************/
USE BizTalkMsgBoxDb
GO
SET NOCOUNT ON
GO
/************************************************************
Declare Variables
************************************************************/
DECLARE
@FILES_PER_FILEGROUP tinyint,
@INITIAL_FILE_SIZE nvarchar(50),
@GROWTH_INCREMENT nvarchar(50),
@MISC_DATA_FILE_PATH nvarchar(200),
@MISC_IDXS_FILE_PATH nvarchar(200),
@PREDICATE_DATA_FILE_PATH nvarchar(200),
@PREDICATE_IDXS_FILE_PATH nvarchar(200),
@MESSAGE_DATA_FILE_PATH nvarchar(200),
@MESSAGE_INSTANCE_FILE_PATH nvarchar(200),
@MESSAGE_IDXS_FILE_PATH nvarchar(200),
@SQLCmd nvarchar(2000),
@DBNAME sysname,
@OBJECT_ID int,
@UNDO bit,
@PRIMARY_FILE_GROUP sysname,
@FILE_GROUP sysname,
@FILE_GROUP_ID int,
@FILE_PATH nvarchar(200),
@DEFAULT_FILE_PATH nvarchar(200),
@FILE_NAME nvarchar(200),
@FULL_NAME nvarchar(200),
@FILE_COUNT tinyint,
@COUNTER int,
@DEFAULT_FILEGROUP nvarchar(50)
/************************************************************
Declare/Create Tables
************************************************************/
Declare @FILE_GROUP_TABLE table
(
name sysname,
file_count tinyint,
file_path nvarchar(1024)
)
Declare @TABLE_ALLOCATIONS table
(
table_name sysname,
main_file_group nvarchar(1024),
ncindex_file_group nvarchar(1024)
)
/************************************************************
Set User-defined Variables
************************************************************/
--Set to 0, this varibale has no effect,
--Set to 1 to undo changes and move all objects back to Primary filegroups, and delete all empty data files/filegroups
SET @UNDO = 0
--Set to the number of data files to be created per filegroup
SET @FILES_PER_FILEGROUP = 1
--Set to the initial file size per data file in MB - eg: '100MB'
SET @INITIAL_FILE_SIZE = N'50MB'
--Set to the Growth Increment per data file in MB - eg: '100MB'
SET @GROWTH_INCREMENT = N'50MB'
--The following 7 Variables should be set to the paths for each filegroup - eg: C:\Data\
SET @MISC_DATA_file_path ='C:\DATA\'
SET @MISC_IDXS_file_path ='C:\DATA\'
SET @PREDICATE_DATA_file_path ='C:\DATA\'
SET @PREDICATE_IDXS_file_path ='C:\DATA\'
SET @MESSAGE_DATA_file_path ='C:\DATA\'
SET @MESSAGE_INSTANCE_file_path ='C:\DATA\'
SET @MESSAGE_IDXS_file_path ='C:\DATA\'
--Set Database Name Variable
SET @DBNAME = DB_NAME()
--Set Default FileGroup Variable
SET @DEFAULT_FILEGROUP = 'MISC_DATA'
/************************************************************
If resetting the object distribution, set the Primary
filegroup as the default
************************************************************/
If @UNDO = 1
BEGIN
if not exists(SELECT TOP 1 groupname FROM sys.sysfilegroups WHERE (status & 0x10 > 0) and groupname = 'PRIMARY')
BEGIN
SET @DEFAULT_FILEGROUP = 'PRIMARY'
exec ('ALTER DATABASE ' + @DBNAME + ' MODIFY FILEGROUP [' + @DEFAULT_FILEGROUP + '] DEFAULT')
END
END
/************************************************************
Find path of data file in the default filegroup to use
until we set our user-defined paths - used when 'Undoing'
************************************************************/
SELECT TOP 1 @PRIMARY_FILE_GROUP = sfg.groupname, @FILE_NAME = sf.name, @FULL_NAME = sf.filename
FROM sys.sysfiles sf, sys.sysfilegroups sfg
WHERE (sfg.status & 0x10 > 0)
AND sf.groupid = sfg.groupid
--Find and set our default file path variable
SET @COUNTER = CHARINDEX(@FILE_NAME, @FULL_NAME)
SET @DEFAULT_FILE_PATH = SUBSTRING(@FULL_NAME, 1, @COUNTER - 1)
/************************************************************
Populate a temporary table with a list of filegroups
we want to create, their paths and the number
of data files to create per filegroup
************************************************************/
INSERT INTO @file_group_table (name, file_count, file_path) VALUES (N'MISC_DATA', @FILES_PER_FILEGROUP, @MISC_DATA_file_path)
INSERT INTO @file_group_table (name, file_count, file_path) VALUES (N'MISC_INDEXES', @FILES_PER_FILEGROUP, @MISC_IDXS_file_path)
INSERT INTO @file_group_table (name, file_count, file_path) VALUES (N'PREDICATE_DATA', @FILES_PER_FILEGROUP, @PREDICATE_DATA_file_path)
INSERT INTO @file_group_table (name, file_count, file_path) VALUES (N'PREDICATE_INDEXES', @FILES_PER_FILEGROUP, @PREDICATE_IDXS_file_path)
INSERT INTO @file_group_table (name, file_count, file_path) VALUES (N'MESSAGE_DATA', @FILES_PER_FILEGROUP, @MESSAGE_DATA_file_path)
INSERT INTO @file_group_table (name, file_count, file_path) VALUES (N'MESSAGE_INSTANCES', @FILES_PER_FILEGROUP, @MESSAGE_INSTANCE_file_path)
INSERT INTO @file_group_table (name, file_count, file_path) VALUES (N'MESSAGE_INDEXES', @FILES_PER_FILEGROUP, @MESSAGE_IDXS_file_path)
/************************************************************
Populate a temporary table with a list of
table and filegroup associations
************************************************************/
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'ActiveRefCountLog', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'AddRef', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'ApplicationProps', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Applications', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'BitwiseANDPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'BizTalkDBVersion', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'ConvoySetInstances', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'ConvoySets', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'EqualsPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'EqualsPredicates2ndPass', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'ExistsPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'FirstPassPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Fragments', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'GreaterThanOrEqualsPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'GreaterThanPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Instances', N'MESSAGE_INSTANCES', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'InstancesOperatedOn', N'MESSAGE_INSTANCES', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'InstancesPendingOperations', N'MESSAGE_INSTANCES', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'InstancesSuspended', N'MESSAGE_INSTANCES', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'LessThanOrEqualsPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'LessThanPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'LocalizedErrorStrings', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MarkLog', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MessageParts', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MessagePredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MessageProps', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MessageRefCountLog1', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MessageRefCountLog2', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MessageRefCountLogTotals', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'MessageZeroSum', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Modules', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'NotEqualsPredicates', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'OperationsProgress', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PartRefCountLog1', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PartRefCountLog2', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PartRefCountLogTotals', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Parts', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PartZeroSum', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PredicateGroup', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PredicateGroupNames', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PredicateGroupZeroSum1', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'PredicateGroupZeroSum2', N'PREDICATE_DATA', N'PREDICATE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'ProcessHeartbeats', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Release', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'ServiceClasses', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Services', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Spool', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'StaticStateInfo', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Subscription', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Tracking_Fragments1', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Tracking_Fragments2', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Tracking_Parts1', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Tracking_Parts2', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Tracking_Spool1', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'Tracking_Spool2', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_0_0', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_0_1', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_0_2', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_0_3', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_1_0', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_1_1', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_1_2', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingData_1_3', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingDataPartitions', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingMessageReferences', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrackingSpoolInfo', N'MESSAGE_DATA', N'MESSAGE_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TruncateRefCountLog', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'TrustedUsers', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES (N'UniqueSubscription', N'MISC_DATA', N'MISC_INDEXES')
/************************************************************
Find our BizTalk Host specific tables and add them into
our temporary table
************************************************************/
declare @nvcAppName nvarchar(256)
declare applications_cursor cursor FAST_FORWARD FOR
SELECT nvcApplicationName FROM Applications
OPEN applications_cursor
FETCH NEXT FROM applications_cursor INTO @nvcAppName
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES ('[' + @nvcAppName + N'Q]', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES ('[' + @nvcAppName + N'Q_Scheduled]', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES ('[' + @nvcAppName + N'Q_Suspended]', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES ('[InstanceStateMessageReferences_' + @nvcAppName + N']', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES ('[DynamicStateInfo_' + @nvcAppName + N']', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES ('[' + @nvcAppName + N'_MessageRefCountLog]', N'MISC_DATA', N'MISC_INDEXES')
INSERT INTO @table_allocations (table_name, main_file_group, ncindex_file_group) VALUES ('[' + @nvcAppName + N'_DequeueBatches]', N'MISC_DATA', N'MISC_INDEXES')
FETCH NEXT FROM applications_cursor INTO @nvcAppName
END
CLOSE applications_cursor
DEALLOCATE applications_cursor
/************************************************************
Loop through our temporary table of filegroups
and create them
************************************************************/
DECLARE file_group_cursor CURSOR FAST_FORWARD FOR
SELECT name, file_count, file_path FROM @file_group_table
OPEN file_group_cursor
FETCH NEXT FROM file_group_cursor INTO @file_group, @file_count, @file_path
WHILE ( (@@FETCH_STATUS = 0) AND (@UNDO = 0) )
BEGIN
SET @file_group_id = FILEGROUP_ID(@file_group)
/************************************************************
Create Filegroup if it does not already exist
************************************************************/
IF (@file_group_id IS NULL)
BEGIN
exec('ALTER DATABASE [' + @DBNAME + '] ADD FILEGROUP [' + @file_group + ']')
END
print 'Creating FileGroup: ' + @file_path + @file_group
/************************************************************
Loop through each new filegroup and create the data file
for the filegroup if one does not exist
************************************************************/
SET @file_group_id = FILEGROUP_ID(@file_group)
IF NOT EXISTS (SELECT TOP 1 groupid FROM sys.sysfiles WHERE groupid = @file_group_id)
BEGIN
set @COUNTER = 0
WHILE (@COUNTER < @file_count)
BEGIN
if @COUNTER = 0
BEGIN
SET @file_name = @DBNAME + '_' + @file_group
END
ELSE
SET @file_name = @DBNAME + '_' + @file_group + '_' + CAST(@COUNTER as nvarchar(3))
SET @full_name = @file_path + @file_name + '.ndf'
print 'Creating Data File: ' + @full_name + ' in ' + @file_group
--Build our TSQL string to add our data file to the database
SET @SQLCmd = N'ALTER DATABASE [' + @DBNAME + '] ADD FILE (
NAME = N''' + @file_name + ''',
FILENAME = N''' + @full_name + ''' ,
SIZE = ' + @INITIAL_FILE_SIZE + ',
MAXSIZE = UNLIMITED,
FILEGROWTH = '+ @GROWTH_INCREMENT +' )
TO FILEGROUP [' + @file_group + ']'
--Execute our TSQL string
EXECUTE (@SQLCmd)
/************************************************************
Set the requested Filegroup to be the default
************************************************************/
If @file_group = @DEFAULT_FILEGROUP
Begin
--Build our TSQL string to set requested FileGroup as default
SET @SQLCmd = N'ALTER DATABASE [' + @DBNAME + '] MODIFY FILEGROUP ' + @file_group + ' DEFAULT'
print 'Changing ' + @file_group + ' FileGroup to DEFAULT'
--Execute our TSQL string
EXECUTE (@SQLCmd)
END
set @COUNTER = @COUNTER + 1
END
END
FETCH NEXT FROM file_group_cursor INTO @file_group, @file_count, @file_path
END
CLOSE file_group_cursor
/************************************************************
Loop through our temporary table of table and filegroup
allocations and recreate clustered and non-clustered indexes
on the New Filegroups as well as move heaps to their new
filegroup.
************************************************************/
declare @table_name sysname,
@index_name sysname,
@column_name sysname,
@column_order int,
@index_id int,
@is_unique bit,
@data_space_id int,
@ignore_dup_key bit,
@is_primary_key bit,
@is_unique_constraint bit,
@fill_factor tinyint,
@is_padded bit,
@is_disabled bit,
@allow_row_locks bit,
@allow_page_locks bit,
@ncindex_file_group nvarchar(1024),
@ncindex_file_group_id int,
@create_index_sql nvarchar(4000)
DECLARE table_allocations_cursur CURSOR FAST_FORWARD FOR
SELECT table_name, main_file_group, ncindex_file_group FROM @table_allocations
OPEN table_allocations_cursur
FETCH NEXT FROM table_allocations_cursur INTO @table_name, @file_group, @ncindex_file_group
WHILE (@@FETCH_STATUS = 0)
BEGIN
/************************************************************
If reverting back to single filegroup, then overwrite
filegroups with 'PRIMARY'
************************************************************/
if (@UNDO = 1)
BEGIN
set @file_group = @primary_file_group
set @ncindex_file_group = @primary_file_group
END
else if (@ncindex_file_group is null)
BEGIN
SET @ncindex_file_group = @file_group
END
set @file_group_id = FILEGROUP_ID(@file_group)
set @ncindex_file_group_id = FILEGROUP_ID(@file_group)
select @object_id = OBJECT_ID(@table_name)
/************************************************************
Get data on the clustered and non clustered indexes
for the current table.
************************************************************/
IF (@object_id is not null)
BEGIN
DECLARE table_indexes_cursor CURSOR FAST_FORWARD FOR
SELECT name, index_id, is_unique, data_space_id, ignore_dup_key, is_primary_key, is_unique_constraint, fill_factor, is_padded, is_disabled, allow_row_locks, allow_page_locks
FROM sys.indexes WHERE object_id = @object_id AND (type = 0 OR type = 1 OR type = 2)
ORDER BY name, index_id ASC
OPEN table_indexes_cursor
FETCH NEXT FROM table_indexes_cursor INTO @index_name, @index_id, @is_unique, @data_space_id, @ignore_dup_key, @is_primary_key, @is_unique_constraint, @fill_factor, @is_padded, @is_disabled, @allow_row_locks, @allow_page_locks
WHILE (@@FETCH_STATUS = 0)
BEGIN
/************************************************************
Are we dealing with a HEAP? ie Index_id = 0
If so, we need to create a temporary clustered index on
new filegroup to move the table before deleting this
clustered index
************************************************************/
if @index_id = 0
BEGIN
/************************************************************
If dealing with a HEAP...
Create temp Clustered Index to move data
************************************************************/
SET @create_index_sql = 'CREATE CLUSTERED INDEX TEMP_CIX ON ' + @table_name + '('
select @column_name = name from sys.columns where object_id = @object_id and column_id = 1
SET @create_index_sql = @create_index_sql + @column_name + ') ON [' + @file_group + ']'
print 'Create TEMP Clustered index TEMP_CIX on table ' + @table_name + ' on FileGroup ' + @file_group
exec (@create_index_sql)
/************************************************************
Delete temp Clustered Index
************************************************************/
SET @create_index_sql = 'DROP INDEX TEMP_CIX ON ' + @table_name
print 'Drop TEMP Clustered index TEMP_CIX from table '+@table_name + 'on FileGroup ' + @file_group
exec (@create_index_sql)
END
/************************************************************
Deal with the clustered or non clustered indexes
************************************************************/
if ( ( (@index_id = 1) AND (@data_space_id != @file_group_id) ) OR
( (@index_id > 1) AND (@data_space_id != @ncindex_file_group_id) ) )
BEGIN
/************************************************************
Build string to recreate this index
************************************************************/
SET @create_index_sql = 'CREATE '
if (@is_unique = 1)
SET @create_index_sql = @create_index_sql + 'UNIQUE '
if (@index_id = 1)
SET @create_index_sql = @create_index_sql + 'CLUSTERED '
else
SET @create_index_sql = @create_index_sql + 'NONCLUSTERED '
SET @create_index_sql = @create_index_sql + 'INDEX ' + @index_name + ' ON ' + @table_name + '('
/************************************************************
Get details of columns for this table
************************************************************/
DECLARE index_columns_cursor CURSOR FAST_FORWARD FOR
SELECT ic.key_ordinal, c.name FROM sys.columns c
JOIN sys.index_columns ic ON ic.object_id = @object_id AND ic.index_id = @index_id AND c.column_id = ic.column_id
WHERE c.object_id = @object_id
ORDER BY ic.key_ordinal ASC
OPEN index_columns_cursor
FETCH NEXT FROM index_columns_cursor INTO @column_order, @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
if (@column_order > 1)
SET @create_index_sql = @create_index_sql + ', '
SET @create_index_sql = @create_index_sql + @column_name
FETCH NEXT FROM index_columns_cursor INTO @column_order, @column_name
END
CLOSE index_columns_cursor
DEALLOCATE index_columns_cursor
SET @create_index_sql = @create_index_sql + ') WITH ('
if (@ignore_dup_key = 1)
SET @create_index_sql = @create_index_sql + 'IGNORE_DUP_KEY = ON, '
if (@allow_page_locks = 0)
SET @create_index_sql = @create_index_sql + 'ALLOW_PAGE_LOCKS = OFF, '
SET @create_index_sql = @create_index_sql + 'DROP_EXISTING = ON) '
if (@index_id = 1)
SET @create_index_sql = @create_index_sql + 'ON [' + @file_group + ']'
else
SET @create_index_sql = @create_index_sql + 'ON [' + @ncindex_file_group + ']'
print @create_index_sql
--Execute SQL string to recreate the index
exec (@create_index_sql)
END
FETCH NEXT FROM table_indexes_cursor INTO @index_name, @index_id, @is_unique, @data_space_id, @ignore_dup_key, @is_primary_key, @is_unique_constraint, @fill_factor, @is_padded, @is_disabled, @allow_row_locks, @allow_page_locks
END
CLOSE table_indexes_cursor
DEALLOCATE table_indexes_cursor
END
FETCH NEXT FROM table_allocations_cursur INTO @table_name, @file_group, @ncindex_file_group
END
/************************************************************
If we are setting everything back to a single filegroup
we have to remove previously created files now that they
are empty before we delete the empty filegroups
************************************************************/
if (@UNDO = 1)
BEGIN
OPEN file_group_cursor
FETCH NEXT FROM file_group_cursor INTO @file_group, @file_count, @file_path
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @file_group_id = FILEGROUP_ID(@file_group)
IF (@file_group_id IS NOT NULL)
BEGIN
DECLARE files_in_group_cursor CURSOR FAST_FORWARD FOR
SELECT name FROM sys.sysfiles WHERE groupid = @file_group_id
OPEN files_in_group_cursor
FETCH NEXT FROM files_in_group_cursor INTO @file_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec('ALTER DATABASE [' + @DBNAME + '] REMOVE FILE [' + @file_name + ']')
FETCH NEXT FROM files_in_group_cursor INTO @file_name
END
CLOSE files_in_group_cursor
DEALLOCATE files_in_group_cursor
exec('ALTER DATABASE [' + @DBNAME + '] REMOVE FILEGROUP [' + @file_group + ']')
END
FETCH NEXT FROM file_group_cursor INTO @file_group, @file_count, @file_path
END
/************************************************************
Truncate T-Log. WITH TRUNCATE_ONLY not available from SQL Server
2008 therefore have to alter recovery model to simple,
issue a checkpoint, then change recovery model back to full
************************************************************/
declare @Currentdbname sysname
set @Currentdbname = db_name()
declare @cmd varchar(1000)
SET @cmd = 'ALTER DATABASE ' + @Currentdbname + ' SET RECOVERY SIMPLE'
EXEC(@cmd)
print 'Set Recovery Model to Simple'
Checkpoint
print 'Perform CheckPoint'
SET @cmd = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY FULL'
print 'Set Recovery Model back to Full'
EXEC(@cmd)
CLOSE file_group_cursor
END
DEALLOCATE file_group_cursor
GO
print '********Object to FileGroup Distribution Completed********'
/************************************************************
Verify that Distribution of tables among filegroups has
completed successfully
************************************************************/
SELECT sysobjects.[name] AS TableName, sysindexes.[name], sysfilegroups.groupname AS FileGroupName,
sysfiles.[filename] AS PhysicalFileName, sysfiles.[name] AS LogicalFileName
FROM sysobjects
JOIN sysindexes ON sysobjects.[id] = sysindexes.[id]
JOIN sysfilegroups ON sysindexes.groupid = sysfilegroups.groupid
JOIN sysfiles ON sysfilegroups.groupid = sysfiles.groupid
where sysobjects.[name] not like 'sys%' and sysobjects.xtype = 'U'
ORDER BY sysobjects.[name], sysindexes.[name]
GO
print '********Object to Filegroup Distribution Report Completed********'
GO
Consulte también
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de