SQL Server Performance

Auto Rebuild indexex

Discussion in 'SQL Server 2005 General DBA Questions' started by reethu, Apr 20, 2007.

  1. reethu New Member

    Hi guys there is any way to rebuild indices on tables, i am trying to use this plan in mauntanance plan.i need to rebuild all indices in database at one shot.
    is any one no how to do it. please....,if any script does this please forward it .
    thanking you.

    SRJ2005
  2. Luis Martin Moderator

  3. ndinakar Member

    what do you mean by "one shot"?
    You need to have your CREATE INDEX scripts for each of the index that you want to rebuild. You can either create multiple jobs and spread out the indexes or create one job with multiple steps in it.

    One job /multiple steps
    Pros: only one job
    Cons: wasting your CPU/time when you are rebuilding one at a time.

    Multiple Jobs/multiple steps
    pros: you are dividing the work so your maintenance window can be shorter. get the work done quicker
    cons: you could be creating dead locks. So its impotant to spread the indexes out properly. Put all indexes belonging to one table under same job.


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  4. reethu New Member

    i mean i need to complete this task in one job but different steps or each step for each database, but using dbcc dbreindex i need to pass every time table name as parametre instead of that iwould like to do it with one script.

    SRJ2005
  5. MohammedU New Member

    You can use the script from the URL provided Luis and schedule it to run when ever you want.... it will be done in single job...

    Even if you want to use Maintenance plan, it will also be done in a single job.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

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

  7. reethu New Member

    i am getting this error when i run it i changed differently it run fine but when i execute it is giving again error.please can you advise me this error exactly happening at dbcc dbreindex string ,

    Msg 207, Level 16, State 1, Procedure sp_defragment_indexes, Line 112
    Invalid column name '''.
    Msg 207, Level 16, State 1, Procedure sp_defragment_indexes, Line 112
    Invalid column name '''.

    SRJ2005
  8. MohammedU New Member

    Just add the following two line before the create procedure statement and run...

    SET QUOTED_IDENTIFIER OFF
    GO


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. reethu New Member

    it is still giving the error i see this error in massagea pan but i can see query result in result pan but i donot think it is reindexing because when i see at logical fragmentation the number before and after looks same or i might looking at different if it rebuild index what column should conform this info.
    sorry for troubling you/
    error in massage pane.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '.'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '.'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '.'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '.'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '.'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '.'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 3701, Level 11, State 5, Procedure sp_defragment_indexes, Line 149
    Cannot drop the table '#fraglis', because it does not exist or you do not have permission.

    SRJ2005
  10. ndinakar Member

    please post the script you have.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  11. MohammedU New Member

    It is working fine for me...
    If your table names have spaces change your code include Quotname function...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. reethu New Member

  13. MohammedU New Member

  14. Luis Martin Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by reethu</i><br /><br />HI I AM USING LOUIS SCRPIPT<br /<a target="_blank" href=http://www.sql-server-performance.com/tp_automatic_reindexing.asp>http://www.sql-server-performance.com/tp_automatic_reindexing.asp</a><br /><br /> <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ndinakar</i><br /><br />please post the script you have.<br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************<br /<a target="_blank" href=http://weblogs.sqlteam.com/dinakar/>http://weblogs.sqlteam.com/dinakar/</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />SRJ2005<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Did you store the script in master db?<br /><br />BTW: Is Tom Pullen script.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  15. satya Moderator

    Have you created the SP in the database and checked for any error while creating that object.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  16. reethu New Member

    Yap i created this sp in master, i am using this script in mssql server 2005 i am sure it may be problem.that time i got this error what i sent to MohammedU.i changed "'" this to '.' then it ran fine but when i am executing it throwing so many errors.i believe it is problem in quotes.but i could not figureout how to do it.please give some breath.<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Luis Martin</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by reethu</i><br /><br />HI I AM USING LOUIS SCRPIPT<br /<a target="_blank" href=http://www.sql-server-performance.com/tp_automatic_reindexing.asp>http://www.sql-server-performance.com/tp_automatic_reindexing.asp</a><br /><br /> <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ndinakar</i><br /><br />please post the script you have.<br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************<br /<a target="_blank" href=http://weblogs.sqlteam.com/dinakar/>http://weblogs.sqlteam.com/dinakar/</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />SRJ2005<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Did you store the script in master db?<br /><br />BTW: Is Tom Pullen script.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />SRJ2005
  17. Luis Martin Moderator

    As I said before, I don't used in 2005 because I have no client with 2005. With 2000 I used a lot, in fact I have to modify the script to work with SQL Spanish in some clients with SQL in that language.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


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



  18. reethu New Member

    Finally i got it guys ,it was problem with quoted identifiers,, i changed database option at database level then now it is ok.

    SRJ2005
  19. reethu New Member

    I GUYS i finnally made it work,the reason showquoted identifiers at database level i turned on truw.then it is working. how ever thanks every one. for helping.

    SRJ2005
  20. MohammedU New Member

    Looks like Reethu is on fire today [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />All issues resolve in one day...<br /><br />Good luck...<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

Share This Page