MSSQLTips

This post is related to this tip: SQL Server script to rebuild all indexes for all tables and all databases

http://www.mssqltips.com/tip.asp?tip=1367

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

ChipLee,

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

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
ORDER BY 1 

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 ...