SQL Server Performance

shrink log issue???

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by 5QL53rv3r, Jul 22, 2009.

  1. 5QL53rv3r New Member

    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?
  2. Luis Martin Moderator

    Welcome to the forums!
    That is a typical error when database is in use by other process.
    You have to check that.
  3. 5QL53rv3r New Member

    Thanks for the warm welcome! [:D]
    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!!! [:D]
  4. cstrong New Member

    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.

Share This Page