SQL Server Performance

To shrink transactiona log.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by kolanv, Aug 9, 2007.

  1. kolanv New Member

    Hi All,
    I always face issues while shrinking the transactional log for a databases on server. All of sudden I notice that log size is 40gb. Shrinking the trasactional log file using DBCC SHRINKFILE doesn't work and there is no drive space to take the DB backup. Can any one sugesst me what is the best possible action need to take for this on the production server? Is it possiblt to shrink or truncate40gb log to 10mb. Please advice on this.
  2. thomas New Member

    Is the log full? You need to clear it out before shrinking it if it is. Back the log up if the database is in full recovery mode or execute a checkpopint if it's in simple recovery mode. To see if the log is full or not, use dbcc sqlperf(logspace).
  3. kolanv New Member

    The log is full and there is no space to take back of 40db log.
  4. thomas New Member

    Put the database in SIMPLE recovery and execute CHECKPOINT in the database. This will clear out the log. Then you can shrink it.
    You should do a full backup of the database afterwards. you should also put in place regular log backups, otherwise this will just happen again.
  5. satya Moderator

Share This Page