HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Why isn't my new index/table being created on the filegroup I specify?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whythenewcreatedfilegroupbeingindexisnspecifytable

Problem

Results from @@Version

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
GO


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

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.