patternsqlMinor
Why isn't my new index/table being created on the filegroup I specify?
Viewed 0 times
whythenewcreatedfilegroupbeingindexisnspecifytable
Problem
Results from @@Version
Here is the database definition
Here is the code to create the table and to add a clustered index. In both cases I'm specifying filegroup PM_G0 but when I check its on filegroup DATA.
```
CREATE TABLE Scratch.dbo.STUPID_TEST (dummycol int) ON [PM_G0];
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON PM_G0;
-- Tried the filegroup name with
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Sep 21 2011 22:45:45
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)Here is the database definition
CREATE DATABASE [Scratch] ON PRIMARY
( NAME = N'Scratch_mdf', FILENAME = N'N:\MSSQL\TEST\Primary\Scratch_mdf.mdf' , SIZE = 17664KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [DATA] DEFAULT
( NAME = N'Scratch_dat1a', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1a.ndf' , SIZE = 14539584KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_dat1b', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1b.ndf' , SIZE = 12016128KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [INDEX]
( NAME = N'Scratch_idx1', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx1.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx2', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx2.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx3', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx3.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Scratch_idx4', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx4.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [PM_G0]
( NAME = N'PM_data0', FILENAME = N'N:\MSSQL\PDB1\Data\PM_data0.ndf' , SIZE = 768000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'Scratch_log1', FILENAME = N'N:\MSSQL\TEST\Log\Scratch_log1.ldf' , SIZE = 833024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
ALTER DATABASE [Scratch] SET COMPATIBILITY_LEVEL = 100
GOHere is the code to create the table and to add a clustered index. In both cases I'm specifying filegroup PM_G0 but when I check its on filegroup DATA.
```
CREATE TABLE Scratch.dbo.STUPID_TEST (dummycol int) ON [PM_G0];
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON PM_G0;
-- Tried the filegroup name with
Solution
First thanks to everyone who tried to help me with this here and on twitter. We did however finally find the answer. It turns out that someone created a database trigger on model. The trigger parses every create index/table command and changes it to a DATA filegroup if it's a heap or clustered index and an INDEX filegroup for everything else. We were then able to track the trigger back to a policy.
Context
StackExchange Database Administrators Q#65505, answer score: 3
Revisions (0)
No revisions yet.