Reindex minimally logged - Does not work

  • Hi

    I am using a job to rebuild indexes. Step one I put the database in Bulk Load. Step 2 I reindex all indexes with something like -"ALTER INDEX ALL ON [dbo].[usertable] REBUILD WITH (FILLFACTOR = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )". Step 3 switch DB back to Full mode.

    Can anybody tell me what operations/settings will cause the bulklog to be ignored?

    Thanks

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • create index and alter index rebuild are both minimally logged.

    What is it leads you to believe they're not?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • The log files growth is the same as when it is in full mode. I see logfiles that grow up to 18 gigs in one hour while reindex is happening.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • are you seeing this on all databases that are having indexes rebuilt??

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Yes I do. This is only a problem with logshipping databases. I only have 4mb line to offsite.

    I think I saw something once that said if some backup is running while reindex, minimal logging is switch off, but I can not find the article again and can not remember it clearly.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • Take frequent log backups if that is the case and rebuild indexes one by one and only if its needed(for e.g. frag.>30 and indx pages >1000 or so).

  • Thanks for the suggestion on how to do reindexing but I still have the problem. It is very important in my enviroment to find a solution to this problem.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • just out of interest what version SQL server are we talking here?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I have 2000, 2005 Ent and 2008 Ent.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • What is the size of larget index present in the database?

    MJ

  • Largest is 4.9Gig

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • The log files growth is the same as when it is in full mode

    Do you mean the size of the database log file ( ldf extention) or the size of the transaction log backup files ?

    Under bulk logged recovery mode, the database log file will use less disk space but the transaction log backup files will be at least the same size as compared to full recovery mode and are often larger.

    SQL = Scarcely Qualifies as a Language

  • Because of this increasing log file size while rebuild indexes making us to disable log shipping configuarion for our environment. We have a database of size 15 GB and rebuilding indexes for that database (based on condition 30>rebuild,<30 reorganize), genarates almost 14 GB for the next log backup.

    Even if I run the log backup every 5 mins, it's genarates a log file of 2 GB and ultimately 7 log files of 2 GB (14 GB) and these large files has to be transferred to secondary.

    Here is the main limitation from Netwok team that we cannot transfer larger than 100 MB file.

    Please suggest alternatives to this problem

  • pshaship (4/22/2010)


    Because of this increasing log file size while rebuild indexes making us to disable log shipping configuarion for our environment. We have a database of size 15 GB and rebuilding indexes for that database (based on condition 30>rebuild,<30 reorganize), genarates almost 14 GB for the next log backup.

    Even if I run the log backup every 5 mins, it's genarates a log file of 2 GB and ultimately 7 log files of 2 GB (14 GB) and these large files has to be transferred to secondary.

    Here is the main limitation from Netwok team that we cannot transfer larger than 100 MB file.

    Please suggest alternatives to this problem

    Since you have a hard limit of 100MB - you either have to stop rebuilding indexes, or only rebuild indexes that actually need to be rebuilt. I see that you are rebuilding when > 30% fragmented and reorganizing < 30% - but are you limiting that to only tables with more than 1000 pages?

    BTW - this issue cannot be resolved by trying to minimally log the rebuilds. As pointed out previously, the transaction log backups will be the same size (or larger) regardless of the recovery model.

    And finally, reorganize is always a fully logged operation.

    I don't see how you are going to be able to solve this issue with that limitation in place.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I

    see that you are rebuilding when > 30% fragmented and reorganizing < 30% - but are you limiting that to only tables with more than 1000 pages?

    why only tables with more than 1000 pages?

    Is index having more then 1000 pages? or tables having more than 1000 pages?

    Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

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