August 6, 2012 at 9:20 am
hello
i try to reindex on my database but it fails and the error message is
Could not allocate space for object 'dbo.SORT temporary run storage: 422362775552000' in database 'XXXXX' 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.
The Database initial file size is 94.6 GB and database set for auto-growth
File Growth is 500MB
and
Restricted file Growth is 95 GB.
so what i need to make some change to execute my DBCC Reindex statement
Thanks
August 6, 2012 at 9:40 am
What options are you using in your INDEX scripts?
If your initial file size is 94.6GB and you have the autogrowth increments set to 500MB...and you have the file size capped at 95BG how on earth is the DBCC command supposed to finish???
What is your log file set to? What's it's autogrowth set to?
If the index is trying to grow...it needs SPACE, and it's important to remember to space your LDF file to at least the same size of your largest index (otherwise you'll get a similar error)
Recommended changes: Remove the restricted file growth altogether, add significantly more space to the MDF file - change log file to do something similar
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 10:06 am
What is your log file set to? What's it's autogrowth set to?
Log file is also enable for Autogrowth
File growth 500MB and Restricted file growth 20Gb and Log file size is 14.5GB
If the index is trying to grow...it needs SPACE, and it's important to remember to space your LDF file to at least the same size of your largest index (otherwise you'll get a similar error)
can you please tell me how to check largest index?
i did sp_spaceused
and i get
database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
XXXXXX 109439.00 MB 0.00 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
114908784 KB 78169088 KB 3191976 KB 33547720 KB
August 6, 2012 at 10:49 am
can you please tell me how to check largest index?
You can do this with the standard reports.
Right-click your database, select Reports>Standard Reports>Disk Usage by Top Tables
You understand from MyDoggieJessie's response where the contention is, right?
August 6, 2012 at 11:12 am
Something like this (you need to run per DB)
SELECT
i.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
--WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY SUM(s.used_page_count) * 8 DESC
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 11:31 am
MyDoggieJessie (8/6/2012)
Something like this (you need to run per DB)
SELECTi.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
--WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY SUM(s.used_page_count) * 8 DESC
I run this script and i get this results
IndexNameIndexSizeKB
PK_tblUserActivityLog47338664
NULL22444848
PK_tblUserChangeLog2278616
IX_tblUserActivityLogARCHIVE860512
PK_tblAssessmentTestQuestionResultHistory815496
PK_tblStudentAttendance765680
PK_tblStudentMarksHistory483136
PK_tblStudentGrades444576
IX_StudentAttendanceSchoolYearCode368000
PK_tblStudentAbsencesHistory329880
PK_tblStudentMarksHistory_1311472
IX_tblUserActivityLog307320
PK_tblStudentAbsences259488
PK_tblAssessmentTitleIMathResults246056
IX_tblUserActivityLog_1233248
PK_tblIncidentActor223672
PK_tblStudentTruancy185248
PK_tblAssessmentTestQuestionResult174568
PK_tblSpecEdStateReportData143272
PK_tblSpecEdStateReportDataOld142648
PK_tblStudentHistory114512
PK_tblStudentScheduleHistory99112
PK_tblUserUsageARCHIVE89280
PK_tblAssessmentTestRawScoresHistory86960
pk1StudentSchoolHistory85880
PK_tblDistrictReview_179744
PK_tblStudentImmunization78048
so the first index PK_tblUserActivityLog is such a big in size
what i need to now for doing DBCC REINDEX
August 6, 2012 at 11:52 am
Maybe I'm missing something here but how does knowing which index is the largest help with the contention created by the contradictory autogrow settings?
August 6, 2012 at 11:53 am
Okay, so your larger index is about 45GB (math could be wrong on that but I think that's right)
Is the DB in simple or full mode?
Recommended changes: Remove the restricted file growth altogethwr or add significantly more space to the MDF file (several GB) - change log file size to at least 50-60GB.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 12:10 pm
Scott D. Jacobson (8/6/2012)
Maybe I'm missing something here but how does knowing which index is the largest help with the contention created by the contradictory autogrow settings?
It doesn't. But the OP is getting an error when attempting to reindex...because they are running out of space with the filegroup. So, they need to either allocate the space needed or allow te files to grow.
Obviously the best scenario would be to size the files appropriately from the get-go so they would not have to grow regularly (which would cause contention).
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 12:18 pm
MyDoggieJessie (8/6/2012)
Okay, so your larger index is about 45GB (math could be wrong on that but I think that's right)Is the DB in simple or full mode?
Recommended changes: Remove the restricted file growth altogethwr or add significantly more space to the MDF file (several GB) - change log file size to at least 50-60GB.
DB is in Full recovery Model
i check in that table, that table is basically contains records for all log entries for the database, right now it contains 25 million records. so i need to talk to my manager and need to delete some of record so we have some space to do re-index
what your recommendation for this?
August 6, 2012 at 12:21 pm
Thanks MyDoggieJessie. Was just starting to feel like our focus was too much on:
If the index is trying to grow...it needs SPACE, and it's important to remember to space your LDF file to at least the same size of your largest index (otherwise you'll get a similar error)
And not enough on:
If your initial file size is 94.6GB and you have the autogrowth increments set to 500MB...and you have the file size capped at 95BG how on earth is the DBCC command supposed to finish???
Wasn't sure if OP understood the latter.
August 6, 2012 at 1:04 pm
LOL, true...been a very busy day here at the office 🙁
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 1:23 pm
How often are your Transaction Log backups running?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 2:48 pm
MyDoggieJessie (8/6/2012)
How often are your Transaction Log backups running?
Thanks a lot for your help, greatly appreciate.
My Transcriptional log will running every 15 mins.
August 6, 2012 at 3:27 pm
i check in that table, that table is basically contains records for all log entries for the database, right now it contains 25 million records. so i need to talk to my manager and need to delete some of record so we have some space to do re-index
what your recommendation for this?
How much free space do you have available?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply