January 11, 2006 at 3:14 am
We are currently using a full recovery model with a Transaction log backups every hour and a full backup every morning, We check integrity and indexes before every backup. Until now we have also scheduled Integrity with Index checking to run via its own schedule on a Sunday but this creates a log file almost the same size as the DB (17gb) which takes time then the Transaction log backup runs almost straight after which take a lot time and disk space! Whilst all this is happening we are obviously getting very poor server performance and many users moaning!
My question is do we really need to schedule this re-index and why when it runs before a full backup every night does it NOT create a huge transaction log as it does when on its own schedule?
Any help would be greatly appreciated
January 12, 2006 at 12:29 am
What you have left out is how you run these processes, maintenance plans, or T-SQL?
The reason that your Log is large is because of the REINDEX, when coupled with BACKUP the log is truncated, otherwise it grows very large as the REINDEX operation is logged. I would suggest only using REINDEX in a "offline" time frame as it will block usage of the table it is operating on, otherwise use DBCC INDEXDEFRAG when in "online" time frame as it minimizes the blocking.
I prefer to use T-SQL:
ALTER DATABASE MyDB SET RECOVERY SIMPLE -- Adjust for your database, here and below
EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('[MyDB].?') WITH NO_INFOMSGS CHECKPOINT"
BACKUP LOG MyDB WITH TRUNCATE_ONLY -- There should be nothing to truncate
DBCC SHRINKFILE ('MyDB_Log') -- You can adjust for a minimum log file size
ALTER DATABASE MyDB SET RECOVERY FULL
-- Now complete the change to FULL by backing up the database
-- So your future Log file backups can be restored
BACKUP DATABASE MyDB TO DISK = 'c:\BAK\MyDB_Full.SBK' WITH INIT
, NOUNLOAD, NAME = N'MyDB_Full', SKIP, STATS = 10
, DESCRIPTION = 'MyDB full backup', NOFORMAT -- Adjust for your parameters
The above minimizes the REINDEX Log file size, since the CHECKPOINT between each REINDEX truncates the Log while in SIMPLE mode.
Andy
January 13, 2006 at 9:00 am
Hi,
if the possibility exists that some user changes any data during the time you do the reindex you should use
BACKUP LOG MyDB WITH TRUNCATE
because otherwise you might lose that change if the server fails during the reindex.
Actually you should use that in any case, you don't lose anything by doing a real backup at that point 😉
regards
karl
Best regards
karl
January 13, 2006 at 12:07 pm
A reindex is an offline action. That means the database pretty much can't be used. I prefer DBCC INDEXDEFRAG which is online and rarely (for me) affects my users.
Also, you should not have to run a reindex very often. The main purpose is when you have lots of transactions that cause the index to become fragmented.
Do you run DBCC SHOWCONTIG to see how fragmented your indexes are?
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply