3

I have an Index optimization job set on the database SWHSJ having two data files with unrestricted growth in the J drive with 174GB free, as you can see below:

╔═══════════╦════════╦══════════════════════════╦═══════════╦══════════════╦═══════════════╦═══════════╦═════════════╗
║   name    ║ fileid ║         filename         ║ filegroup ║     size     ║    maxsize    ║  growth   ║   usage     ║
╠═══════════╬════════╬══════════════════════════╬═══════════╬══════════════╬═══════════════╬═══════════╬═════════════╣
║ SWHSJ     ║      1 ║ E:\sql_dat\SWHSJ.mdf     ║ PRIMARY   ║ 2490368 KB   ║ Unlimited     ║ 0 KB      ║ data only   ║
║ SWHSJ_log ║      2 ║ D:\sql_log\SWHSJ_log.LDF ║ NULL      ║ 266816 KB    ║ 2147483648 KB ║ 10240 KB  ║ log only    ║
║ SWHSJ_2   ║      3 ║ G:\sql_dat\SWHSJ_2.ndf   ║ PRIMARY   ║ 24117248 KB  ║ Unlimited     ║ 0 KB      ║ data only   ║
║ SWHSJ_3   ║      4 ║ H:\sql_dat\SWHSJ_3.ndf   ║ PRIMARY   ║ 59940864 KB  ║ Unlimited     ║ 0 KB      ║ data only   ║
║ SWHSJ_4   ║      5 ║ J:\sql_dat\SWHSJ_4.ndf   ║ PRIMARY   ║ 104857600 KB ║ Unlimited     ║ 262144 KB ║ data only   ║
║ SWHSJ_5   ║      6 ║ J:\sql_dat\SWHSJ_5.ndf   ║ PRIMARY   ║ 10485760 KB  ║ Unlimited     ║ 10%       ║ data only   ║
╚═══════════╩════════╩══════════════════════════╩═══════════╩══════════════╩═══════════════╩═══════════╩═════════════╝

Even though data files have enough space to grow, still my Index optimisation job keeps on failing with the below error:

Command: ALTER INDEX [IX_JLog_MessageType] ON [SWHSJ].[dbo].[JlLog] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 706445, Fragmentation: 5.42321 Msg 50000, Level 16, State 1, Server ServerName, Procedure CommandExecute, Line 152 Msg 1105, Could not allocate space for object 'dbo.JLog'.'IX_JLog_MessageType' in database 'SWHSJ' 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. Outcome: Failed

This job was working fine till 11th march, 2015. Please suggest.

  • 1
  • 3
    I dont think you are using intelligent script because PageCount: 706445, Fragmentation: 5.42321 Msg 50000, Level 16, State 1, Server does index with 5% fragmentation require a reorganize ?. Did you tried Ola Hallengren script – Shanky Mar 20 '15 at 12:59
  • When did you query the size of the files? after the transaction took place? You might be seeing how they look like after the rollback. Are you sure the drive does not become full or nearly full? "SWHSJ_5.ndf" grows in 10% increments, why don't you set to a fixed number? You may be seeing free space on the drive but the required increment might not be there. You need to monitor the transaction and how much space it is using. If it takes a long time, you probably want to run a job every 30 seconds that stores the drive's info in a table to really see what's going on. – Salvador L Mar 20 '15 at 14:42
  • Salvador, this would be better served as a comment as it doesn't provide a solution. Check out the Help Center on how to write good answers – LowlyDBA Mar 20 '15 at 14:44
  • @Salvador 1. No, I did not query the size after the transaction. 2. "SWHSJ_5.ndf" does not grow in 10% increment, it grows by 500MB unrestricted growth. 3. I have given "SWHSJ_4.ndf" 100GB initial size and it also grows by 500MB unrestricted growth. 4.Only this ALTER INDEX [IX_JLog_MessageType] fails for the table JlLog in the database SWHSJ, even the UPDATE STATISTICS for this index succeeds along with all the other alter indexes and update statistics in the job. Please suggest. – Anandita Sharma Mar 25 '15 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.