Post reply

Reindex failed primary file group not enough space

  • November 7, 2011 at 9:21 am

    #1404033

    But our data file size of the whole database is already 10 gb.

    quote: By table = 4GB, you mean the clustered index is 4GB in size?

    If I go to our biggest table, right click Property , then Storage,

    I can see it listed the size of the data is 4 gb, and index size is 0.6 gb.

    Thanks

  • November 7, 2011 at 9:36 am

    #1404047

    So when you rebuild you need AT LEAST another 4.8 GB of free space (unless doing it in tempdb). Since you only have 3.7GB left you have this error.

  • November 7, 2011 at 9:38 am

    #1404052

    Ninja's_RGR'us (10/31/2011)

    Actually there's an easy way out of this.

    Maint. Plans always reindex everything no matter what (needed or not). This is usually a big waste of time and ressources.

    I use this instead... which you can call in the maint. plan instead of the current code (you'll need to add a step to run sql statement instead of the current step to reindex. I'm no expert in ssis so I can't give clearer directions than that).

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

    Totally agree. I have been switching our maint plans to this same method - but using Agent job steps instead of the maint plan. I prefer the more intelligent method and the lower consumption of resources.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 7, 2011 at 9:39 am

    #1404054

    Ninja's_RGR'us (10/31/2011)

    sqlfriends (10/31/2011)

    Thanks all.

    First, I see the script is so long, I will have to study it a little bit later before I use it .

    Second, I checked in the maintenance plan we do have the sort in tempdb checked in the reindex task.

    How can I know how much space do all the indexes use in the database?

    Thanks

    That's close to what you need.

    http://jasonbrimhall.info/2010/05/19/tablespace-update/

    The script I posted creates the script to run. To call it you just call an sp with 2-3 parameters.

    Thanks Remi - this is an excellent recommendation;-)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 7, 2011 at 9:42 am

    #1404056

    SQLRNNR (11/7/2011)

    Ninja's_RGR'us (10/31/2011)

    sqlfriends (10/31/2011)

    Thanks all.

    First, I see the script is so long, I will have to study it a little bit later before I use it .

    Second, I checked in the maintenance plan we do have the sort in tempdb checked in the reindex task.

    How can I know how much space do all the indexes use in the database?

    Thanks

    That's close to what you need.

    http://jasonbrimhall.info/2010/05/19/tablespace-update/

    The script I posted creates the script to run. To call it you just call an sp with 2-3 parameters.

    Thanks Remi - this is an excellent recommendation;-)

    Only if the new version is out ;-). (http://www.sqlservercentral.com/Forums/Topic1201443-373-1.aspx)

  • November 7, 2011 at 9:46 am

    #1404058

    sqlfriends (11/7/2011)

    But our data file size of the whole database is already 10 gb.

    quote: By table = 4GB, you mean the clustered index is 4GB in size?

    If I go to our biggest table, right click Property , then Storage,

    I can see it listed the size of the data is 4 gb, and index size is 0.6 gb.

    Thanks

    Two things:

    Did you do this?

    GilaMonster (10/31/2011)

    Query sys.dm_db_index_physical_stats. Limited mode is good enough. See the page_count column

    This will also let you know the rough frag % and if defragging the biggest table is necessary every time the maint plan runs.

    Second,

    Use the scripts referenced by Remi. One is the table size script on my site. This will break down the Table Size and Index size for all tables. This is much faster than right clicking and checking properties for each object.

    The second script is the Index Defrag script by Michelle Ufford (sqlfool). The script is long because it creates a few objects and stored procedures. You can use this script - it is safe (Remi, Gail and I would have said otherwise if it wasn't good - but we understand you want to do due diligence).

    That script will determine if the index really needs to be reorged or defragged and then only do those that are necessary. What's more, is that you can have it run for a length of time and sleep easier knowing the job will not run too long.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 7, 2011 at 9:47 am

    #1404061

    Ninja's_RGR'us (11/7/2011)

    SQLRNNR (11/7/2011)

    Ninja's_RGR'us (10/31/2011)

    sqlfriends (10/31/2011)

    Thanks all.

    First, I see the script is so long, I will have to study it a little bit later before I use it .

    Second, I checked in the maintenance plan we do have the sort in tempdb checked in the reindex task.

    How can I know how much space do all the indexes use in the database?

    Thanks

    That's close to what you need.

    http://jasonbrimhall.info/2010/05/19/tablespace-update/

    The script I posted creates the script to run. To call it you just call an sp with 2-3 parameters.

    Thanks Remi - this is an excellent recommendation;-)

    Only if the new version is out ;-). (http://www.sqlservercentral.com/Forums/Topic1201443-373-1.aspx)

    It will be out shortly - time permitting 😀

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 7, 2011 at 9:49 am

    #1404065

    SQLRNNR (11/7/2011)

    sqlfriends (11/7/2011)

    But our data file size of the whole database is already 10 gb.

    quote: By table = 4GB, you mean the clustered index is 4GB in size?

    If I go to our biggest table, right click Property , then Storage,

    I can see it listed the size of the data is 4 gb, and index size is 0.6 gb.

    Thanks

    Two things:

    Did you do this?

    GilaMonster (10/31/2011)

    Query sys.dm_db_index_physical_stats. Limited mode is good enough. See the page_count column

    This will also let you know the rough frag % and if defragging the biggest table is necessary every time the maint plan runs.

    Second,

    Use the scripts referenced by Remi. One is the table size script on my site. This will break down the Table Size and Index size for all tables. This is much faster than right clicking and checking properties for each object.

    The second script is the Index Defrag script by Michelle Ufford (sqlfool). The script is long because it creates a few objects and stored procedures. You can use this script - it is safe (Remi, Gail and I would have said otherwise if it wasn't good - but we understand you want to do due diligence).

    That script will determine if the index really needs to be reorged or defragged and then only do those that are necessary. What's more, is that you can have it run for a length of time and sleep easier knowing the job will not run too long.

    I forgot one thing.

    Gail asked about if the table has a Clustered Index. Data size in properties will report the size of the Clustered Index or the Heap - whichever is present on that table. We really need to know if that table has a CI. Without it, you can't defrag the other indexes.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 7, 2011 at 9:49 am

    #1404066

    SQLRNNR (11/7/2011)

    Ninja's_RGR'us (11/7/2011)

    SQLRNNR (11/7/2011)

    Ninja's_RGR'us (10/31/2011)

    sqlfriends (10/31/2011)

    Thanks all.

    First, I see the script is so long, I will have to study it a little bit later before I use it .

    Second, I checked in the maintenance plan we do have the sort in tempdb checked in the reindex task.

    How can I know how much space do all the indexes use in the database?

    Thanks

    That's close to what you need.

    http://jasonbrimhall.info/2010/05/19/tablespace-update/

    The script I posted creates the script to run. To call it you just call an sp with 2-3 parameters.

    Thanks Remi - this is an excellent recommendation;-)

    Only if the new version is out ;-). (http://www.sqlservercentral.com/Forums/Topic1201443-373-1.aspx)

    It will be out shortly - time permitting 😀

    I know, I know. So hard to do a copy / paste :-D.

  • November 7, 2011 at 9:50 am

    #1404067

    Ninja's_RGR'us (11/7/2011)

    So when you rebuild you need AT LEAST another 4.8 GB of free space (unless doing it in tempdb). Since you only have 3.7GB left you have this error.

    Thank you, but I see in maintenance plan we do check sort in tempdb.

    And also the table does have a clustered index.

  • November 7, 2011 at 9:56 am

    #1404068

    sqlfriends (11/7/2011)

    Ninja's_RGR'us (11/7/2011)

    So when you rebuild you need AT LEAST another 4.8 GB of free space (unless doing it in tempdb). Since you only have 3.7GB left you have this error.

    Thank you, but I see in maintenance plan we do check sort in tempdb.

    And also the table does have a clustered index.

    I don't know those exact internals but it would make sense to copy the index from tempdb to the local db before dropping the old one.

    ANYWAYS, you need to add space to that group. There's no way around that.

    My script will save you from rebuilding something that doesn't need it, but once it doesn it will error out as well.

    Plan C would be to reorganize instead of rebuild for that table. That would avoid that error but it's more a hack than anything else.

  • November 7, 2011 at 10:06 am

    #1404072

    SQLRNNR (11/7/2011)

    We really need to know if that table has a CI. Without it, you can't defrag the other indexes.

    Yes, you can.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 7, 2011 at 10:07 am

    #1404074

    GilaMonster (11/7/2011)

    SQLRNNR (11/7/2011)

    We really need to know if that table has a CI. Without it, you can't defrag the other indexes.

    Yes, you can.

    It has always failed for me. How do we (without creating CI and then dropping it after the defrag)?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 7, 2011 at 10:09 am

    #1404076

    Ninja's_RGR'us (11/7/2011)

    I don't know those exact internals but it would make sense to copy the index from tempdb to the local db before dropping the old one.

    The index is not copied anywhere. The options is sort_in_temp_db. If it's on, then TempDB is used for the extra space to sort as opposed to the user database (that 20% that I mentioned a few times). It's not the index build that's moved to tempDB, that would be really silly for huge indexes.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 7, 2011 at 10:11 am

    #1404077

    SQLRNNR (11/7/2011)

    GilaMonster (11/7/2011)

    SQLRNNR (11/7/2011)

    We really need to know if that table has a CI. Without it, you can't defrag the other indexes.

    Yes, you can.

    It has always failed for me. How do we (without creating CI and then dropping it after the defrag)?

    Same way as for tables that have a clustered index - ALTER INDEX <nonclustered index name> REBUILD.

    The heap itself can't be, but the nonclustered indexes built on top of the heap absolutely can be rebuilt.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply