SQL Server Performance

t-log pls help

Discussion in 'SQL Server 2005 General DBA Questions' started by farrokhp, Jun 11, 2010.

  1. farrokhp New Member

    I am not too expert but there is a T-log I have, No OpenTran, Size 17gb, Used only 200mb, Tried several things to shrink it but it does not shrink...why?? very strange to me. I tried 1- backup tlog with no_log, 2- dbcc shrinkfile (tlog ...) 3- From SSMS and EM
    Have same problem sometimes in sql2k

    Please advise the correct fully steps
  2. preethi Member

    Please check the recovery model of the databases. If it is not simple, You need to take a transaction log backup before you shrink the database.
    Optionally, you can change the recovery model to simple (database -> Options will show this in Enterprise Manager/Management Studio)
    and then shrink the log.
    Hope this helps.
  3. satya Moderator

    Welcome to the forums.
    First of all you have to go through the number of processes that are running & scheduled jobs such as DBCC or any other kind of resource intensive processes, that will contribute a lot to Transaction log.
  4. rohit2900 Member

    Hi Just few additions....You should keep the db in full recovery when you're taking tlog backups at regular interval and you're concerned abt point n time recover at the time of failure. Else keep you db in simple recovery always.Secondly change your recovery model to simple and you can shrink log file by selecting shrink db from tools option. Just make sure to take a full backup before and after doing this.
  5. rohit2900 Member

    One more thing... whenever you try to shrink a file using tsql try below steps
    dbcc shrinkfile(<File Name>, NOTRUNCATE)-- this will rearrange the data pages in the file to the beginning of the file, without releasing the unsed space to OS
    GO
    dbcc shrinkfile(<File Name>, TRUNCATEONLY)-- this will release any unused space from end of the file to OS
    GO
  6. satya Moderator

Share This Page