SQL Server Performance Forum – Threads Archive

Auto Rebuild indexex

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

This is for 2000, not sure about 2005. http://www.sql-server-performance.com/tp_automatic_reindexing.asp 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.

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/

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

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.

See my blog toohttp://sqlserver-qa.net/blogs/tools…r-2005-index-optimization-best-practices.aspx 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.

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

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.

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

please post the script you have. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

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.

HI I AM USING LOUIS SCRPIPT
http://www.sql-server-performance.com/tp_automatic_reindexing.asp

quote:Originally posted by ndinakar please post the script you have. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

SRJ2005

quote:Originally posted by reethu HI I AM USING LOUIS SCRPIPT
http://www.sql-server-performance.com/tp_automatic_reindexing.asp

I used the same script on my test server and it is working fine MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by reethu HI I AM USING LOUIS SCRPIPT http://www.sql-server-performance.com/tp_automatic_reindexing.asp</a> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by ndinakar please post the script you have. *********************** Dinakar Nethi SQL Server MVP *********************** http://weblogs.sqlteam.com/dinakar/</a> <hr height="1" noshade id="quote"></blockquote id="quote"> SRJ2005 <hr height="1" noshade id="quote"></blockquote id="quote"> Did you store the script in master db? BTW: Is Tom Pullen script.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] 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.

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.

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. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Luis Martin <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by reethu HI I AM USING LOUIS SCRPIPT http://www.sql-server-performance.com/tp_automatic_reindexing.asp</a> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by ndinakar please post the script you have. *********************** Dinakar Nethi SQL Server MVP *********************** http://weblogs.sqlteam.com/dinakar/</a> <hr height="1" noshade id="quote"></blockquote id="quote"> SRJ2005 <hr height="1" noshade id="quote"></blockquote id="quote"> Did you store the script in master db? BTW: Is Tom Pullen script.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

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.

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

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

Looks like Reethu is on fire today [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] All issues resolve in one day… Good luck… MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>