Does this make any sense? RRS feed

  • Question

  • I have inherited a SQL 2005 server with a few small databases on it.  There's a maintenance plan here that doesn't seem to make a lot of sense to me.  Can anyone comment:


    Every Sunday at 4:00 AM


    1. Reorganize index on All user database Tables and Views - compact large objects.

    2.  Rebuild index on local server connection, All user databases, Tables and view, Original amount of free space.

    3.  Shrink database.  All user databases.  Limit 100MB.


    I'm confused a little about item 3.  Won't a shrink be kind of useless after all of the work that goes on in steps 1 and 2.  When I ran this manually, the transaction logs jumped significantly.


    Thanks in advance for your help.





    Tuesday, March 18, 2008 2:30 AM


All replies

  • We re-organize or rebuild indexes when the data get fragmented or you can also call this process defragmenting the data. Once you have rebuild the indexes , there may be lot of unused space in the db. In that case you may shrink the db. Shrinking database is not a best practice but at time you may do this due to space constraint in the physical machine. When you rebuild indexes it will consume TL and there will be significant TL growth depends upon the indexes being rebuild. The new indexes are not created on top of existing one its a fresh index creation.



    Tuesday, March 18, 2008 6:42 AM
  • You can refer this DMF sys.dm_db_index_physical_stats to check how fragmented your indexes are. Depending on the fragmentation level you can choose to reorganize or rebuild the indexes. Also refer this link about the impaxts of regular shrinking, and this link deals about the order in which the Sql Server maintenance plan needs to be scheduled,

    Its better to change the recovery model to Bulk logged when performing operations like Index rebuild, so that the transaction log size can be controlled as there will be minimal logging.

    - Deepak
    Tuesday, March 18, 2008 7:46 AM
  • I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.


    Ola Hallengren


    Wednesday, March 19, 2008 11:42 PM