SQL Server Performance

Shrinking the log,

Discussion in 'SQL Server 2005 General DBA Questions' started by contactjimmyus, Nov 12, 2010.

  1. Hi Every One,
    I have a sql 2005 server with a database with compatability to 80.
    Daily we are taking backup at 10 pm and transaction log backup at 12pm Noon
    The issue is logs are not shrinked after the full backup at night.I tried to shrink the log using management studio and not shrinking
    I tried to find the used files of the log file using dbcc loginfo and most of the status column returns 2
    Also i checked the open transaction and the result was 0. I also give manual checkpoint from query and dont help much
    The log file is set for 20% groth.I am looking for more info about how the status of the dbcc loginfo query are changed.
  2. Luis Martin Moderator

    What recovery model do you have?.
  3. The database is in full recovery mode .Also whe i tried to run dbcc shrinkfile it returnsCannot shrink
    log file 2 (LogFile) because all logical log files are in use.If this case how we can shrink the log file .I hope one more log file back up can clean up the log and align the log pages in sequence and then shrink the file can help in this situation.I will update when new log backup is done
  4. After the log back up i am able to shrink the log back to normal
  5. Luis Martin Moderator

    I suggest to backup log more frequently, said each hour. With that, log should grow slowly.
  6. Yuri New Member

    You may run script below from QA or create job (Replace DB_NAME with own).1.create job2.paste this script to step1 of job3.add new schedule4.create alert and operator to informing you about errors of job (if wont)use master--* Replace recovery mode to SIMPLEALTER DATABASE DB_NAMESET RECOVERY SIMPLEGO--* Shrink DBDBCC SHRINKDATABASE (DB_NAME)GO--* Replace recovery mode to FULLALTER DATABASE DB_NAMESET RECOVERY FULLGO
  7. satya Moderator

Share This Page