February 11, 2009 at 7:16 am
We have a SQLSERVER 2005 DB that is re-indexed every every day.
Every evening it nearly runs out of space and I extend the size of the DB.
The current set DB size is
DB 40 GB
INDEX 30 GB
LOGS 20 GB
and intra day the index used space is less then half however during the re-index process almost all is taken. A similiar thing happens with the logs. Should I consider using dbcc indexdefrag (ie would this help)
I am using this command at the moment
exec sp_msforeachtable 'dbcc dbreindex (''?'')';
OR should I be looking to take the DB out of FULL RECOVERY mode when the re-index process runs?
Thoughts please
February 11, 2009 at 8:02 am
Why do you need to reindex everyday, that too entire database?
Anyways you can switch to bulk logged recovery before you begin reindex and once you are done switch it back to full revocery.
Its just that your log file is 20GB which is because of the reindex job
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
February 11, 2009 at 10:21 am
Will this affect our log shipping?
February 11, 2009 at 10:42 am
Thasos Perifimou (2/11/2009)
Will this affect our log shipping?
Answer is NO, but-->
The bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup. Check this link to understand what I mean
http://msdn.microsoft.com/en-us/library/ms190692.aspx
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply