Mit der Data Reviewer-Lizenz verfügbar.
Ensure that the workspace was created under the correct FileGroups for proper data storage and the reduction of disk contention. Using the DBTUNE configuration that you modified previously, you can run the following script examples in SQL Server Management Studio.
Legt die aktuelle Datenbank fest.
USE REVDB
GO
Listet alle FileGroups und Datendateien in einer bestimmten Datenbank auf.
EXEC sp_helpdb revdb
GO
Listet die Datendateien für eine bestimmte FileGroup auf. In diesem Beispiel werden die Datendateien der PRIMARY-FileGroup aufgelistet.
EXEC sp_helpfilegroup 'PRIMARY'
GO
Listet Tabellen nach FileGroup auf.
SELECT USER_NAME(o.uid) [Owner],
OBJECT_NAME(i.id) [Table Name],
FILEGROUP_NAME(groupid) AS [Filegroup Name]
FROM sysindexes i inner join sysobjects o
ON i.id = o.id
WHERE i.indid IN (0, 1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0 AND
USER_NAME(o.uid) = 'rev'
ORDER BY 1,3,2
GO
Listet Indizes nach Tabelle und FileGroup auf.
select 'owner'=user_name(o.uid)
,'table_name'=object_name(i.id),i.indid
,'index_name'=i.name ,i.groupid
,'filegroup'=f.name ,'file_name'=d.physical_name
,'dataspace'=s.name from sys.sysindexes i
,sys.sysobjects o,sys.filegroups f
,sys.database_files d, sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and i.id = o.id
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
and user_name(o.uid) = 'rev'
order by object_name(i.id),i.name,f.name
GO
If any tables or indexes are stored in the wrong FileGroup, ALTER TABLE and ALTER INDEX can be used to change the FileGroup (see SQL Server Books online).
In SQL Server Management Studio, you can also re-create the DDL script of tables and indexes; then, within create script, you can modify the FileGroup parameter and re-create the tables and indexes in the correct FileGroups. This is particularly useful when tables are empty and you are allowed to re-create database objects.