SQL Server Performance

log file database is full (*.LDF)

Discussion in 'Performance Tuning for DBAs' started by rone, Nov 29, 2003.

  1. rone New Member

    Dear All,
    I have a problem in my database, the log file database (mydatabase.ldf) is to big and make the space of the harddisk full. How to reduce the log file and safety for the database.
    Thanks,
    rone
  2. satya Moderator

    Firstly run BACKUP LOG ... WITH TRUNCATE_ONLY option and then take full backup of the database and keep it safe location.

    Then use DBCC SHRINKFILE to shrink the Tlog size to the optimum size as per h/d space.

    If this is happening every now and then, then assess the Tlog size from all maint.jobs and restrict the size or schedule a shrink job for Tlog every week or so to reduce the size.

    Refer to books online for more information on DBCC statement referred.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. rone New Member

    i try to run DBCC SHRINKFILE (filename_log,1000) and error message 2 file in use.
    and then i stop the service for the database but i cannot connect to database and run DBCC SHRINKFILE.
    what can i do?

    thanks,
    rone
  4. Luis Martin Moderator

    When you run DBCC SHRINKFILE, is better when you are the only user SQL.
    Also SQL must be up, to run any SQL command.
    So, be along, do what Satya said. Also before shrink run DBCC CHECKPOINT.
    If nothing work, do backup same way Satya said, change database properties to simple, run Shrink and back to database properties full.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. satya Moderator

    What was the exact error message before you restarted the SQL services?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. Jon M Member

    I got these tips from Microsoft SQL Server 2000 DBA Survival Guide, Second Edition by M Spenik and O Sledge.

    Try to do one of the following:

    1. Increase the free space on the hard drive until the problem goes away: Although this is the simplest solution, it might not be the most effective solution. If you do not properly manage your transactions, you can fill up the transaction log regardless of how space has been allocated to it.

    2. Increase the frequency of the log backup: Doing so might reduce the frequency of the error or prevent it from occurring.

    3. Set the database recovery model to Simple: This option automatically truncates the inactive portion of the log when the log is 70% full. Only use this option if you are not backing up the transaction log; this option is not recommended for production databases!

    Jon M
  7. gaurav_bindlish New Member

    I would go for the procedure suggested by Satya for the same.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. itbhushan New Member

    Hi,

    I too have a problem with a very large transaction log file size.

    I have executed a big delete query which took around an hour to execute & during the same time my transaction log file size increased from 200 mb to 10.5 gb, already i have executed BACKUP LOG .. with TRUNCATE_ONLY option & thereafter dbcc shrinkfile command but there has been no considerable reduction (still greater then 10gb)in the log size. As per the maintenance plan the database is shrinked daily it has been 3 days since the increase in log file, wanted to know whether the log size will gradually decrease with passage of time ?

    Theres another way of getting the log file size small by deattaching the database & attaching a fresh new log file, but would prefer to use it as a very last option. If in case there is any other better option of reducing the current log file plzzz let me know.

    Thanks
    Bhushan
  9. satya Moderator

    Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log.

    So using DBCC SHRINKFILE with TRUNCATE_ONLY option will be ideal to reduce the Tlog size quickly than any other option.

    For log files, SQL Server uses target_size to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  10. itbhushan New Member

    Satya, iam very clear about the truncation of virtual log files, therefore in order to reduce the physical log size i had already executed dbcc shrinkfile with TRUNCATEONLY option, but there is no reduction in the log file size & also it clearly displays that the currentsize is equal to the usedpages.

    Wanted to know if there will be gradual decrease in the size of the log size ? or is there any other method to reduce the log file.

    Thanks
    Bhushan
  11. satya Moderator

    Yes there will be a gradual decrease of Tlog file, as its observed many times at my end.
    And also after you issue SHRINFILE, try to issue BACKUP LOG statement periodically to work it out.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  12. Luis Martin Moderator

    I insist, If nothing work backup database, change database properties to simple (when you are alone, no SQL activity) shinrk database, backup again and change again to full.
    I know is not the better way, but it happens to me, and I don´t find other way.
    Of course it was last way when I have no more space.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  13. ChrisFretwell New Member

  14. rone New Member

    Dear All,
    Thanks for the comment and reply for my problem, I do what Satya said and successful,the mistake is one user still login to the database so i unplug the connection and i run that command.
    I have experience, if i rename the log file,automatically the we have a new log file, is this the same thing with run the command? i mean that is another solution for log problem?maybe some one have try this?

    thanks ...
    rone
  15. satya Moderator

    Do you mean renaming .LDF (Tr.log file) and in that case you may require to stop and start SQL services.

    THe statement required is SP_DETACH_DB and rename file and SP_ATTACH_SINGLE_FILE_DB to reattach .MDF file.



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. joonkit New Member

    Dear All,

    I have some problems with the database of the *.ldf file. It has reached 34GB in less than 2 months. Now the entire program could now retrieve any data that save after on. For example, the data that could retrieve is only until 20th Dec. Those data on 21st and so on is not available. When I put a restrict file size of *.ldf, the program could load otherwise, unrestricted, it could not load. I suspect this might happen because the log file is full.

    I have thought of backing up the log file but the disk space remaining is 1GB. I know that after performing backup log, the log file would decrease. Thereafter, I only can run the command DBCC SHRINKFILE. Can I delete the log file and create a new one? How should I shirnk the log file that is 34GB in size? It uses the space is merely the same as well, nearly 34GB, not much free space in the log file.

    Thanks,
    Joon
  17. satya Moderator

    I know that after performing backup log, the log file would decrease.
    Using backup log truncates the log and it does not reduce the size of a physical log file, it reduces the size of the logical log file.

    Thereafter, I only can run the command DBCC SHRINKFILE.
    For information on shrinking the size of a physical log file, see Shrinking the Transaction Log in Books online.
    Can I delete the log file and create a new one?
    You can do this but there will outage to the system until it recovers, you can detach the database using SP_DETACH_DB and delete current .LDF file. Then use SP_ATTACH_SINGLE_FILE_DB to recreate a fresh log file.
    How should I shirnk the log file that is 34GB in size?
    Refer to books online for DBCC SHRINKFILE topic.

    Also search for similar topic in the forum.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page