dbcc dbreindex

Last Post 13 Oct 2008 08:24 AM by dbandee. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dbandee
New Member
New Member

--
13 Oct 2008 07:36 AM
i have inherited one environment
db is 80 GB
and prev dba had weekly job running the following code

print convert(varchar(30),getdate(),121)+ ': Start dbReindex accessgroup' dbcc dbreindex(tblgroup)
print convert(varchar(30),getdate(),121)+ ': Start dbReindex accounttype' dbcc dbreindex(tblnttype)
print convert(varchar(30),getdate(),121)+ ': Start dbReindex accumulator' dbcc dbreindex(tbllator)
print convert(varchar(30),getdate(),121)+ ': Start dbReindex actionprocess' dbcc dbreindex(tblss)
print convert(varchar(30),getdate(),121)+ ': Start dbReindex actionreason' dbcc dbreindex(tblreason)
print convert(varchar(30),getdate(),121)+ ': Start dbReindex activityaction' dbcc dbreindex(tblaction)
...
and this continues for all 838 tables

during the run time (2 hours) database adds another 20 GB to mdf file and 30 GB to log file, thus making db carry extra 20 GB all the time
this becomes problematic when we restore it to a diff servers that are tight on space and extra GBs really mess things up
***db is in FULL recovery mode all the time***

is there a better way to do it?

Thank you
dbandee
New Member
New Member

--
13 Oct 2008 08:24 AM
thank you
Homebrew
New Member
New Member

--
29 Oct 2008 06:59 AM
quote:

Originally posted by: gunneyk
Also you can change the recovery mode to Simple before you reindex and get a minimally logged operation instead.


If you switch to SIMPLE during reindex, remember to switch back to FULL, then you MUST take a FULL BACKUP or you will lose your T-Log recovery chain. Some people switch to BULK_LOGGED instead.
SQLUSA
New Member
New Member

--
20 Nov 2008 01:44 AM
quote:

Originally posted by: Homebrew

If you switch to SIMPLE during reindex, remember to switch back to FULL, then you MUST take a FULL BACKUP or you will lose your T-Log recovery chain.


Affirmative. That is a good point. Actually that way you have a backup of reindexed database which will be performing well in case of disaster failover.

Kalman Toth, SQL Server 2008 Training
http://www.sqlusa.com/register/


dbandee
New Member
New Member

--
29 Jan 2009 07:15 AM
To all
Thank you very much
This was very, very helpful

I used the code from BOL and run this in db being in simple recovery

THANK YOU!!!


Acceptable Use Policy
---