x

Primary filegroup is full

I am trying to create a primary key (clustered across 6 columns on a 5 million row table)

When I execute my Create Index statement:

ALTER TABLE dbo.Fact_Prescriptions ADD CONSTRAINT
PK_Fact_Prescriptions PRIMARY KEY CLUSTERED 
(
    VarcharCol_1,
    VarcharCol_2,
    VarcharCol_3,
    VarcharCol_4,
    VarcharCol_5,
    VarcharCol_5,
    DateTimeCol_1,
    DateTimeCol_2
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I get the following error

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage:  
422220863176704' in database 'XXXXXXXXX' because the 'PRIMARY' filegroup 
is full. Create disk space by deleting unneeded files, dropping objects 
in the filegroup, adding additional files to the filegroup, or setting 
autogrowth on for existing files in the filegroup.

How do I correct this?

more ▼

asked Nov 03, 2009 at 03:47 PM in Default

Raj More gravatar image

Raj More
1.7k 80 82 84

question is not understandable to give solution . please explain more :)
Nov 03, 2009 at 03:51 PM anishmarokey
Please mark Kevs answer as accepted, to give him the reputation points and to make it clear to other viewers that your question have a solution. :)
Nov 04, 2009 at 05:20 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Well I think you understand the error - there isn't any room left in the filegroup to create the index. Can you confirm that either the database isn't set to autogrow, or the disk that the filegroup is on is full?

Is the database name you have XXXd out tempDB or a user db?

Are you specifying the SORT_IN_TEMPDB option on the CREATE INDEX?

more ▼

answered Nov 03, 2009 at 04:18 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

The Primary file group was set to a autogrow, and set to a maxsize and it had hit that ceiling. My DBA kindly gave me 10GB and I was off to the races.
Nov 03, 2009 at 05:28 PM Raj More
It is a warehouse db, not TempDB. I just posted my SQL Statement in the question.
Nov 03, 2009 at 05:31 PM Raj More
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x34

asked: Nov 03, 2009 at 03:47 PM

Seen: 4683 times

Last Updated: Nov 03, 2009 at 05:27 PM