Hi, I've got the following error message on a shrink log step in a reindex job - 'Cannot shrink log file 2 (xxx_log) because all logical log files are in use. [SQLSTATE 01000] (Message 9008)' the reindexing part of the job succeeds, but takes much longer to complete. I thought it may be a scheduling issue and there was some contention with other jobs (backup & checkdb), but I don't think this is the issue. Any ideas?
Welcome to the forums! That is a typical error when database is in use by other process. You have to check that.
Thanks for the warm welcome! [] I first suspected that it was the backup job as this kicks off during the reindex job. After some investigation I have found that one of our largest tables was included in the reindex last night (top 8 are reindexed after they meet the threshold) - hence the reason the job took so long and log has more than doubled in size. The threshold has been set at 20% for fragmentation and this table had just hit it. I am going to monitor this and see what happens overnight. Dependant on usage, this table could be included in the reindex again tonight and the same thing could happen. Main thing is that the checkdb is good and the backup is there and the reindex job does complete and mirroring is OK and logshipping is OK..... But on the other hand with big logs (that aren't shrinking) comes space issues!!! It's a DBA's life!!! []
You may also want to review the fill factor's of your indexes on this table as well as others. If you are hitting 20% fragmentation on a daily basis, you're fill factor could be set too high.