Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Performance Monitoring in the Cloud

Monitor and Troubleshoot SQL Server Performance

5 ways monitoring SQL Server Agent Jobs can make your job easier

Deploying Applications in Azure without Configuration Limitations

SQL Server script to rebuild all indexes for all tables and all databases

MSSQLTips author Greg Robidoux By:   |   Read Comments (68)   |   Related Tips: More > Fragmentation and Index Maintenance

One of the main functions of a DBA is to maintain database indexes.  There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server.  In addition, other tips have been written about using maintenance plans to maintain indexes on all databases.  One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not.  In this tip we look at a simple script that could be used to rebuild all indexes for all databases.


The one nice thing about maintenance plans is that it works across multiple databases and therefore you can push out one task to handle the same activity across all of your databases.  The problem that I have seen with maintenance plans is that sometimes they do not work as expected, therefore here is another approach.

The script below allows you to rebuild indexes for all databases and all tables within a database.  This could be further tweaked to handle only indexes that need maintenance as well as doing either index defrags or index rebuilds.

The script uses two cursors one for the databases and another for the tables within the database.  In addition, it uses the INFORMATION_SCHEMA.TABLES view to list all of the tables within a database. 

Because we need to change from database to database we also need to create dynamic SQL code for the queries.  For the DBCC DBREINDEX option we can just pass in the parameters, but for the ALTER INDEX statement we need to build the query dynamically.  Here is the script.

DECLARE @Database VARCHAR(255)  
DECLARE @fillfactor INT

@fillfactor = 90

name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

@cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM ['
  WHERE table_type = ''BASE TABLE'''  

-- create table cursor  
EXEC (@cmd)  
OPEN TableCursor  

FETCH NEXT FROM TableCursor INTO @Table  

-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)  

NEXT FROM TableCursor INTO @Table  

DEALLOCATE TableCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
DEALLOCATE DatabaseCursor

The script will work for both SQL 2000 and higher versions.  For SQL 2000 it uses DBREINDEX and for SQL Server 2005 and higher it uses ALTER INDEX.  Thanks go out to LittlePanthers for providing the code snippet to check the version of SQL Server.

Also, I have excluded the system databases, so you can include these or also add other databases to exclude from you index maintenance routines.

Next Steps
  • This is a simple base script that could be modified into a stored procedure and also allow you to pass other parameters such as doing an index rebuild or an index defrag.
  • Make the index rebuild statements more robust with other options.
  • You could also modify this to read from a table that you create to identify which databases and which indexes you want to run this against.  You can look at index fragmentation and only rebuild the indexes that need to be rebuilt.
  • This approach rebuilds all indexes, so be careful if you run this on very large indexes or large databases
  • Take a look at these other index related tips

Last Update: 10/14/2012

About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Tuesday, November 18, 2014 - 5:21:36 PM - Kim Read The Tip

This one is simpler.


EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
EXEC sp_updatestats

Wednesday, November 12, 2014 - 6:02:46 AM - Victor Read The Tip

This is an awesome script for once-off brute force index rebuild.

Thanks for this, Greg. It is exactly what you say it is - and is valuable for what it is.

If you want finesse, schedules, reports etc check out www.OlaHallengren.com - Ola's made a whole suite of scripts available for free - and there are great lessons for developers when you open and read his actual code.


Tuesday, October 21, 2014 - 2:30:30 PM - Greg Robidoux Read The Tip

Hi Alex, I would look at fragmentation levels and rebuild or reorganize indexes based on the fragmentation. 

Check out this tip instead that does selective index maintenance versus all indexes: http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/

Tuesday, October 21, 2014 - 1:42:42 PM - Alex Read The Tip


I did a SHRINK on my Database (I had to do) and it realeases around 55% of Available free space on my Data Base, and I've been reading that "Reorganize or Alter index" could be good after doing a SHRINK, Is it really important to run this script?, Will this script increase my .mdf size again?

Thank you!

Tuesday, September 30, 2014 - 5:53:53 AM - jacouh Read The Tip

Thank you very much for sharing this script. This morning we could no more insert newrecords in the products table. After running this rebuilding and defragmentation script, all work as a charm.

Wednesday, May 07, 2014 - 3:29:39 PM - Zack Read The Tip

I like the script. I have a lot of databases in read only or offline status on my development server though - which caused it to fail. So I modified the script for the database cursor slightly... 


SELECT name FROM master.sys.databases

WHERE name NOT IN ('master','msdb','tempdb','model') AND is_read_only<>1 AND state<>6

Wednesday, February 19, 2014 - 9:12:26 AM - Greg Robidoux Read The Tip

Hi Jay,

Take a look at this tutorial about tuning queries: http://www.mssqltips.com/sqlservertutorial/2250/graphical-query-plan-tutorial/

Also, take a look at these tips to gather information about poor performing queries using Profiler and server side traces: http://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/

Tuesday, February 18, 2014 - 8:59:23 PM - Jay Ganesh Read The Tip

Thanks ,

It Works.

I Need a suggession. i useing sql server 2005 for one small Hospital (free Service organisation).

all table record have a relation with one table. when make query with relate that table , is it affect performance ? (in feature)

can you assit me to how optimize and organize the databse with best performance





Tuesday, December 10, 2013 - 7:43:09 AM - mo Read The Tip

exec sp_MSforeachdb '

IF ''!'' NOT IN (''master'',''model'',''msdb'',''tempdb'')


USE [!]; 


END',@replacechar = '!'

Nice & Easy ;)

Friday, November 22, 2013 - 4:25:50 PM - Greg Robidoux Read The Tip

@Lava - yes this script could be modified, but this doesn't get current fragmentation levels it does every index which is not always the best approach.

Take a look at this tip to see if you could use this or combine the two together:


Friday, November 22, 2013 - 3:50:17 PM - Lava Read The Tip

Hi Greg -

Is it possible to modify your script to add a case statement to where the program either rebuild or reorganize based on fragmentation level? I like your way of writing scripts, it is always simple and easy to follow.




Tuesday, November 12, 2013 - 6:57:19 AM - Parker Read The Tip

Remember, this article is a bit aged. Rebuilding indexes, especially on a recurring schedule, is no longer important, and usually waste of resources. The only reason you would do this is for index fragementation where the entirety of the index pages are sequentially organized on a single rotating drive. With most data in multi-physical-drive raid environments, and most certainly with Solid State Drives, reducing fragmentation will not have any impact since the data will still be read from multiple discontinguous points, no matter how many times you defrag those pages. The take-away is that hardware technology changes have made it where data and indexes no longer need to be continguous. In most cases the default fill factor will suffice, but your hardware should be considered before throwing on excess processing usage that serves no purpose; save your CPU, log files, and network traffic for your backups, ETL, reporting, etc.

Thursday, October 24, 2013 - 10:45:24 AM - Carlos Read The Tip

I am new to sql scripts good job guys, good tips... I will be stoping by every so often to check for new tips

Wednesday, October 23, 2013 - 1:20:43 AM - Iyyankann M Read The Tip

Thanks guys, it really helped.

Monday, August 05, 2013 - 1:13:17 PM - Greg Robidoux Read The Tip

@Pat - thanks your your feedback on this tip.  Yes you are correct the better approach is to only maintain indexes that have fragmentation issues.

This is a good alternative for using Maintenance Plans, which rebuilds all indexes, or for small databases where reindexing all indexes is not very time consuming.

I agree a better approach is to selectively rebuild, but maintaining all indexes is better than doing nothing.


Monday, August 05, 2013 - 12:11:02 PM - Pat Read The Tip

I have to question if you really wish to rebuild every index on every table in every database.

At the very least, I would think that you would want to check the degree of fragmentation and the number of pages that the index consumes before deciding if the index even needs to be rebuilt in the first place.  Otherwise, you my be wasting a lot of unnecessary CPU cycles.


Thursday, May 30, 2013 - 11:20:33 AM - Greg Robidoux Read The Tip

@Ashok Kumar 3 - take a look to see if the index rebuilds are being blocked by other proceses.  This is one reason why it may be taking longer to complete.  Also, it maybe helpful to do one database at a time to see if you can pinpoint which database is causing the issues.


Thursday, May 30, 2013 - 6:02:00 AM - ASHOK KUMAR J Read The Tip

Hi Graig,
Excellent Article

I have one question:

We have a 20 databases, We are used to use maintenance Plan to schedule the Weekly rebuild

index job on Every Saturday night 12pm EST. Last

Two Weeks Weekly Rebuild Index job

Running long over 2days? But acutally it might

run 30 mints.

Which is caused this? How to fix this problem?

Friday, May 10, 2013 - 9:07:35 AM - Greg Robidoux Read The Tip

@Paul - have you run Profiler or a server side trace to find queries that take the longest to run?  You can find these queries and then look at exeuction plans to determine how to improve query performance.

Once you have optimized all of your queries the only other option is to add more or faster hardware such as CPU, memory and disks.

Take a look at these other tips:


Thursday, May 09, 2013 - 8:19:39 PM - Paul Read The Tip

@Greg -  Anything else I can do to speed up SQL besides reindexing?

Thursday, May 09, 2013 - 8:14:31 PM - Paul Read The Tip

Perfect, thanks Greg. I appreciate it, this is a big help...

Thursday, May 09, 2013 - 5:31:23 PM - Greg Robidoux Read The Tip

@Paul - yes you can just paste the code from the above into a query window and run this.

If you have small databases this will run quickly, but for larger databases and indexes it will take some time.

When you rebuild an index it will block other users until the index rebuid is complete, unless you are using the ONLINE option which is only available in the Enterprise edition of SQL Server.

Best practice is to rebulid indexes when there is low to no database usage just to eliminate any blocking or heavy I/O that might occur.

You can also change this to limit to one or more databases. 

Let's say you want to do this for only databases Test1 and Test2 change this code:

SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  


SELECT name FROM master.dbo.sysdatabases  
WHERE name IN ('Test1','Test2')  

Thursday, May 09, 2013 - 4:15:42 PM - Paul Read The Tip

I am very new to SQL but I jsut wanted to verify that I can past that code into a new query window for my SQL 2000 server and execute and it will run? 

Also might be a dumb question (I think the answer is yes) but do I have to make sure all users are out of the SQL application before running?

Wednesday, April 17, 2013 - 9:46:59 AM - Greg Robidoux Read The Tip

@Carl - change this part of the code as follows to do just one database

name FROM master.dbo.sysdatabases  
WHERE name IN ('RESCOM')  

Tuesday, April 16, 2013 - 8:55:50 PM - Carl Read The Tip


Thanks for your prompt reply..

so i will paste this query without editing this?..sorry for the question, i dont know if i should put and edit this query indicating the name of my database(RESCOM)..and if yes i dont know ehere to put it..



Tuesday, April 16, 2013 - 10:23:03 AM - Greg Robidoux Read The Tip

@Carl - this will do this for all indexes for all databases.

Tuesday, April 16, 2013 - 6:40:05 AM - CArl Read The Tip

Can anyone help me?


would i just run this query on my SQL and will it re index all my databases?or i need to modify or change some of it before running this query?

thanks for the help..



Tuesday, April 02, 2013 - 12:33:40 AM - xexex Read The Tip

@Greg Robidoux It work! Thanks.

Wednesday, March 27, 2013 - 2:40:11 PM - Greg Robidoux Read The Tip

@xerex - you can add this code as shown below.  This should work, but I have not tested it.



and add this

  CLOSE TableCursor
  DEALLOCATE TableCursor  

  FETCH NEXT FROM TableCursor INTO @Table

Wednesday, March 27, 2013 - 5:56:43 AM - xexex Read The Tip

@Greg Robidoux - I mean I don't need to hardcore the name to change recovery mode, please see the post in February 25, 2013 and February 26, 2013

Thanks for Reply :)



Wednesday, March 27, 2013 - 5:34:45 AM - Greg Robidoux Read The Tip

@xerex - the names in the following line only exclude databases, otherwise all databases are included:

WHERE name NOT IN ('master','msdb','tempdb','model','distribution')

Wednesday, March 27, 2013 - 2:05:15 AM - xexex Read The Tip

Thanks @Greg Robidoux

How can I add the script so I don't need to hardcore the DatabaseName inside the Database Cursor loop

Wednesday, March 06, 2013 - 5:58:26 PM - brian Read The Tip

 my friend set- up my site and it was hacked. the hacker changed the site completely and moved its location and eased the backups. i rebuilt the site but dont known how to change the server and place the file where it needs to be . any thoughts on how in known its in the pretable but what i am looking greek to me 

Tuesday, February 26, 2013 - 7:35:43 AM - Greg Robidoux Read The Tip

@xexex - take a look at these articles on how to set the recovery model to SIMPLE and FULL




You could add these commands inside the Database Cursor loop.

After you revert back to Full you need to make sure you do a full backup again, becuase the log chain will be broken for restoring your last full backup and your transaction log backups.

Monday, February 25, 2013 - 9:34:44 PM - xexex Read The Tip

Thanks, the script is great.

I ran it on my SQL Server, but my transcation gowth large.

I'd like to change my database to "simple mode" before re-index, how can I add this to the script?

Saturday, February 02, 2013 - 2:33:57 AM - Dhanesh Read The Tip

Hi Greg,

can you plz tell, how to check whether index is rebuidl or not means last index rebuild date

Sunday, January 27, 2013 - 2:09:22 PM - Greg Robidoux Read The Tip

Hi Brent, thanks for the feedback.  I haven't looked at this tip in quite some time.  That's kind of weird I made master upper case.  Oh well, thanks again for the note and this has been fixed.

Thanks again.

Sunday, January 27, 2013 - 11:03:31 AM - Brent Ozar Read The Tip

Hi, Greg. One quick tweak to the script - in the below line, switch MASTER to master.  On case-sensitive instances, it'll matter.  Thanks sir!


SELECT name FROM MASTER.dbo.sysdatabases   

Tuesday, October 23, 2012 - 7:20:35 AM - Thamaraiselvan Read The Tip

request came for the recreation of the indexes of all EVS tables (DAT_EVS_*) in DATxdb_work schema on URANUS-DAT.


Please copy, the existing indexes of EVS tables (DAT_EVS_*) in EARTH-DAT DATXdb TO  URANUS-DAT DATxdb_work EVS tables (DAT_EVS_*)


Please do the same for the primary keys as well.



Thursday, August 23, 2012 - 6:47:25 PM - ilir dokle Read The Tip

This work just great thanx a lot.

Tuesday, May 22, 2012 - 7:50:42 AM - Sherbaz Mohamed C P Read The Tip

With the help of some forums and blogs, I have designed a script that will automatically decide and rebuild or reorg all indexes in a database based on fragmentation level. Please have a look and let me know your inputs.


Saturday, May 19, 2012 - 1:32:18 PM - venkat Read The Tip

i have executed the script given in the begining of this page. it has executed succesfully. can you provide me a script that lets me know how the rebuild operation has performed.




Thursday, May 03, 2012 - 9:56:32 AM - Greg Robidoux Read The Tip

BobC - I agree it is better to only maintain the indexes that need maintenance instead of doing all indexes.  This is a good approach for people that want to do something and not have to put to much thought into the process.  Also, this is a good approach for SQLExpress where you cannot setup maintenance plans.

Here is another tip that does selective index maintenance: http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/

Thursday, May 03, 2012 - 8:16:42 AM - BobC Read The Tip

I second the suggestion to use Ola Hallengren's: http://ola.hallengren.com, or a similar one that will test fragmentation and reogr/rebuild accordingly. 

See also:


Sunday, February 05, 2012 - 2:31:47 AM - Anil Lobo Read The Tip

Thanks guys, it really helped.

Monday, January 09, 2012 - 7:01:06 AM - Ramesh Read The Tip

Generally I will use the rebuild index script for all my production database. It runs quite good and helps me alot.

Recently i have ran this script at my another production server database which has very huge data. The size of MDF is 30GB.

I got the below mentioned error, can you tell me what would be reason for that and how should i rectify.

***** Error **************

Database name 'tempdb' ignored, referencing object in tempdb.

Msg 1088, Level 16, State 9, Line 1

Cannot find the object "#TEMP_Location_Scanner_Counts_______________________________________________________________________________________000000000137" because it does not exist or you do not have permissions.

****************** end of error mesage. *****

Thanks in advance




Sunday, August 28, 2011 - 6:23:36 AM - Greg Robidoux Read The Tip

You can change these lines to use the fill factor that is currenlty in effect for the index:


-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor) 


-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'

-- SQL 2000 command

Sunday, August 28, 2011 - 3:22:23 AM - chandramohan Read The Tip


    When we have indexes with fillfactor other than 90 then this script will rebuild the indexes with new fill factor which is not wanted. So you need to modify the script so that will take the existing fill factors only.


Monday, July 25, 2011 - 3:26:18 PM - Greg Robidoux Read The Tip

Jay - take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1791

Monday, July 25, 2011 - 2:51:26 PM - Jay Read The Tip


The script is good, i am looking for kind of a same script, which show me fragmentation for some tables but not all. how can i do that.

Any help is greatly appreciated.


Tuesday, July 12, 2011 - 11:06:42 AM - Much better alternate Read The Tip

Instead of rebuilding ones that don't need it, try a more comprehensive solution, like

Ola Hallengren's: http://ola.hallengren.com


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


Friday, June 24, 2011 - 10:22:19 AM - Rickster Read The Tip

Greg ... error was produced using Sql Server 2000 QA tool. I found at least one other Google hit on that error msg from someone else who had posted exact same code.

Thanks Marcio.


Friday, June 24, 2011 - 8:04:39 AM - Greg Robidoux Read The Tip

Rickster I have not see this issue.  What version of SQL are you using and how many databases are you running this on?

Friday, June 24, 2011 - 8:03:47 AM - Greg Robidoux Read The Tip

Thanks Marcio for the alternate approach.

Thursday, June 23, 2011 - 11:42:47 PM - marcio dalago Read The Tip

EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'')
    PRINT ''#'';
    EXEC #.dbo.sp_msForEachTable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)'',
    @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Index Updated''''''
END',@replaceChar = '#'

EXEC sp_msForEachDb @command1='PRINT ''?'';'  -- DATABASE
EXEC sp_msForEachTable @command1='PRINT ''?''' -- TABLES

Thursday, June 23, 2011 - 2:38:42 PM - Rickster Read The Tip

I get the following error when running the author's script as shown:

Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'TableCursor' already exists.

Others aren't getting this?





Sunday, September 05, 2010 - 3:38:37 PM - marcio dalago Read The Tip
hi, my sample is:


EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = ''90'')'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Index Updated'''''' END',
@replaceChar = '#'

Friday, April 16, 2010 - 3:28:37 PM - Repriser2010 Read The Tip

Not good enough. You have to query DMV first to find which ones are really in need of a rebuild, not just rebuild everything, because the resource usage will stall your server.

Monday, December 21, 2009 - 6:41:15 PM - admin Read The Tip


That is a handy little trick if you have to manage multiple versions of SQL Server.

Monday, December 21, 2009 - 5:35:58 PM - LittlePanthers Read The Tip

here you go, sorry forgot to uncomment out the actual 2k call.

if (@@microsoftversion / power(2, 24) >= 9)
       -- SQL 2005 command
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
       -- SQL 2000 command
       DBCC DBREINDEX(@Table,' ',@fillfactor) 
EXEC (@cmd)

Monday, December 21, 2009 - 5:34:48 PM - LittlePanthers Read The Tip

to make it so you don't have to comment/uncomment lines of code, here is a tid bit I put in to run the script for either 2k or 2k5

if (@@microsoftversion / power(2, 24) >= 9)
       -- SQL 2005 command
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
       -- SQL 2000 command
       --DBCC DBREINDEX(@Table,' ',@fillfactor) 
EXEC (@cmd)

Friday, November 20, 2009 - 12:41:49 PM - daspeac Read The Tip

thanks for your comment. I think that the recovery implies the rebuilding of entire database, the detailed description of procedure takes a lot of time, it can be read on the website of application developer

Monday, November 16, 2009 - 1:16:35 PM - admin Read The Tip


It looks like the tool you mentioned is for recovering data\databases, but this tip is related to rebuilding indexes.  I believe these are two different needs.

Thank you,
The MSSQLTips Team

Monday, November 16, 2009 - 5:58:08 AM - daspeac Read The Tip
Hi there, I believe there is an easier way to do it. try the waiting for sql server to recover databases program, it is a good solution, if you experience any difficulties with dbx files. This application also converts dbf files. Hope it helps

Friday, June 27, 2008 - 8:49:26 AM - sql_er Read The Tip

When the indexes are being rebuilt, very often the transaction log grows, sometimes to a size you don't want it to grow to, as it might cause problems.  That is why, when I rebuilt indexes (in 1 database only), I usually rebuilt table by table, manually, checking the trans log size (using DBCC LOGSPACE) each time a big table with large indexes was rebuilt.

 If we are to automate it, I would think it might be helpful to add a test, such that after each index/table is rebuilt, check the transaction log space used.  If it is above a certain value, then either backup trans log, or just keep waiting and checking in a loop, until the scheduled trans log backup is executed, and the trans log space used drops below a certain value, and then continue with index rebuilding ...

Monday, June 02, 2008 - 5:03:23 AM - honzas Read The Tip

It would be better to exclude databases in some non-operational states (e.g. offline, readonly etc.)

Just enhance the filtering of the cursor's query e.g. by following:

WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')    -- exlude system datbases
 AND status &  -- exclude statuses not suitable for reindexing
 (   32 -- loadig
  |  64 -- pre recovery
  | 128 -- recovering
  | 256 -- not recovered
  | 512 -- offline
  |1024 -- readonly
  |2048 -- dbo use only
  |4096 -- single user
  |32768 -- emergency mode
  ) = 0

Friday, April 04, 2008 - 7:41:39 AM - admin Read The Tip


Agreed and thank you for the post.  In our scenario, we did not have spaces in the database or table names, but from a best practices perspective it is a good idea to address the issue just it case it does occur.

Thank you,
The MSSQLTips.com Team

Monday, March 10, 2008 - 7:05:36 AM - ChipLee Read The Tip

There is a little problem in this script. When database name or table name contains space, this script will fail.

The modification is to add '[' and ']' before database name and table name

Sponsor Information