August 2, 2013 at 1:26 pm
Hi everyone..
I am getting following error message while adding Clustered Index to my table..
CREATE CLUSTERED INDEX [xIK_Style_ROLLUP_MONTHLYTIMEKEY] ON [dbo].[Style_ROLLUP]
(
[MONTHLYTIMEKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Error:
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 3
Could not allocate space for object 'dbo.SORT temporary run storage: 482346876993536' in database 'RPDB' 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.
Database RPDB already has auto growth by 10% for max Unlimited size on File property.
Disc has 80% space available as well., What should I do??? I am not getting any error while creating Non Clustered index!!!
August 2, 2013 at 1:52 pm
Bajrang (8/2/2013)
Hi everyone..I am getting following error message while adding Clustered Index to my table..
CREATE CLUSTERED INDEX [xIK_Style_ROLLUP_MONTHLYTIMEKEY] ON [dbo].[Style_ROLLUP]
(
[MONTHLYTIMEKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Error:
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 3
Could not allocate space for object 'dbo.SORT temporary run storage: 482346876993536' in database 'RPDB' 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.
Database RPDB already has auto growth by 10% for max Unlimited size on File property.
Disc has 80% space available as well., What should I do??? I am not getting any error while creating Non Clustered index!!!
You're sure the data file is on the drive with 80% space available? Usually this issue is due to what you already checked for. Another potential cause (very rare one) is that the drive is heavily fragmented.
Oh, what version of SQL Server are you using? If you're using express, you could be coming up on the max data size.
August 2, 2013 at 2:06 pm
In fact.. I just found that out.. Yes, DB Limit had limitation of 4GB in SQL 2005 Express. and I was hitting that limit..
Thank you for your reply..
August 2, 2013 at 2:15 pm
Try changing the option SORT_IN_TEMPDB to ON
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply