SQL Server Performance

_DBREINDEX and MDF file size

Discussion in 'Performance Tuning for DBAs' started by ms_crb, Oct 19, 2005.

  1. ms_crb New Member

    I am running a _dbreindex database job in production that keeps failing with the following error. Could not allocate space for object in database '*' because the 'PRIMARY' filegroup is full..

    size of drive 49.9G, (mdf file is the only thing on the drive)

    size of datafile prior to running job 26.7 G,

    I checked the size after the job failed and it was 41 G.

    I have tried shrinking the database first to reduce the file size, and then reindexing but nothing seems to work, I would like to use adding additional space as a last resort. Is there a workaround or patch?

    DBCC SHRINKDATABASE
    ( databasename, 10)
    go

    exec _dbreindex databasename
    go

    Any suggestions?

  2. Luis Martin Moderator

    Do you have autogrow setting on?
    Also what kind of reindex you run?

    Take a look of one article written by Tom Pullen. Is a good way to reindex when neccesary.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

    Check if the reindexing is still working, then in that case it falls under open transaction and you will not be able to shrink the database. In your case choose the reindex for required indexes rather than going for all the indexes in the database.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. ms_crb New Member

    autogrow is checked, filegrowth in megabytes 1, maximum file size- restrict file growth to 42000 mb.

    ------------
    Originally posted by LuisMartin

    Do you have autogrow setting on?
    Also what kind of reindex you run?

    Take a look of one article written by Tom Pullen. Is a good way to reindex when neccesary.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  5. mmarovic Active Member

    Why do you shrink database? If db server needs more space for reindexing, shrinking will not help, because db will have to expand again. Actually, shrinking db causes internal fragmentation and that can make things even worst. Based on what criteria do you reindex? Indexes should be rebuilt only if they are fragmented. Do you have database log on the same drive?
  6. mmarovic Active Member

    The mdf file must be awfully fragmented if you started with smaller db size and expanded each time just 1 M.
  7. ms_crb New Member

    I am using the sp _dbreindex and reindexing the whole database. Since I kept running out of space I thought shrinking the database first might help but it didn't. The log file is on another drive. The only thing on the drive is the mdf file. I first tried using the optimization job in the database maintenance plan and it failed for weeks, I would run this 3 times a week. Then I tried the _dbreindex, it failed, next I tried shrink the database first and then running the _dbreindex and it also failed. We have slow performance, we haven't had a successful reindex in weeks.

    Catherine
  8. mmarovic Active Member

    You should reindex only indexes that are fragmented. I think you can add aditional 1 GB (or so) file in primary filegroup and then rebuilde fragmented indexes one at time. Have you read the article recommended by Luis?
  9. satya Moderator

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx a good start.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. ms_crb New Member

    I was able to reindex two of my larger tables using dbcc reindex tablename, this weekend I will run the dbcc reindex table in batches to test to see how many I can reindex without running out of space. Currently I am hard coding the tablenames in and running them in a job. What is the best way to get my table names for a specific database into a table, I have 700 tables for this database.

    Also, I am new to this site how do I do a search on the articles that were suggested to me?

    Thanks,

    Catherine
  11. Luis Martin Moderator

    The article I recommend is:

    http://www.sql-server-performance.com/tp_automatic_reindexing.asp

    The idea is to check fragmentation and defrag only those indexes witch are below some value, said 85%.

    You can schedule this store procedure to in windows time at least once a week.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  12. mmarovic Active Member

    Also in your case it is good idea to start with smaller tables and to rebuild indexes on largest at the end. That way you will have enough space for smaller and gradually release more and more space so at the end you may have enough space to rebuild indexes on largest one. If you need to rebuild clustered index on the table, first drop all non-clustered indexes, rebuild clustered one, then re-create indexes you have droped.
  13. Anil New Member

    Last week I too had same problem. I had added 500 Mb's more to mdf file and re-ran the job. Job executed sucessfully. But I don't think it is a wise idea.

    I think this is happening due to file resrtiction option.

    Regards,
    Anil Kumar
  14. satya Moderator

    It may not be wise idea but atleast gives the job to carryon the tasks for performance keepup.
    This way you can be able to monitor the database growth on your own, it is a best point to assess the indexes that requires regular defragmentation than doing all the indexes in the database, that could deliberately contribute this issue.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. dtipton New Member

    quote:Originally posted by mmarovic

    The mdf file must be awfully fragmented if you started with smaller db size and expanded each time just 1 M.


    AMEN!!!!!
  16. dtipton New Member

    Have you tried DBCC INDEXDEFRAG?

    It does not require additional space in the datafile.

    See DBCC SHOWCONTIG in BOL for a good example of how to do this.





  17. ms_crb New Member

    I put the DBCC DBREINDEX @tablename into a cursor and executed each statement separated, that worked great on the development server. I will be running it against our production database tonight. I have tried DBCC INDEXDEFRAG and that worked? Besides the dbreindex locking the tables what's the difference between the two?
  18. benwilson New Member

    if you have a read in BOL and the link Satya posted, you will see the differences! Here is one though:

    Updating statistics is a side effect of DBCC DBREINDEX, which is not the case with DBCC INDEXDEFRAG. You can increase the effectiveness of DBCC INDEXDEFRAG by running UPDATE STATISTICS afterwards.


    'I reject your reality and substitute my own' - Adam Savage
  19. satya Moderator

    Using UPDATE STATISTICS with INDEXEFRAG will fetch good performance, but by all means only DBREINDEX can fetch you correct results for performance. Using intermittent UPDATE STATS on volatile table will give good view though, I recommend.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page