locked
How can we Create a New Index for a Huge Table (SQL 2012) RRS feed

  • Question

  • Hi guys, 

    I would like to create a new in a huge table ( a table where used to retrieve historical data for viewing) and here's the table details in size and total records. 

    However, the index creation script gets fail due to insufficient disk space at log drive (total disk space 100GB). I believe it will take more disk space for this since it is a huge table. Is there any alternate way to perform this index creation besides adding disk space at log drive? I am thinking about allocate additional database log file at another drive to perform this but not sure whether is a right step or not. 

    Hope can get some advice here. Highly appreciated and have a nice day. 

    SQL Edition : SQL Server 2012, Standard Edition , running database mirroring at this database

    Best Regards, 

                       Han 


    • Edited by Harn Monday, May 23, 2016 1:58 AM
    Monday, May 23, 2016 12:34 AM

Answers

  • Are you using the FULL recovery model?  If so, consider temporarily changing the model to SIMPLE or BULK_LOGGED.  Also, consider specifying the SORT_IN_TEMPDB option.  See https://msdn.microsoft.com/en-us/library/ms184246.aspx?f=255&MSPPError=-2147217396


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Harn Wednesday, May 25, 2016 1:04 AM
    Monday, May 23, 2016 12:46 AM
    Answerer

All replies

  • Are you using the FULL recovery model?  If so, consider temporarily changing the model to SIMPLE or BULK_LOGGED.  Also, consider specifying the SORT_IN_TEMPDB option.  See https://msdn.microsoft.com/en-us/library/ms184246.aspx?f=255&MSPPError=-2147217396


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Harn Wednesday, May 25, 2016 1:04 AM
    Monday, May 23, 2016 12:46 AM
    Answerer
  • Yeah, in full recovery model since database mirroring had been set. 
    Monday, May 23, 2016 1:05 AM
  • I believe you do need more log space in that case, consider adding addition space for log.

    Monday, May 23, 2016 1:50 AM
  • The SORT_IN_TEMPDB option will perform the index transaction in tempdb instead of the user database, which should reduce user database log requirements. Of course, you'll need to ensure tempdb files are sized accordingly.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, May 23, 2016 2:04 AM
    Answerer
  • Yeah but the point is both database log and tempdb log are located at the same log drive...
    Monday, May 23, 2016 2:10 AM
  • Although you won't get a performance benefit with all on the same storage, at least the SORT_IN_TEMPDB might mitigate user log space requirements.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, May 23, 2016 2:46 AM
    Answerer
  • Since they're all sitting in the same drive, you're kinda limited in options. 

    If you need to get this done quickly and have space on some other drive, you can add secondary data files and log files that sit in a new filegroup on that other drive. This might just get you past the hump.

    The right thing to do is to figure out your storage requirements (not just space, think IOPS, throughput, and latency) and update whatever storage you have for the database. Remember to do the same for the primary and the mirror else you'll soon lose that protection when storage fall over on the mirror.

    Btw, after things are back to normal, suggest you check out Stretch Database. It's for 2016 only but with a table over 1billion row in size, you should seriously rethink how you manage it. Even if StretchDB is not an option, you should consider other techniques (e.g. partitions). 


    No great genius has ever existed without some touch of madness. - Aristotle

    Tuesday, May 24, 2016 10:09 PM
  • I just manage to create the index by following steps :- 

    1) Break the mirroring 

    2) Set recovery model to SIMPLE 

    3) Temporary add another log file at another drive 

    4) Create the index

    5) Remove the extra log file 

    6) Set recovery model back to FULL 

    7) Re-configure the mirroring

    Thanks for all the advice. Case closed. :)

    Wednesday, May 25, 2016 1:06 AM